Navigate:  Introduction How it Works Do it Yourself Mapping the Database Performing Your Mischief Protecting Against SQL Injection Concluding Remarks

Introduction

SQL Injection caused some major problems a few years back. However, now most applications block against it. Even though this subject is not so applicable as a hacking technique any more, it can still demonstrate an issue that must be taken into consideration whenever two different protocols are interlaced.

SQL Injection is a process that takes advantage of the junction between two languages, specifically the junction between a language and SQL (the language of most databases). It relies on a specific set of user inputs, usually to online forms, to trick the database into doing something that was unintended. Through the use of SQL injection, a smart hacker could gain sensitive information, steal a user's password, open up a more serious security hole, or even delete your entire database. Once this tactic has been accomplished, there is nothing in that database that is truly safe.

Fortunately, SQL injection is extremely easy to protect against. There are a few tricks one can perform to minimize the chance of this being a security threat. Even if you are just looking to implement these, I would still encourage you to read the rest of this article. The general idea behind SQL injection extends beyond this specific instance. It is a possibility whenever two programs meet in such a way that requires one to parse commands for another. Similarly, when programming, this is one of the most difficult "bugs" to catch because nothing is actually performing incorrectly. Therefore, some knowledge about the cause will help to prevent issues caused by parsing from occurring.

How it works

SQL injection takes advantage of a program's need to parse SQL statements prior to sending them to the database. If the program does not do an adequate job in blocking escape characters, additional clauses or statements can be injected inside the original statement (hence the name). To demonstrate how SQL injection works, consider a common page on any website that requires a user login: a forgotten password page. Also, let's assume this example page is being executed using PHP. Keep in mind though, that this tactic is not unique to these two conditions. Any user input that is parsed into an SQL statement by any programming language needs to be considered as a possibility. An example of what this example form may look like is below:

Forgot your password? Enter your email below and your password will be emailed to you

   

When first looking at this form, it is hard to imagine that it could be used to do so much damage inside your database. However, let's take a look at the PHP code that would do a rough parse of this page:

$sql = "SELECT password FROM user_table WHERE email = '$emailV'";

Now that a little is known about the backend of this form, perhaps there is an input that could be constructed to "break" it. What would happen to the PHP statement above if the user inputted the following:

test'; /* $sql = "SELECT password FROM user_table WHERE email = 'test'; /*'";

The PHP parsing will not take into account the quote entered into the user's input and will just insert the input directly into the SQL statement. Since the command is only parsed here (it's executed later), there is no more differentiation between what was the user's input and what was not. The SQL command that would be executed can be seen below:

SELECT password FROM user_table WHERE email = 'test';

Although this command does not do anything particularly bad (in fact it will perform exactly how the script was meant to perform), a more malicious statement can be constructed using the same principal. In the above statement, the key was the single quote. This single quote was parsed in such a way that it ended the username string, leaving the rest of the statement open for more SQL syntax. To demonstrate this, and to show a more devastating use of SQL injection, let's take the theoretical user input below:

'; DROP TABLE user_table; \* $sql = "SELECT password FROM user_table WHERE email = ''; DROP TABLE user_table; \*'"; SELECT password FROM user_table WHERE email = '';
DROP TABLE user_table;

This example shows probably the most devastating example of what SQL injection can do. If your table was not backed up, that data is gone for good. You would have to make an announcement saying your server got hacked and that everyone will have to sign up again. Before getting into more complications for this, let's take a quick trip to the dark side and pretend that we're trying to use SQL injection to steal someone's password.

Do it yourself

Let's say we've found the same forgotten password page as in the previous section. We know someone on this website who stole your pencil in 3rd grade and you are going to get back at them by stealing their password and posting a terrible post from them on this forum. They will be blamed and their life will be ruined. So here's how we begin.

