PHP SQL bind_param with STR_TO_Date error-CodePudding

I'm doing an update into the database but its not working using bind param but if I do it in a sql file and replace the ? to the actual values it works.

                $sqlUpdate3 = $connection->prepare("UPDATE Users SET DateOfBirth = DATE(STR_TO_DATE(' ?, ?, ? ', '%d, %m, %Y')) WHERE UserName=?");
                $sqlUpdate3->bind_param("ssss", $_POST["daySelect"], $_POST["monthSelect"], $_POST["yearSelect"], $_SESSION["username"]);
                $sqlUpdate3->execute();

CodePudding user response：

Question marks inside a string literal in SQL are not parameter placeholders, they're just literal question mark characters.

To make them parameters, you must put the question marks outside string delimiters:

$sqlUpdate3 = $connection->prepare("
  UPDATE Users SET DateOfBirth = 
  DATE(STR_TO_DATE(CONCAT(?, ', ', ?, ', ', ?), '%d, %m, %Y')) 
  WHERE UserName=?");
$sqlUpdate3->bind_param("ssss", $_POST["daySelect"], 
  $_POST["monthSelect"], $_POST["yearSelect"], $_SESSION["username"]);

However, I recommend to format the date in a MySQL-compatible YYYY-MM-DD format in PHP, then bind that as a single parameter.

$date = new DateTime();
$date->setDate($_POST["year"], $_POST["month"], $_POST["day"]);
$dateString = $date->format('Y-m-d');

$sqlUpdate3 = $connection->prepare("
  UPDATE Users SET DateOfBirth = ?
  WHERE UserName=?");
$sqlUpdate3->bind_param("ss", $dateString, $_SESSION["username"]);