How does PHP PDO's prepared statements prevent sql injection? What are other benefits of using PDO? Does using PDO reduce efficiency?

12
votes

I have been spotting the sentence PHP PDO's prepared statements prevents SQL injection.

  • How does php PDO's(PDO's prepared statements) prevent sql injection?
  • What are other pros/cons of using PDO(PDO's prepared statements)?
  • Does using PDO(PDO's prepared statements) reduce efficiency?

I have read this: Are PDO prepared statements sufficient to prevent SQL injection? But the data there is not completely clear.

3
phppdosql-injection
Its not PDO, that prevents you from SQL-injections, its prepared statements. You can use PDO without using its prepared statements and also other extensions (MySQLi, SQLite3, ...) support them too. – KingCrunch
I will revise my question. :) – ThinkingMonkey
may be this one would be more clear: MySQL / PDO / Prepared Statements - All a big jump, a bit overwhelming and a little confusing? – Your Common Sense
@Col.Shrapnel reading. Will need time at it is a long one. – ThinkingMonkey
Funny enough, in the question you linked to, there is my [deleted] answer saying that the accepted answer makes no sense. This site works strange ways - nonsense answers being upvoted and propagated while good and reasonable answers remains obscured. My guess it is because very few people can really understand the meaning and most of them just believe that some answer is right. There is another question with my explanations on the matter: stackoverflow.com/questions/8263371/… – Your Common Sense

3 Answers

8
votes

Well, at second glance your question looks more complex to be answered with just one link

How does php pdo's prepared statements prevent sql injection?

How can prepared statements protect from SQL injection attacks?

What are other pros/cons of using PDO?

Most interesting question.
A greatest PDO disadvantage is: it is peddled and propagated a silver bullet, another idol to worship.
While without understanding it will do no good at all, like any other tool.
PDO has some key features like

  • Database abstraction. It's a myth, as it doesn't alter the SQL syntax itself. And you simply can't use mysql autoincremented ids with Postgre. Not to mention the fact that switching database drivers is not among frequent developer's decisions.
  • Placeholders support, implementing native prepared statements or emulating them. Good approach but very limited one. There are lack of necessary placeholder types, like identifier or SET placeholder.
  • a helper method to get all the records into array without writing a loop. Only one. When you need at least 4 to make your work sensible and less boring.

Does using PDO reduce efficiency?

Again, it is not PDO, but prepared statements that reduces efficiency. It depends on the network latency between the db server and your application but you may count it negligible for the most real world cases.

1
votes

  1. The primary method PDO uses to prevent against SQL injection is preparing statements with parameters in the query and supplying values when the query is executed. PDO will automatically take care of escaping quotes and other characters in the values. As long as you do this in every query, and not put values directly in the query, you are protected against SQL injection. The answers in the question you linked to show how this is done.

  2. One of the main advantages of using PDO, or any DBA, is that PDO encapsulates the low-level communication to the actual DB, leaving you to only deal with the actual query logic. It lets you change which database you're using (MySQL, Postgre, etc.) with minimal effort. It also makes it easier to work with master/slave setups and read replicas.

  3. In most cases using PDO will only be marginally slower than direct function calls. In any case, the slight decrease in performance is well worth it.

1
votes

How does php pdo's prepared statements prevent sql injection

Rather than being annoying about nitpicking this question, I'll give you the answer to the real question: prepareing a query essentially runs mysql_real_esape_string or some equivalent on each token (represented by a question mark or :value). This makes it easier to make sure all variable data is properly escaped. This does not prevent all security problems (for example, % and _ are not escaped, which can impact LIKE clauses).

What are other pros/cons of using PDO?

As far as I know there are no cons to using PDO. I suppose a con is that it does not support all known DBs .. there are limited drivers, but this is only a con if you want to use PDO for a DB that it cannot support. Pros? Well you get a lot of flexibility out of PDO, especially if you create a wrapper for it (just in case you needed to switch DBAs), and since it's compiled C it's supposedly faster than using other php functions (see below). It also saves you from having to write your own methods to prepare queries, etc.

Does using PDO reduce efficiency

Reduce efficiency compared to what? What kind of efficiency? Programming efficiency, or execution speed? As I understand it, PDO is compiled so using it should actually be faster than creating your own DB wrapper to prepare queries and such. If this is really a concern, you can benchmark the difference, but I suggest you look elsewhere for slowdowns first.