Welcome to End Point’s blog

Ongoing observations by End Point people

You shall not pass! Preventing SQL injection

Greg Sabino Mullane presented a few extremely useful techniques for preventing SQL injection. His advice was mostly based on his recent real-world experience.


The chunk of simple code was causing a potentially very dangerous security breach to the system:

[query … where order_number='[scratch order_number] and username='[session username]']

This code can generate this SQL query:

select * from orders where order_number = '12345' and username = 'alice';

Or this SQL query:

select * from orders where order_number=' ';  delete from orders where id IS NOT NULL;

This is a vulnerability, and you certainly do not want any random stranger to delete records from the "orders" table in your database.

The problem was solved in no time by escaping user input.

Here is Greg's list of recommendations to make SQL injection impossible:

  1. Escape all user input passed to the database.
  2. Log extensively. If this system hadn't logged SQL queries, they would have never noticed anything strange. They used tail_n_mail that tracks PostgreSQL logs and sends out emails whenever SQL exception occurs.
  3. Introduce fine-grained control for accessing and manipulating the database. Split responsibilities between a lot of database users and selectively grant permissions to them. Run your code as the appropriate database user with the most restrictive set of permissions possible.
  4. Database triggers can become very handy. In Greg's case it was impossible to delete the already shipped order because of the triggers assigned to the record.
  5. Have a lot of eyes on the code to eliminate the obvious mistakes.
  6. And finally, if SQL injection is happening, consider shutting down the database server until you find the cause. This is an emergency!


Jeff Boes said...

Not quite worthy of a full post: I brought up a side note here, when the discussion ranged to "how can you track a SQL statement back to the issuing session/user/browser, if the architecture includes load balancers, etc."?

Simple answer: SQL comments. In Interchange-speak, something akin to:

SELECT /* [data session id] */
sku, price
FROM products WHERE ...

Anonymous said...

Nice post, but just escaping strings is not a complete solution to the problem of preventing sql injection, as discussed here:

SQL injection prevention

Jon Jensen said...

Anonymous, the article you mention says "Just escaping quotes is not enough to prevent SQL injection" but that is wrong.

What is described there is incomplete escaping. Any proper escaping has to include both \ and ' on MySQL and some other databases.

Yes, prepared statements and placeholders are the best way to do it, but escaping that's done correctly is perfectly fine as well. (And some database drivers simply interpolate placeholders and escape strings behind the scenes anyway.)