Can parameterized statement stop all SQL injection?

iceagle picture iceagle · Jul 22, 2011 · Viewed 33k times · Source

If yes, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

Answer

Mike picture Mike · Jul 22, 2011

The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:

  1. Those just starting out may have no awareness of SQL injection.

  2. Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_query page, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.

  3. A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.

  4. Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.

  5. Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.

  6. False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.

  7. Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.

  8. Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.