About the Author

Chris Shiflett

Hi, I’m Chris: web craftsman, community leader, husband, father, and partner at Fictive Kin.


The Unexpected SQL Injection

Alexander Andonov (Mordred) has written an articled called The Unexpected SQL Injection for the Web Application Security Consortium:

We will look at several scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used.

The focus of the article is stressing the importance of filtering input and escaping output, as neither is a substitute for the other, but he does so very clearly with specific examples that include queries that use integer values (sans quotes), user-supplied column names, LIMIT clauses, and LIKE clauses. A number of example exploits are supplied for each case, and he discusses which ones work, which ones don't, and why. It's a good article and worth a few minutes of your time.

On a slightly related note, Paul Reinheimer (who moved his blog for no good reason) has recently posted about addslashes() Versus mysql_escape_string(), and if you're curious about the difference between mysql_escape_string() and mysql_real_escape_string() or just want to see an example that demonstrates why character encoding matters, check out my posts on addslashes() Versus mysql_real_escape_string() and the Google XSS Example.

Happy reading!

About this post

The Unexpected SQL Injection was posted on Fri, 28 Sep 2007. If you liked it, follow me on Twitter or share:

5 comments

1.Michael Peters said:

I'm not a PHP programmer, so maybe I'm missing something, but wouldn't all of these PHP SQL vulnerabilities and problems of which escaping functions to use (the fact that the language has to have a function named "real_" anything seems like an utter failure) be gone if people would just use bind variables and prepared statements?

Obviously you still need output escaping, but that's trivial (especially if you're using a good templating system).

Mon, 01 Oct 2007 at 01:39:37 GMT Link


2.Terry Chay said:

I'm not a PHP programmer, and heck, I've never heard about dynamic SQL or the fact that earlier versions of MySQL didn't support bind variables, but let me proceed to lecture you and why the language you use that powers a good portion of the internet is crap.

Mon, 01 Oct 2007 at 16:02:32 GMT Link


3.Dylan said:

This sums it up quite nicely:

http://imgs.xkcd.com/comics/exploits_of_a_mom.png

Wed, 10 Oct 2007 at 05:33:28 GMT Link


4.Sherri said:

Good article. I've already been intval-ing my integer numbers (and casting other types as well), but the concern about the injected wild cards in LIKE searches was a great tip.

Tue, 27 Nov 2007 at 15:45:42 GMT Link


5.Dave said:

I notice in the article that the original (vulnerable) versions of queries do not contain the grave accent character (`) around table and column names, but the "fixed" versions of the queries do. Does using the grave accent in this manner add any security benefits to queries?

Tue, 10 Feb 2009 at 23:10:05 GMT Link


Hello! What’s your name?

Want to comment? Please connect with Twitter to join the discussion.