SQL injection is still here

The first SQL injection bug I wrote was in PHP, in about 2002, in a guestbook. I know because I found it myself a couple of years later, reading old code and suddenly understanding what the word “injection” in an article I’d read meant for a line I’d written. Nobody had ever exploited it, as far as I know, mostly because nobody had ever visited the guestbook.

I thought about that guestbook yesterday reading Ian Carroll and Sam Curry’s write-up, “Bypassing airport security via SQL injection”. Their story, briefly: FlyCASS is a web application that some smaller airlines use to manage their crew in the TSA’s Known Crewmember program, the one that lets pilots and flight attendants skip the regular screening line, and in CASS, which handles cockpit jump-seat access. Its login form sent the username straight into a SQL query. The researchers noticed because a stray character produced a database error message, which is about the oldest sign in the book, and from there they got administrator access for an airline in the system, which would have let them add people as crew. They reported it to DHS in April; FlyCASS was cut off from the programs, and the TSA disputes how far the access would actually have gone. I’m not going into the details beyond that, and their post doesn’t need my help.

It’s 2024. The xkcd strip with Bobby Tables is nearly seventeen years old. SQL injection was #3 on MITRE’s CWE Top 25 list last year. So I want to do two things here: explain how it works, properly, for anyone who’s only ever used an ORM, and then go through why it keeps happening, which is the more interesting question.

How it works

A database receives SQL as text, and the first thing it does is parse that text into a structure: this is a SELECT, from this table, with this condition, comparing this column to this value. Everything after that, planning, executing, returning rows, works from the structure.

The bug happens when the program builds that text by gluing strings together and some of the strings came from a user. In C# it looks like this:

// Don't do this
var sql = $"SELECT id, name FROM users WHERE email = '{email}'";
using var cmd = new SqlCommand(sql, conn);

The author’s mental model is that email is a value that goes inside the quotes. The database doesn’t know about the author’s mental model. It receives one string and parses all of it. If the user’s input contains a quote character, then as far as the parser is concerned, the string literal ends there, and whatever follows the quote is SQL. The user hasn’t broken out of anything in the program; they’ve just written part of the query, because the program asked them to.

That’s all injection is: data crossing into the code side of a language. The same idea is behind cross-site scripting (data becoming HTML or JavaScript), shell injection (data becoming shell syntax), and, these days, prompt injection, where text a language model reads gets treated as instructions. SQL is just the oldest and best-known case.

Why parameterized queries fix it

The fix isn’t escaping the quote, though for a long time that’s what people did, and PHP even had a feature, magic quotes, that tried to do it for you on every request, which was removed in 2012 after causing more trouble than it saved. Escaping is a fight on the parser’s own terms: you have to know every character that’s special, in every context, in every character set, for this particular database, and you have to never forget to do it. People got it wrong constantly, including in some famous ways involving multibyte character sets.

The real fix is to never put the input into the SQL text at all:

var sql = "SELECT id, name FROM users WHERE email = @email";
using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@email", email);

Now the query text is a constant. With most databases and drivers, the text and the values travel separately. In Postgres’s protocol, for example, the client sends a Parse message containing the SQL with a placeholder, then a Bind message containing only the values, then Execute. The structure of the query is fixed before the user’s input even arrives, and the input is only ever a value of a type. There’s nothing to escape because nothing gets parsed.

(Some drivers and ORMs do the substitution on the client side instead, with careful, well-tested quoting, and send one string. PHP’s PDO does this by default with MySQL. That’s fine when the library gets it right, and the big ones do. The point is that you’re not the one doing it.)

Every mainstream framework now does this by default. ActiveRecord with a hash condition, Entity Framework, Dapper with an anonymous object, Django’s ORM, all of them produce parameterized queries without you thinking about it. Which is the reason the bug is rare in new code from competent teams, and also, I think, part of the reason it isn’t extinct.

Why it’s still here

The first reason is the obvious one. There’s a huge amount of old code, and a huge amount of code written by small shops for small markets, where nobody has been paid to look at it in years. A crew-management web app for a handful of small airlines is exactly that kind of software. It’s niche, it works, it probably had a few customers and one developer, and nothing about its market rewards a security review until someone like Carroll and Curry comes along.

The second is that string building is always the easiest thing to type. The API for running a query takes a string. String interpolation is right there and it reads nicely. Tutorials, and now AI code assistants trained on years of tutorials, produce the concatenated version often enough that someone always has to catch it in review.

The third is the one I find most interesting, and it’s about ORMs. When 95% of your queries are safe by construction, people stop thinking about the other 5%, and the other 5% is where the escape hatches are. These are the ones I see over and over:

• Sorting. You can’t use a parameter for a column name or for ASC/DESC, only for values. So code that sorts by a column the user picked from a table header builds that part of the SQL as a string. Rails used to let you pass a raw string to order, and recent versions now refuse anything that doesn’t look like a column name, for exactly this reason.
• IN lists. A parameter is one value, and IN (...) wants a variable number of them, so people join the values into a string. Most libraries have a safe way to do this now, and people still write the join.
• LIKE. A parameter protects the query’s structure, but the value inside a LIKE pattern has its own special characters, % and _. One of the levels in the Stripe CTF I did in 2012 was exactly this: a properly parameterized query that still leaked everything, because the input went into a LIKE pattern and nobody escaped the wildcards. Not injection in the strict sense, but the same mistake, data being treated as syntax.
• Dynamic SQL in stored procedures. In SQL Server you can build a string inside a procedure and run it with EXEC. The procedure’s caller passed parameters safely, and then the procedure concatenated them into a new string. Moving the concatenation into the database doesn’t make it go away. sp_executesql with parameters is the fix there.
• Reporting and admin code. Internal tools get less review, run with more privileges, and are much more likely to have a hand-written query with filters glued on.

What I found in ours

So, because I’d just been reading about airline crew access, I spent yesterday afternoon and some of this morning grepping our own code. We’re mostly Ruby and C#, on Postgres, and I searched both for SQL being assembled from strings: interpolation inside strings that contained SQL keywords, find_by_sql, .where with interpolation, order and pluck with anything that wasn’t a literal, and in the .NET services, NpgsqlCommand and Dapper calls whose SQL wasn’t a constant.

That turned up 34 places. Thirty only interpolated constants or identifiers from a fixed list in the code. Three used values from our own configuration, which isn’t user input, though I’d still rather they didn’t. And one, in an internal admin report, took the sort column straight from the query string. You had to be logged in as one of our admins to reach it, so I don’t think it was exploitable from outside, but I’ve stopped finding “it’s behind admin auth” as reassuring as I used to. Admin accounts are exactly the ones a phishing email goes after. The sort column is now checked against a list of allowed columns, and the other three take their values as parameters.

The grep itself was messy and I’m not confident it found everything. A static analysis tool would do better, and we have one in CI, which flagged none of these 34, because each of them was assembling SQL in a way the rules didn’t recognize. I’m not sure whether that’s a failure of the tool or a sign that most of the 34 really were fine. Probably some of each.

The guestbook, for the record, was taken down years ago. The site it was on is gone, and so is the hosting company.