How to protect against SQL injection, and why SQL injection is dangerous

I am quite sure that most of web developers know what the SQL injection is, and nowdays it seems that even junior developers know basics on how to protect web applications against this type of hacker attack. However, I often see web developers underestimate the level of potential threat. Some web developers think: "We are too small to be interesting for hackers", but they forget that security wholes in their applications can be used to attack other resources, systems and users.

In this post I am not going to show how to attack web applications using an SQL injection, but I'd like to show why SQL injection is dangerous and also how to protect against SQL injection.

What is SQL injection?

First of all, lets see what the SQL injection is.

SQL injection - is one of very common types of attack on web applications that use relational databases such as MySQL, PostgreSQL, MS SQL, etc. The idea of the attack is based on the insertion (injection) and execution of some SQL code through an input field or GET request parameter in the URL.

Here is how the attack may look in real life. Suppose your site has a news section where you display your current news, and when a visitors clicks on one of those news, you display it.

  http://yourdomain.com/news.php?id=1

Your SQL query may then look like this:

  SELECT * FROM news WHERE id=1

Let's now modify a bit the GET request and put some extra parameters in it to change the logic of the query:

  http://yourdomain.com/news.php?id=1+OR+1=1

Now, let's see how your SQL query will look then if you don't protect your application:

  SELECT * FROM news WHERE id=1 OR 1=1

As a result, the SQL query will return all news instead of just one. This is a very simple and silly example, just to give you an idea. Now think about a query that is used for user authentication...

  $_POST['username'] = 'admin';
  $_POST['password'] = "' OR ''='";

  SELECT * FROM users WHERE user='admin' AND password='' OR ''=''

Doesn't look good, right? This is what may happen if you do not protect your web application.

Another example of SQL injection

There are a lot of different techniques to attack web sites with SQL injection. One of the most common is the use of UNION statement.

  http://yourdomain.com/news.php?id=-1+UNION+SELECT+username+FROM+user

  SELECT title FROM news WHERE id=-1 UNION SELECT username FROM user

Using the GET request described above a hacker can get a username, or password, or anything else from other tables in your database.

Why SQL injection is dangerous?

Keep in mind, that using an SQL injection a hacker may execute almost any SQL statement to get data from your tables, insert data, drop data, or even get/execute files on your server.

Again, don't forget that security wholes in your application can be used to attack other websites (e.g. on a shared hosting system).

How to protect against SQL injection?

SQL injection can be inserted to one (or few) of your SQL statements used in the application through a GET parameter or an input field. So, to prevent those attacks you'll need to check all data that comes from GET/POST requests and used in your SQL queries.

Here are a few basic and most important rules on how to protect against SQL injection:

1. Do not trust users and incoming data

Any data that comes to your application, whether from POST or GET, you should treat this data as suspicious. To protect against SQL injection always carefully filter all incoming data that will be used in SQL-query. Do not accept data if its type or length is not expected! For example, if you expect an integer value, make sure that you get an integer number. If you expect a string with the maximum length of 10 characters - truncate the string.

Use PHP functions to validate and secure incoming data:

  $id = intval($_GET['id']); // Get the integer value of a variable
  $lang = substr($_GET['lang'], 0, 2); // Get only 2 letters from the GET parameter 'lang'

2. Escape Quotes in SQL.

Adding quotes is one of the most common techniques to attack web sites. Always escape quotes in your quesries using PHP functions:

  $query = sprintf("SELECT * FROM user WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

3. Use prepared statements and parameters.

You can use libraries (e.g. PDO in PHP) for prepared statements and parameterized queries. It'll escape values in your queries and make them secured.

$statement = $pdo->prepare('SELECT * FROM news WHERE id = :id');
$statement->execute(array(':id' => $id));

Conclusion

As I mentioned in the beggining of this post, there is a lot of web sites that aren't protected against SQL-injection, so I hope this post will help someone to better understand the danger and methods of protection against this danger.