PDO and escaping data

[Drongo_III]

I'm just learning PDO as it seems to be recommended as the most secure way to query mysql.

 

Through reading a tutorial it suggested that if you use prepared statements with bound queries you do not need to escape or quote user data.

 

So if i ran:

 

$pdo->prepare('SELECT * FROM users WHERE username = :username');
$pdo->execute(array(':username' => $_GET['username']));

 

Does it mean that PDO will automatically escape the data? And therefore when using PDO you don't have to do anything with user data you're sendingt the database? Or do I have this wrong?

 

Really keen to understand this so I don't inadvertently bugger anything on my server.

 

Thanks,

 

Drongo

[KevinM1]

Prepared statements automatically escape.  That's one of their key features.

[Drongo_III]

Thanks Kevin!

[Christian F.]

Just to ensure that there are no confusion:

This has nothing to do with PDO per se, but is true for all Prepared Statements. MySQLi also supports PS, so it's just as safe to use MySQLi with PS as PDO with PS.

 

The only thing PDO brings to the table, so to say, is a standardized set of functions to access the DB. So that you don't have to go ":s/mysqli/psql/g" (or similar) should you want to change database engines. However, chances are you'll still need to massage the queries themselves a bit in that case. Depending upon their complexity, and similarity of the DBMS' SQL dialects, of course.

[xyph]

Just to ensure that there are no confusion:

This has nothing to do with PDO per se, but is true for all Prepared Statements. MySQLi also supports PS, so it's just as safe to use MySQLi with PS as PDO with PS.

 

The only thing PDO brings to the table, so to say, is a standardized set of functions to access the DB. So that you don't have to go ":s/mysqli/psql/g" (or similar) should you want to change database engines. However, chances are you'll still need to massage the queries themselves a bit in that case. Depending upon their complexity, and similarity of the DBMS' SQL dialects, of course.

 

PDO also brings named placeholders, something the MySQLi prepared statements don't allow. You can also define placeholders one-at-a-time with PDO.

[Christian F.]

Ah, you got a point there. Forgot about them. Sorry about that.

Thanks for reminding/correcting me.

[xyph]

I don't know why MySQLi doesn't have that functionality. Doing a variable, multi-row insert with MySQLi prepared statements is a nightmare.

[Drongo_III]

So the moral of the story is just use PDO then.

 

Out of interst, why are prepared statement considered so SQL-injection-proof? Is it simply because they automatically escape the data? Or is it because they don't allow people to append new queries through user data?

 

[Drongo_III]

Also, another question on PDO.

 

There are two ways to send data to perpared statements:

 

1) via an array

2) by using bindParam method

 

In what scenario are these two options preferable? Or are they simply equivalent?

 

 

 

 

 

So the moral of the story is just use PDO then.

 

Out of interst, why are prepared statement considered so SQL-injection-proof? Is it simply because they automatically escape the data? Or is it because they don't allow people to append new queries through user data?

[PFMaBiSmAd]

Out of interest, why are prepared statement considered so SQL-injection-proof? Is it simply because they automatically escape the data? Or is it because they don't allow people to append new queries through user data?

 

#2. The sql query statement can only be what was prepared (assuming you didn't put any external user supplied values into the query statement when you prepared it.) The values that are put into the query when it is executed are used as data only.

 

By automatically escaping string data, prepared statements prevent sql errors when the data contains special sql characters.

 

There are two ways to send data to prepared statements:

 

1) via an array

2) by using bindParam method

 

In what scenario are these two options preferable? Or are they simply equivalent?

 

Passing an array of data via the  ->execute() method has one disadvantage that I know of. All the data values are treated as string data (the same as if you bound them using PDO::PARAM_STR) and they are surrounded with single-quotes when they are placed into the query. This will cause numerical data to be treated as a string containing a number, which causes extra processing and problems if the number is a decimal data type (at least mysql converts a string containing a number to type float, then uses the resulting floating point number) and causes a sql error if the parameter being replaced is in a LIMIT clause (LIMIT 'x' is invalid syntax, it must be LIMIT x.)

 

[Drongo_III]

Thanks for your response PFM

 

Sorry to draw this out but trying to understand this and PDO is quite  a big chunk to learn.

 

When you say:

 

#2. The sql query statement can only be what was prepared (assuming you didn't put any external user supplied values into the query statement when you prepared it.) The values that are put into the query when it is executed are used as data only.

 

Do you mean that this would be safe (assuming I had done nothing at all to cleanse the $_POST data):

 

   	$sth = $db->prepare('INSERT INTO users (address, town) VALUES ( :address, :town)');
$sth->bindParam(':address', $_POST['address']);
         $sth->bindParam(':name', $_POST['town']);
$sth->execute();

 

 

Also, I'm struggling to get PDO::lastInsertId() to work - the manual page for this is a bit hard to follow. Can you give an example of how to use this? And is always likely to be accurate?

 

Thank you for your help!

 

Drongo

 

 

Out of interest, why are prepared statement considered so SQL-injection-proof? Is it simply because they automatically escape the data? Or is it because they don't allow people to append new queries through user data?

 

#2. The sql query statement can only be what was prepared (assuming you didn't put any external user supplied values into the query statement when you prepared it.) The values that are put into the query when it is executed are used as data only.

 

By automatically escaping string data, prepared statements prevent sql errors when the data contains special sql characters.

 

There are two ways to send data to prepared statements:

 

1) via an array

2) by using bindParam method

 

In what scenario are these two options preferable? Or are they simply equivalent?

 

Passing an array of data via the  ->execute() method has one disadvantage that I know of. All the data values are treated as string data (the same as if you bound them using PDO::PARAM_STR) and they are surrounded with single-quotes when they are placed into the query. This will cause numerical data to be treated as a string containing a number, which causes extra processing and problems if the number is a decimal data type (at least mysql converts a string containing a number to type float, then uses the resulting floating point number) and causes a sql error if the parameter being replaced is in a LIMIT clause (LIMIT 'x' is invalid syntax, it must be LIMIT x.)

[Drongo_III]

Think I've worked out how to get last id now.

 

Is this correct? It does return the ID but I wonder how accurate it would be if multiple inserts were happening at once?

 

$id = $db->lastInsertId();

 

I was getting confused and using $sth->lastInsertedId() - rather than using the original object.