[A picture of private offices at Fog Creek Software] Alert! This ancient trifle retrieved from the Joel on Software archive is well-past its expiration date. Proceed with care.

Joel on Software

What's a SQL Injection Bug?

by Joel Spolsky
Wednesday, November 01, 2006

I tried to sign up for an online site.

The signup page wanted a secret question and secret answer. For the secret question, I put “what is aunt Vera's cat's color”. It complained about the apostrophe in the question. OK, fine. I deleted that apostrophe.

For the secret answer, I put “Aunt Vera doesn't have a cat.”

And I got this:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't have a cat', 'male')' at line 1

This means that the programmers are in the habit of taking strings that they got from the user (i.e. GET or POST parameters) and concatenating them together with other bits and pieces of SQL to generate SQL statements.

For example, in PHP with PostgreSQL:

$x = pg_query("select * from accounts where name='" . $_GET["name"] . "'");

(For non-PHP programmers: “.” is the string concatenation operator).

I'm not surprised that they are in the habit of doing this; a lot of programming books, tutorials, and documentation use examples like this.

Unfortunately it's a gigantic security hole called SQL injection.

The user, if malicious, can close the string that you opened, finish your select statement, put in a semicolon (the SQL statement separator), and then type any SQL code they want, and it will run.

So, for example, if the user supplies this as name:

foo'; delete from accounts --

... the SQL statement executed will be:

select * from accounts where name='foo'; delete from accounts --'

... which will do exactly what it looks like: it will delete the entire table of accounts.

This is an extremely common problem: Michael Sutton did a little research project and found that 11.3% of web applications have SQL injection vulnerabilities.


Have you been wondering about Distributed Version Control? It has been a huge productivity boon for us, so I wrote Hg Init, a Mercurial tutorial—check it out!

Want to know more?

You’re reading Joel on Software, stuffed with years and years of completely raving mad articles about software development, managing software teams, designing user interfaces, running successful software companies, and rubber duckies.



About the author.

I’m Joel Spolsky, co-founder of Fog Creek Software, a New York company that proves that you can treat programmers well and still be highly profitable. Programmers get private offices, free lunch, and work 40 hours a week. Customers only pay for software if they’re delighted. We make Trello, easy web-based collaboration software, FogBugz, an enlightened bug tracking and software development tool, and Kiln, a distributed source control system that will blow your socks off. I’m also the co-founder and CEO of Stack Exchange. More about me.

© 2000-2014 Joel Spolsky