Do PHP PDO prepared statements need to be escaped?

0 votes
asked Jun 29, 2010 by metropolis

On the PDO::Prepare page it states,

"and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters"

Knowing this, is there a PHP function like mysql_real_escape_string() that takes care of escaping stings for PDO? Or does PDO take care of all escaping for me?

EDIT

I realize now that I asked the wrong question. My question really was, "What all does PDO take care of for me?" Which I realize now with these answers that it really only removes the need to escape the quotes. But I would still need to do any other PHP sanitize calls on the values that I pass to the execute function. Such as htmlentities(), strip_tags()...etc...

5 Answers

0 votes
answered Jun 29, 2010 by josh-leitzel

You don't have to worry about it. PDO does not require you to escape your data before passing it along to the database.

Edit: Just to be clear, I mean to say that as long as you are passing variables into your parameters (for example, the value of a form field), you don't have to worry about it. However, if you're passing variables that you've defined as strings, for example, then obviously you need to escape anything that needs escaping in that string in order to avoid breaking syntax. However, this wouldn't even make much sense since one of the main advantages of PDO is that you're passing information from the user to the database without having to sanitize it yourself, and there aren't many times (if any?) that you would be passing strings that you yourself had defined.

Also, make sure you still sanitize your data for type. For example, make sure it's an integer if you expect it to be, make sure it's less than or greater than x if you expect it to be, etc.

0 votes
answered Jun 29, 2010 by tc

Yes and no:

  • Literals which you embed into the statement string need to be escaped as normal.
  • Values which you bind to the prepared statement are handled by the library.
0 votes
answered Jun 29, 2010 by sjoerd

If you prepare a statement and use bindParam or bindValue to supply variables, you do not need to escape the variables. Note that these functions assume that the variable contains a string, so use the third parameter to bindValue if you want to use booleans or floats.

0 votes
answered Jun 29, 2010 by mario

PDO does not escape the variables. The variables and the SQL command are transferred independently over the MySQL connection. And the SQL tokenizer (parser) never looks at the values. Values are just copied verbatim into the database storage without the possibility of ever causing any harm. That's why there is no need to marshall the data with prepared statements.

Note that this is mostly a speed advantage. With mysql_real_escape_string() you first marshall your variables in PHP, then send an inefficient SQL command to the server, which has to costly segregate the actual SQL command from the values again. That's why it's often said that the security advantage is only implicit, not the primary reason for using PDO.

If you concat the SQL command and don't actually use prepared statments (not good!), then yes, there still is an escape function for PDO: $pdo->quote($string)

0 votes
answered Jun 29, 2010 by your-common-sense

Very few people here understands what escaping is and when to use it.
Escaping itself do not make any data "safe". it just escape delimiters, to distinguish a delimiter from a part of data. field = 'it's me' will cause an error, while field = 'it\'s me' will not. That's the only purpose of escaping. So, it works only when you use quotes. If you don't - escaping become useless.

Do you use quotes with placeholders? No. Thus, no escaping would be sensible.

When you use binding, it works very different way.
It does not send the whole query to the server, but send your prepared query separate from the binded data. So it cannot interfere. And thus no injection possible.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...