PDO prepared statement to insert data into MySQL

Question

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.

Answer 1

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.

Answer 2

$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.

Answer 3

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.