PDO prepared statement to insert data into MySQL

I am changing all mysqli queries to use PDO now and just want to make sure I'm doing it right. Here is an old one:

$sql = "INSERT into profile (profileid, name, description) values ('$profileid', '$name', '$description')";
$sql= mysqli_query($connection,$sql); if (!$sql) {
die("Database query failed: " . mysqli_error($connection)); } else { redirect_to('/my-account'); }  And here is how I rewrote it: $stmt = $pdo->prepare("INSERT into pools (profileid, name, description) values (:profileid, :name, :description)");$stmt->execute([':profileid' => $profileid, ':name' =>$name, ':poolname' => $poolname, ':description' =>$description]);

redirect_to('/my-pools');


It seemed to work fine when I tested, just want to make sure I did EVERYTHING right. Does it all look good?

Also, prior to my old mysqli query, I would do this to "sanitize" the data:

$description = mysqli_real_escape_string($connection,$_POST['description']);  With PDO, I do NOT have to have that AT ALL anymore, is that correct? Just want to confirm before I delete all the escaping stuff from my code after switching to PDO. • You can use parametrized queries with mysqli too. You don't have to use PDO just for that. See php.net/manual/en/mysqli.prepare.php Jul 20 '17 at 21:33 • this should go on stack overflow since the code is not working... at least i dont think it's working. i think pdo will throw an error because you have too many paramters in your execute call. also, "name" is a mysql keyword so it should be quoted with the backticks. Jul 21 '17 at 18:13 • @Iwrestledabearonce. It's a keyword, but not a reserved word, so it doesn't need to be quoted. Jul 24 '17 at 7:16 3 Answers Yes, you did everything right, save for the obvious typo with the extra parameter. You can keep with it all right. The only improvement (which is the point of this site) I can think of is that you can make this code less bloated, with help of positional placeholders. $sql = "INSERT into pools (profileid, name, description) values (?,?,?)";
$pdo->prepare($sql)->execute([$profileid,$name, $description]); redirect_to('/my-pools');  To my taste, this is the most clear and readable approach: now it fits onto screen without horizontal scrolling and you still can visually control the parameter order. $stmt = $pdo->prepare("INSERT into pools (profileid, name, description) values (:profileid, :name, :description)");$stmt->execute([':profileid' => $profileid, ':name' =>$name, ':description' => $description]);  • You're not using the poolname paramter so remove it from the execute call. • You don't need to do any escaping AT ALL. mysqli_real_escape_string requires a mysqli connection to work anyway. You can't mix mysqli stuff with pdo stuff. • If he needed to do escaping with PDO, he'd translate mysqli_real_escape_string() to $pdo->quote(). Jul 24 '17 at 7:17
• Thanks @barmar are there cases when quote might be preferred over a prepared statement? Jul 24 '17 at 11:18
• mysql keywords don't have to be quoted Jul 24 '17 at 13:05
• @YourCommonSense It will work without the quotes because it's not a reserved word Jul 24 '17 at 13:20
• Still.. if you have a complicated query it helps readability to quote columns when the table has keywords. Jul 24 '17 at 13:21

Your code looks fine, it will work.Also you should consider looking at more options how it can be done.

First One would be with bindParam()

$stmt =$pdo->prepare("INSERT into pools (profileid, name, description) values (:profileid, :name, :description)");

$stmt->bindParam(":profileid",$..);
$stmt->bindParam(":name",$..);
$stmt->bindParam(":description",$...);

$stmt->execute();  So what bindParam does is that it bind the variables to the parameter markers.Bound variables pass their value as input and receive the output value, if any, of their associated parameter markers. Second One would be with bindValue() $stmt = $pdo->prepare("INSERT into pools (profileid, name, description) values (:profileid, :name, :description)");$stmt-> bindValue(":profileid",$..,PDO::PARAM_INT);$stmt-> bindValue(":name",$..,PDO::PARAM_INT);$stmt-> bindValue(":description",$...,PDO::PARAM_STR);$stmt->execute();


So what bindValue does is that the variable is bounded as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Also it can be done the way you done it:

execute([':profileid' => $profileid, ':name' =>$name, ':poolname' => $poolname, ':description' =>$description]);


So the question is whats the better approach?

It's up to you :D

• With bindParam, you can only pass variables ; not values

• with bindValue, you can pass both (values, obviously, and variables)

Also how does your connection look like?

Don't forget to add charset=utf8 in the connection.

Also you should add setAttribute(PDO::ATTR_EMULATE_PREPARES,false), if you are using mysql, because the PDO default emulates to emulation ,when using MySql.

If you don't disable emulation you don't get the full protection when using prepares statements.

• This is absolutely pointless answer that does not add any value. Jul 24 '17 at 13:10
• @YourCommonSense i explained in detail how it can be done.Also i pointed out mistakes that MOST people are doing...I don't see how it doesn't add any value?At least in my case i like when somebody adds extra information so that i can learn more from it. Jul 24 '17 at 13:58
• The point of this site is to review the existing code, not to retell the PHP manual without any particular aim. Jul 24 '17 at 14:01
• @YourCommonSense I don't want to be arrogant and i respect you as a person(from the stand point of your programming knowledge).Yes probably the only part would be that i add 'it looks okay than to rewrite the exact same code'.I don't see how it would add any value to it? Jul 24 '17 at 14:03
• For example Mike Brant answered my questions with a broad explanation where i had the opportunity to learn more and to expand my knowledge.I really don't understand how you can say that it ads no value, can you explain me that part please? Jul 24 '17 at 14:05