What's a SQL Injection Bug?

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.

My new book is here! Apress has just published a new collection of 36 essays from Joel on Software, aptly named More Joel on Software. Get yours today! Available from Amazon.com or wherever fine cheese is sold.

About the Author: I’m your host, Joel Spolsky, a software developer in New York City. Since 2000, I've been writing about software development, management, business, and the Internet on this site. For my day job, I run Fog Creek Software, makers of FogBugz—the smart bug tracking software with the stupid name, and Fog Creek Copilot—the easiest way to provide remote tech support over the Internet, with nothing to install or configure.

Enter your email address to receive a (very occasional) email whenever I write a major new article. You can unsubscribe at any time, of course.