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

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.

Dutch PHP Conference

10 - 12 Jun 2010

At TBD, Amsterdam, Netherlands.

O'Reilly Open Source Convention

19 - 23 Jul 2010

At Oregon Convention Center, Portland, Oregon.

New Comments

Niall Kelly wrote:: Having tried other methods without success and looked through plenty of bloated documentation, th...; Posted in Git on Snow Leopard
liukang wrote:: I have problem with this example. In my php.ini magic_quotes_gpc is off so i'm using only addsla...; Posted in addslashes() Versus mysql_real_escape_string()
RyanTheGreat wrote:: Well, I'm not Chris, but I will do my best to address the questions raised in the comments by Ian...; Posted in Security Corner: Cross-Site Request Forgeries
Chris Shiflett wrote:: Thanks for the kind words, Simon. I'm glad you liked the tutorial. In case it's helpful, here'...; Posted in Webstock
Chris Shiflett wrote:: Hi Robin, I plan to post something about it, but it's going to be hard to express everything i...; Posted in Webstock