About the Author

Chris Shiflett is an author and speaker who leads the web application security practice at OmniTI.

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

php|tek

19 - 22 May 2009

At Sheraton Gateway Suites Chicago O'Hare, Chicago, Illinois.

OSCON

20 - 24 Jul 2009

At San Jose McEnery Convention Center, San Jose, California.

New Comments

Ronald wrote:: A little hard for a rookie like me, but useful. I also thought you'd like to know there is a grea...; Posted in A rev="canonical" HTTP Header
Alex wrote:: Aren't you forgetting that the session will expire if _write() is never called? That excludes ...; Posted in
Andy Mabbett wrote:: @Chris Shiflett, #4, belatedly: Google only accepts rel=canonical within the same domain. My s...; Posted in A rev="canonical" HTTP Header
Kenneth Udut wrote:: I've implemented this rev="canonical" idea on http://free.naplesplus.us in the hopes that it catc...; Posted in Save the Internet with rev="canonical"
Mark wrote:: After reading your article and all the comments, what I got out of this was that sessions are not...; Posted in