First we need to know whether this solution is even possible. Will SQL injection work on this system? This is actually pretty simple to do. There are two inputs to try: a single quote and a double quote (both should be tried in case the web designed switched their order in statement. The resulting statement would appear:

sql = "SELECT password FROM user_table WHERE email = ''"'";

This statement itself will not give you any passwords what-so-ever. However, websites very often have different error messages for errors and no-matches. As a result, this input would generate an SQL error page instead of a no-users-found or even a success page. If this happens, you know that this page is susceptible to SQL injection.

If you receive a non-standard error message when you input the input above, you know that this website has not placed in protection for SQL injection. This means that we have a chance at stealing our "friend's" password. So now we have to take inventory of what we've got: a website that we can use SQL injection to infiltrate. More importantly, we should make a list of what we don't know:

• The database name
• The database table
• The database field names

In order to find this information out, we'll need to do a process called mapping the database. Essentially, we'll be trying to find information about the structure of the database through trial and error.

Mapping the Database

Taking stock of what we know now, we can begin to map out the database to figure out a way to exploit SQL injection to steal our "friend's" password. It is worth noting that many times when an SQL error occurs, some error pages will actually display the SQL statement. If this happens, most of the need for this mapping is removed. However, if we have to start from scratch, here's an algorithm.

First we'll make a few assumptions about the database. Since we're infiltrating a website that has a user login, we can assume that there is a table that stores all the information about the user: the user's id, the user's username, the user's password, and the user's email address among other things. Since we're going in through an email recover field, we can assume that the SQL statement would look something like:

SELECT password FROM user_table WHERE email = '$user_input'

We'll work from the bottom of our list up. So the first thing we need to find out is the field names for the users' information. Fortunately, this is relatively simple, though it may require some tenacity. We'll design an input that will test these. We already know that the website will generate a different error page for an SQL error and a user-not-found error. Therefore, our input must do three things: finish the first statement in a real fashion, insert our test statement, and comment out the rest of the unnecessary statement. For this example, we'll try and find out the email field name (the bolded part is our input to the text field):

SELECT password FROM user_table WHERE email = 'bad_email' AND emailfield = ''; /*'

There are a few things to note about the statement above. First of all, you want to make sure that the SQL command does not return anything valid. If it does return a valid entry, then someone could get their password emailed to them (so much for being sneaky...). A second thing is the comment characters at the end. We want to make sure that we're executing what we want, so it is a good idea to comment out the rest of the statement instead of trying to fit your input into the statement (just end the statement with your input and comment the rest out). Finally, the input after the 'AND' is what we're using to test the field. After submitting our input, if we get an SQL error page, we know that the field we've tested is wrong. If we get a user-not-found page, we know that we've stumbled across a real field.

This is where we get to be creative. We have to map out all the fields that we wish to use from our little input box. Each time you get an SQL error page, change it slightly to keep testing. Some common email field names are 'email', 'emailfield', 'user_email', 'mail', etc. I think that you can see where the tenacity and creativity come into play. The best advice I would offer at this point is to just keep at it. If they are simple, you'll find them eventually. However, don't waste your entire life trying possible field names; the web developer may have used extremely esoteric names. If you do manage to get all the field names that you need, feel free to continue.

Now we have all the field names from the database that we need. However, these will not do us much good unless we know the database table name. Therefore, finding the database table name is our next task. A similar SQL statement can be constructed using the input to the text field as when we were trying to find the field names:

SELECT password FROM user_table WHERE email = 'bad_email' AND 1=(SELECT COUNT(*) FROM tablename); /*'

The second statement will almost never return a true value, however, it will differentiate between an SQL error (wrong table name) and a user-not-found error (real table name). Again, just as with the field names, you just have to keep trying table names. Some common examples are 'user_table', 'users', 'user_list', and 'members'. Once you find a database table that you believe to be the correct one, all you need to do is test it. Again, this is relatively simple. Also, you'll be happy that there is no guesswork here!

SELECT password FROM user_table WHERE email = 'bad_email' AND tablename.email IS NULL; /*'

When you input the command above, the same set of results applies as before. If you get an SQL error page, you need to find a new table name. However, if you get a user-not-found error, you know that the field belongs to that table. Simply check that the other fields you've found belong to the table as well, and if they do, you're finished with mapping the database!

Performing Your Mischief

At this point, we've mapped out the database to meet our ends. It's time to really get our hands dirty. For reference, let's say we've mapped the database below:

This is probably the most fun part of the whole process: everything comes together. The guesswork is much more fun, and consists of more than simply brute-forcing. It is now time to take a better look at how this password recovery system works. This will take a little specialization in each case, but for this example, let's assume the system works as follows: if a legitimate email address is entered, an email is sent to the email address offering a link to reset the user's password. Let us also assume that the password is stored encrypted inside the database (there's no way to know this, but it is usually safe to assume). This is probably the hardest setup you'd have to face if you've gotten to this point. There is no way to directly get the user's password. Also there is no way to get the encrypted password. This means that there is no way to infiltrate without anyone knowing you've done it. So in this case, we should minimize the number of people who know (only the person whose account you're breaking into will know). The list below details the steps we'll take to do our damage:

• Change the email address to one that we control
• Send the reset password email to ourselves
• Change the password
• Perform our nefarious tasks
• Change the password again
• Change the email address again

Once we've gotten to this point, we only need to engineer two more SQL injection statements, and they will appear very similar. It may be possible to do it in just one, if you can change your email address while logged in. The statement to change the email address is as follows:

SELECT password FROM user_table WHERE email = 'bad_email'; UPDATE user_table SET email='myemail@domain.com' WHERE user='my_friend'; /*' SELECT password FROM user_table WHERE email = 'bad_email';
UPDATE user_table SET email='myemail@domain.com' WHERE user='my_friend';

Once you have done this to change the email address (remember a user-not-found error page means that you've successfully changed the email address), enter the email address you used in the previous statement into the input field to send yourself the reset password email. Then, reset your "friend's" password to whatever you want. Now you are free to do whatever you want with his/her account. Do your damage!

Now it's time to clean up after yourself. This is important to prevent retaliation or legal action (if you did anything worthy of that). The first thing to do is reset the password to something else that no one would guess. Do this the same way as above, entering your own email into the forgot password field. Here you have some options. You can set the email back to the previous email (assuming you know it). This will allow the previous user to recover his account and discover the damage, etc. etc. However, I would prefer to lock this person out completely. Change the email to an email address that no one has access to. If you do this, your "friend" no longer has his/her password and the email address is not his, so he/she has no way to reset it. Short of emailing the site administrator, convincing them that they are not trying to deceive, and getting them to manually reset the account, this person can no longer access their account. The perfect crime!

Protecting Against SQL Injection

I do not wish to promote people to use SQL injection to try and break into their enemies' websites (nor is it likely possible anymore). Instead the previous example was created to explain the methods of protecting against SQL injection. If we were to re-read the example in the previous section, we can identify the steps used access the database. Using these steps, we can engineer a great defense against this technique. Here's the list of what a hacker would have to do:

• Check if it is possible: enter a single and double quote
• Map out the database: guess the table and field names
• Make their own changes: inject their own statement

We'll break these down one by one. The first deals with the heart of the problem, the quotation characters. The easiest way to stop this is to escape these characters. Add an extra command when parsing that adds a backslash in front of the quotation marks. However, you should be careful as this can still be broken. In addition, you would need to escape the backslash character itself as well. Also, to prevent multiple statements from being executed, it would be a good idea to escape the semi-colon as well. Once you've done this, you are essentially safe from SQL injection. However, you can never be too careful. There are extra steps that will make future hacking techniques more difficult.

If you remember, the only way that we knew whether we were succeeding or failing when we were injecting was by the differing error messages. Since the average user won't know the difference between the errors, there's not much of a reason to program in different error messages outside of debug. Therefore, make SQL errors and user-not-found errors look the same, at least in the page they return. Also, do not, for whatever reason, print out the SQL statement for the user to see (unless you are using it for debugging purposes, but immediately remove it).

The next task on the hacker's list is to map out the database. This too can be easily stopped. The first way is to implement a maximum number of failed tries before the system locks down for that IP. This would delay and frustrate the attacker, but not stop them (still a good technique to implement to stop brute-forcers). The ideal way to stop this step is to add a fair amount of creativity to your field and table names. Remember, you are the only person who will see them, so just go nuts. Instead of 'user_table', make it 'user_haha_no_sql_injection_table' or 'user_access_denied_table'. You get the idea. Since the would-be hacker would have to guess at the table, just make them un-guessable.

The last task on the hacker's list is to inject their own statements into your statement. The easiest way to protect against this is to limit the size of the user's input. This can be done in the HTML for the webpage, however, this is easily bypassed. Therefore, you'll have to include a check for it in the backend for the form. Again, not so difficult to do, but severely limits a would-be hacker’s options.

There are also some other techniques that are good to implement. They are not geared towards stopping SQL injection, but more towards letting you, the site administrator, know that someone has attempted it. Since you read a few paragraphs previous, you have already implemented the design to give the same error page on both types of errors. However, you can still implement different back-ends for the two errors. You can have the website either update an administration log or send an email to an administration email upon database errors. In addition, the email should include the parsed SQL statement, so you can see if someone is up to mischief. If you also include the user's IP, you can IP ban them, or take other actions as you see fit.

Concluding Remarks

Well, that's really all I have to say about SQL injection. It gets a bit more complicated, but the basic principles are still the same. If you are more interested, feel free to read up on it in other places. Wikipedia has a nice article on different techniques used by hackers besides the differing error pages. Remember, this is applicable to more than just SQL databases on webpages. Anytime commands are parsed, you can run into this problem. Also, it may happen unintentionally in non-user input situations. If this happens, it creates a bug that is EXTREMELY difficult to track down. So just keep this in mind and good luck!