PDO - Should I sanitize? if so how?

[bulletcleaners25]

So I've finally decided to port over to PDO after reading the thread posted in here, I already have general knowledge of mysql so it's not difficult just have to learn the process.

But after reading a few tutorials about PDO and reading through the manual, I am a little confused as to if it is really going to protect against attackers. Some say yes, some say no and that we should sanitize our data still, yet they also say PDO escapes for us so we don't need to use mysql_real_string_escape.

What are your views and how do you protect using PDO? I've tried searching google quickly but nothing useful really came up.

 

[callumacrae]

If you're using prepared statements, then you don't need to sanitise it. If you're inserting the values into the query itself, then you're using PDO wrong (but you would need to sanitise it).

 

[misson]

But after reading a few tutorials about PDO and reading through the manual, I am a little confused as to if it is really going to protect against attackers. Some say yes, some say no and that we should sanitize our data still, yet they also say PDO escapes for us so we don't need to use mysql_real_string_escape.

mysql_real_string_escape should only be used with the mysql extension, not with PDO and not with mysqli. Only if PDO is emulating prepared statements (the PDO::ATTR_EMULATE_PREPARES attribute is set to TRUE, or the driver doesn't support prepared statements) will it escape data bound to parameters; otherwise, bound data is kept separate from the statement, so there's never a need for quoting string delimiters. Only data bound to parameters is safe; after all, there's no way for the DB driver to distinguish data from statement when the former is interpolated directly into the latter, since the essence of SQL injection is the confusion of the two.

What are your views and how do you protect using PDO? I've tried searching google quickly but nothing useful really came up.

If you search this site for "PDO quote" using Google (the vBulletin search doesn't seem to index words of less than three letters), the second result is: "Issues regarding Mysql Injection and PDO".

A Google search for "PDO escape" turns up Nathan Long's answer to "Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?" (which answers your question by focusing on the "why" and "how"), and "Escape arguments for PDO statements?". Both of these are on StackOverflow, which additionally has many duplicates.

The documentation for PDO::prepare itself tells you what you ask:

Calling PDO:repare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

 

[essellar]

PDO's prepared statements will protect you from SQL injection attacks, but that isn't where things begin and end.

Most of the data we collect from users will wind up displayed on a web page at some point, and that web page is another vector for malicious (or careless) users. You still need to do things like eliminating HTML where it ought not be in your input validation, whitelisting formatting wherever it's allowable in your application (never rely on blacklists; people are capable of amazing workarounds that would never occur to you) and so forth. You need to consider possibilities like malformed HTML (a simple unclosed italic can screw up an entire web page), JavaScript injection, cross-site scripting and cross-site request forgery. If you allow rich text user input, then an alternate markup system like BBcode (as used in much forum software) or MarkDown (probably best known for its use on the StackExchange and StackOverflow sites) will save you a lot of hassle.

So yes, you still need to "sanitize" user input, but not to prevent SQL injection. You may want to keep guards in place, though, to make your error handling more elegant (offering a "did you mean..." rather than a failure, which at the same time gives a helping hand to users who don't know anything about SQL and a "rannygazoo is not tolerated here" message to the malicious) It's optional rather than necessary, though, and if you miss a trick it won't come back to bite you, it'll just result in unfriendly user errors.