How to prevent SQL injection attacks?

In our earlier tutorial on SQL Injection, one way to have prevented the SQL injection attack was by simply having the user input sanitized – which we briefly discussed. 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
! $ & * - = ^ ` | ~ # % ' + / ? _ { } @ . 

Sanitizing input is not enough to prevent SQL injection

Unfortunately, just sanitizing user inputs is not enough to prevent SQL injection – as you will see in the examples below. So, let’s explore some other options and see what works and why – it’s good to know all the options, so be sure to read everything.

What about escaping strings? Shouldn’t this remove the threat of quotes in SQL injection?

In case you forgot what “escaping” means in the context of programming, basically it’s just allowing special characters (like single/double quotes, percent signs, backslashes, etc.) in strings to be saved so that they remain as part of the string, and are not mis-interpreted as something else. For example, if we want to include a single quote in a string that gets output to the browser in PHP (note in the word “it’s” we have a single quote that will be output), then we have to add a backslash to the single quote so that PHP outputs it as a single quote:

echo 'Programmer Interview - It\'s Great!';  

So, when this is displayed on a webpage it will look like:

Programmer Interview - It's Great!

This is what’s called escaping strings. If we did not escape the quote in our string then it would not output anything, and would result in a PHP error because the quote is also used to enclose the characters in an echo statement.

Now, how would escaping the quotes have helped in our previous example? Remember our hacker is trying to input this harmful/malicious code into the email form field:

     Y';
     UPDATE table
      SET email = '[email protected]'
      WHERE email = '[email protected]';

What if we escape the quotes in the string above before we pass the SQL to the database? Well, that would mean the quotes in the string become a part of the string that is searched for using the Emailinput field – in effect the query is searching for an email address that is equal to that giant string. In other words, the quotes are part of the string literal, and will not be interpreted as SQL. In MySQL, we can escape a quote simply by prepending a quote with another quote – basically 2 single quotes will be interpreted as one quote – which is what we do in the example below. So, the actual SQL that will be run looks like this:

SELECT data
FROM table
WHERE Emailinput = “ Y''; --the quote after the Y is escaped
UPDATE table SET email = ''[email protected]'' -- escape quotes
WHERE email = ''[email protected]'' ”; --and, more quotes escaped

The key in the example above is that the quotes are now being treated as part of a string that gets compared to a field in the table, and NOT being translated as actual SQL – it’s very important that you understand the distinction because it is exactly the problem that escaping quotes solves for us.

If we do not escape quotes, it allows those quotes to become part of the SQL, and basically allows the hacker to run 2 statements at once – which is exactly what is so dangerous. The 2nd statement (the “ UPDATE table SET email = ‘[email protected]’ WHERE email = ‘[email protected]’;”) is what really messes things up, because it allows the hacker to change the email address of an existing account to his own email address. And, that 2nd statement is only allowed to run because the quotes are not escaped. Escaping a string is also known as quotesafing, since you are essentially making the SQL query “safe” for quotes.

Just Escaping Strings Does Not Prevent SQL Injection

Although we went through an example in which escaping the string prevented the SQL injection attack, just escaping strings is actually not enough protection against SQL injection attacks. A decent hacker can run another attack, by exploiting the fact that some databases allow people to escape strings in more than just one way. MySQL actually allows you to escape quotes in a variety of different ways – in fact as you can see below in some information pulled straight from the MySQL reference pages, you can easily escape quote characters by preceding them with a backslash – a “\” :

There are several ways to include quote characters within a 
string that goes into a MySQL query:
1.A “'” inside a string quoted with “'” may be written as “''”.
2.A “"” inside a string quoted with “"” may be written as “""”.
3.Precede the quote character by an escape character (“\”).

Let’s say that we choose to escape quotes manually by just adding a single quote every time a string comes in with a quote. Because, if we have a name field, we want to allow people with quotes in their name to be able to save their name without any issues – for instance, someone with the name Jack O’Leary should be able to be saved in our database without the quote causing any issues.

So, if we are retrieving someone’s name from our database, then the SQL may look like this:

SELECT *
  FROM customers
 WHERE name = 'Jack O’’Leary';  -- this works great

And this works perfectly fine because the double quotes will be interpreted as a single quote, and MySQL will search for Jack O’Leary (with one quote), and not Jack O’’Leary (with 2 quotes).

But, let’s say a clever hacker realizes that you may be running a MySQL database, and knows that MySQL also allows you to escape quotes by preceding the quote character with a backslash – so a quote could also be escaped like this: \’

So, our clever hacker tries to insert a string like this into the email field on our form:

\'; DROP TABLE users;

But after we do our own manual string escaping (by adding the extra quote), that string turns into this:

\''; DROP TABLE users; --

So, the SQL that is run will look like this:

SELECT *
  FROM customers
 WHERE name = '\''; DROP TABLE users; --';

What happens when this SQL is run? Well, the ‘\’’ gets interpreted by MySQL as a string with a single quote, meaning that the system will just search for a name with a single quote. The 2nd quote (the one that comes after the \’), will allow the hacker to close the first statement, insert a semicolon, and then run another malicious statement (the DROP TABLE users; code).

The hacker essentially fools the system into NOT escaping one of the extra quotes by taking advantage of 2 things here:

• 1. The application developer is trying to escape quotes himself by just appending an extra quote.
• 2. MySQL supports escape mechanisms other than just appending a quote. In this case, the hacker also used the backslash escape mechanism to run his malicious code.

Remember, the quotes are key because it allows the hacker to close one statement and run any extra statement of his or her choosing.

Let’s repeat this again: Just escaping quotes is not enough to prevent SQL injection

The lesson here is that escaping quotes is unfortunately not enough to prevent all SQL injection attacks, and also extremely difficult to do correctly on your own. And because of the latter, many languages that provide database interface libraries have a function that will handle escaping strings for you. These functions will handle both parsing of the string and quotesafeing as well – so when you use those functions you have a much better chance of getting things done correctly.

If you are looking for actual examples of those functions, PHP has a function called mysql_real_escape_string and Perl’s DBD module has a function called quote. You absolutely should be using these functions before using form data in your queries.

The best way to prevent SQL Injection – Prepared Statements

But, the best way to prevent SQL injection is to use prepared statements. You can (and should) read about prepared statements and their role in preventing SQL injection here: Prepared Statements and SQL Injection