SQL Injection

Provide an example of SQL Injection

A SQL injection attack is exactly what the name suggests – it is where a hacker tries to “inject” his SQL code into someone else’s database, and force that database to run his SQL. This could potentially ruin their database tables, and even extract valuable or private information from their database tables. The idea behind SQL injection is to have the application under attack run SQL that it was never supposed to run. How do hackers do this? As always, it’s best to show this with an example.

SQL Injection Example

SQL injection attacks typically start with user inputs from people filling out a form on a website. A website ‘form’, if you don’t already know, is something you have definitely used – like when you log into Facebook you are using a form to login, where you input your email address and your password.

For our example of SQL injection, we will use a hypothetical form which many people have probably dealt with before: the “email me my password” form, which many websites have in case one of their users forgets their password.

The way a typical “email me my password” form works is this: it takes the email address as an input from the user, and then the application does a search in the database for that email address. If the application does not find anything in the database for that email address, then it simply does not send out an email address to anyone. However, if the applicationdoes successfully find that email address in its database, then it will send out an email to that person with a new password, or whatever information is required to reset the password.

Starting the SQL Injection Process

The SQL that would retrieve the email address in the “email me my password” form would typically look something like this:

SELECT data FROM table WHERE Emailinput = '$email_input'; 

This is, of course, a guess at what the SQL would look like, since a hacker would not know this information since he does not have access to that. The “$email_input” variable is what the user inputs into the form.

Now, the way a hacker would try to achieve his SQL injection is by inputting SQL where the email address would normally go in the form. A hacker would typically start by inserting an email address with a single quote attached to the end. What’s the point of this? Well, let’s suppose that the hacker tries to input an email address that looked like this – pay special attention to the fact that a quote is appended to the end of this email address:

hacker@programmerinterview.com' 

This would mean that the SQL being run would look like this – note that there is now an extra quote at the end of the WHERE statement in the SQL below:

SELECT data FROM table WHERE Emailinput = 'hacker@programmerinterview.com''; 

What does “sanitizing” the user input mean?

Now, what would happen if the SQL above is executed by the application? Well, that really depends on how the application is set up to handle bad SQL. If the application is ready to handle bad input data, then it will do what’s called “sanitize” the input and reject it with an error message saying something like “Incorrect email format”, or something similar.

Sanitizing data is the act of stripping out any characters that aren’t needed from the data that is supplied – in our case, the email address. Once the data is sanitized, then an error message can be returned saying that the data provided was bad.

The error response tells the hacker a lot

However, if the application is not sanitizing it’s input then it will actually run the bad SQL. So, if the application does run the SQL, then we could get something like an HTTP 500 error – note that this is different from the error message he would get if the application was sanitizing its input. The most important thing to note here is that the error message tells the hacker a lot – because it tells him whether or not the application is sanitizing its input. And if the application is not sanitizing it’s input then it means that the database can most probably be exploited, destroyed, and/or manipulated in some way that could be very bad for the application owner.

Let’s say that the hacker now knows that the database is vulnerable, and that he can attack further to get some really good information. What could our hacker do? Well, if he’s been able to successfully figure out the layout of the table, he could just type this code (where the email address would normally go):

UPDATE accounts SET email = 'hacker@ymail.com' WHERE email = 'joe@ymail.com';  

Then, if this malicious code is run by the application under attack, it would look like this:

SELECT data FROM accounts WHERE Emailinput ='Y';

UPDATE accounts SET email ='hacker@ymail.com'WHERE email = 'joe@ymail.com';

Can you see what this code is doing?  Well, it is resetting the email  address that belongs to “joe@ymail.com” to “hacker@ymail.com”.  This  means that the hacker is now changing a user’s account so that it uses  his own email address – hacker@ymail.com.  This then means that the  hacker can reset the password – and have it sent to his own email  address!  Now, he also has a login and a password to the application,  but it is under someone else’s account. 

In the example above, we did skip some steps that a hacker would have taken to figure out the table name and the table layout, because we wanted to keep this article relatively short. But, the idea is that SQL injection is a real threat, and taking measures to prevent it is extremely important.

How to prevent SQL injection attacks?

In our example above, the best way to have prevented the SQL injection attack was by simply having the user input sanitized – for which we also gave a definition earlier. Since we are dealing with email addresses in our example, this means that we should be able to safely exclude certain characters which don’t normally appear in email addresses. Here is a list of characters that would normally appear in emails, and anything else should not be allowed inside the database – the user should just receive an error saying something like “Invalid email address” if he tries to input an email address with any characters other than the ones below:

abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789 @.-_+