Since I'm changing mysqli queries to PDO prepared statements, can I eliminate all escaping?

Question

I'm giving in to "doing the right thing" and going to change all my mysqli queries to use PDO so I was hoping someone could confirm I'm doing it correctly. 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, and I guess my main question, 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, as long as I'm using placeholders in the statement, correct? Hoping to confirm before I delete all the escaping stuff from my code after switching to PDO.

Yes. You are correct. You don't need, or even should, escape the data if you're using prepared statements. The reason is when using prepared statements, the query and the values are sent separately. The query won't be concatenated with the values. — M. Eriksson, Jul 21 '17 at 14:49
With mysqli you also didn't have to! [It supports prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). PDO is nice (nicer, I would say), so please convert if you like, but I thought you should know you don't have to have PDO to have prepared statements. — GolezTrol, Jul 21 '17 at 14:55
Hmm, do you think mysqli prepared statements would make more sense than using PDO? Or six of one, half dozen of the other? — user3304303, Jul 21 '17 at 14:56

score 1 · Answer 1 · answered Jul 21 '17 at 14:53

It will work but you just bound all your parameter as string which is the default behaviour if you pass the bounded values to the execute method.

If you need more control or want to be more specific about the type of the parameter your should use bindValue and bindParam and pass one of PDO predefined constants:

PDO::PARAM_BOOL
PDO::PARAM_NULL
PDO::PARAM_INT
PDO::PARAM_STR
PDO::PARAM_LOB
PDO::PARAM_STMT
PDO::PARAM_INPUT_OUTPUT

Example from the docs:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

See the following answer to understand the differences between bindValue and bindParam: What is the difference between bindParam and bindValue?

Since I'm changing mysqli queries to PDO prepared statements, can I eliminate all escaping?

1 Answers1