About the Author

Hi, I'm Chris, a web developer and a founding member of Analog. I live and work in Brooklyn, NY.

The Unexpected SQL Injection

Sat, 29 Sep 2007 at 02:52:27 GMT
5 Comments
References

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 Sat, 29 Sep 2007 at 02:52:27 GMT.

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

Upcoming Talks

Kiwi Foo Camp

12 - 14 Feb 2010

At Mahurangi College, Warkworth, New Zealand.

Webstock

15 - 19 Feb 2010

At Wellington Town Hall, Wellington, New Zealand.

ConFoo

10 - 12 Mar 2010

At Hilton Montréal Bonaventure, Montréal, Canada.

South by Southwest

12 - 16 Mar 2010

At Austin Convention Center, Austin, Texas.

New Comments

Sujoy wrote:: Chris, this is the first time I'm visiting your blog! Your 2009 Highlights is really great! Fanta...; Posted in 2009 Highlights
Giovanni wrote:: Hi Chris! First of all, my persona thanks for all your article about PHP security! it's really u...; Posted in The Truth about Sessions
Chris Shiflett wrote:: Thanks, John. Friendly and trustworthy are high compliments. Much appreciated. :-) Sorry about...; Posted in 2009 Highlights
Eric B wrote:: Hi Chris, Thanks for this clean, concise article on this topic. You are a life saver! -E; Posted in Guru Speak: Storing Sessions in a Database
Radoslav Stankov wrote:: wow, I looks like 2009 wasn't very boring year. p.s. I didn't know you too are Arsenal fan.; Posted in 2009 Highlights