Jump to content

PDO and escaping data


Drongo_III

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.