How to (NOT) put holes in your application: SQL injections

04Dec'11

Written by Leonardo Fischer

Hi,

I was asked at work to give some help to a colleague about SQL injections. He doesn’t know too much about the topic, so I wrote him a mail introducing the problem. This post is a little more than that mail has.

Note: this is an introduction to SQL injections, not an “all you need to know”. Use it to introduce yourself to the problem and learn how to avoid the most common mistakes. But don’t believe that it will solve all your security problems. This book covers a lot of points of SQL injections and ways to avoid it. And this other book covers several other points on securing your website.

Who invented SQL injections?

I like the “learn by example” method. So let’s start with one. Suppose you are the newest employee at Google. Your first task is to rewrite the login page for the Google products because it has low performance on the server side. (Off course, things at Google should not go the way I’ll describe here. It’s just an example.)

Remembering, this is the page you will deal with:

You open the current version, see lots of stuff and you don’t understand why so much code for a simple task such as find the user on a table. Then, you delete everything and start from scratch.

Suppose that you need to code in Java, in one of those web frameworks. You start by creating a code like this:

String username = getParameter("username");
String password = getParameter("password");
String sql = "select * from users where username = '" + username +
    "' and password = '" + password + "'";
// the code that runs the query here...

Saving the password’s has is itself a topic for another post, so let’s assume that the password is stored as plain text. Now you start to test. You fill the login form as follows:

Your code will create and execute the following SQL:

"select * from users where username = 'testuser' and password = 'testpass'"

Day 1, 1:30pm: you make several tests to ensure that the page just let pass users with the correct username and password.
Day 1, 3:00pm: your code is working and running very fast, due to its simplicity. You submit it to the repository.
Day 1, 4:30pm: another employee picks up your code from the repository, and generates a new package. And then sends it to production.
Day 1, 10:00pm: one automatic job picks the package with your code and releases to the public.
Day 2, 11:00am: several tech blogs writing news about security issues in the Google login.
Day 2, 2:00pm: you are screwed.

“Help-me!! I don’t know what I did wrong!!”

Hackers are everywhere and some of then use Google. And some of those hate Google’s new design. One of them has filled the login form as follows:

How strange is this username… Let’s see the generated SQL query.

"select * from users where
    username = 'somegoogleusername' -- ' and password = 'thepassdontmatter'"

Did you get the SQL injection?

The way that you’ve built the SQL combined with the values that the malicious user filled on the forms resulted in a query that has nothing to do with selecting one specific user from the database and checking his password. On the first point, it is searching for the username somegoogleusername. Remember that the -- in several SQL dialects means the same as // in most common programming languages: a comment until the next line break. The comment on the SQL forces an “ignore the password comparison” behavior. Resuming, the malicious user gained access to somegoogleusername account. If this is bad, imagine someone filling the username '; drop table users;--.

So, what should I do?

Please, don’t think about using complex table names or change the order of parameters in the SQL. This will just make the hacker’s job approximately 0.75% trickier.

First, what you should never do again: build a SQL with parameters by just concatenating strings. You still could use this approach if you remember to escape special characters on the parameter values. But this way is error prone, as is easy to forget to escape one parameter. Also, your code will be a lot harder to read and maintain latter. MySql with PHP lets you do this through the mysql_real_escape_string() function.

Now I’ll show you a more interesting solution. Good APIs to manage your database through code provide some ways to insert parameters in a SQL query.

The way that I like most is the “named parameters” approach. You will write the query using some placeholders for the parameters, and these parameters will be provided at the time of the execution of the query. Hibernate support this in the following way:

String sql = "select * from users where username = :pusername "+
    " and password = :ppassword ";
Map params = new Map();
params.put( "pusername", getParameter("username") );
params.put( "ppassword", getParameter("password") );
// the code that runs the query here...

One other way is “positional parameters”, which uses a question mark (?) instead of a name preceded by a colon (:). I don’t like this one because if you rewrite the query and change the position of the parameters, you need to change the order of the values in the list you pass to the API (yes, I’m lazy). But you still can use this if you like. The Java JDBC API provides the PreparedStatement for this.

And there are other ways to do this. The Grails framework supports the “criteria” way to write SQL queries: you write the SQL code in Groovy, and the framework takes care to build the SQL string for you.

I believe that these two are the most common among several languages/programming APIs. But there are others for specific frameworks.

I hope this helps you understand SQL injections. If you have any doubt about the topic, please let me know. If you already know about the topic, leave your thoughts about it on the comments.

Tags: SecuritySQL

No comments yet