SQL Injection – A Primer

One of the most widespread software vulnerabilities to afflict websites is SQL injection. Poor coding standards, lax security and a misplaced belief that scripting languages are inherently secure can open your website up to numerous and potentially damaging attacks.

Over the course of this post, I will cover the main reasons why you should be concerned and go over some simple steps you can take to make sure that your code is secure.

What is SQL Injection?

Wikipedia defines SQL injection as…

…a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

What causes this type of vulnerability?

By and large the largest culprit is non-escaped user input. As a web developer, it is easy to get caught up in the sheer scale of a large application and to forget to check the finer points. It is all too easy to assume that the user is non-malicious and/or non-inquisitive and as such will submit data to a script in a “normal-user” fashion.

How can user-submitted data be dangerous?

Let’s take for example, a script called login.php which allows users to log into a protected area on a website. Let’s say that there’s a form which gets posted to the script with two elements, ‘username’ and ‘password’.

Within the PHP code, we might query the database for a user record like this:

SELECT *
FROM `users`
WHERE `username` = '$_POST[username]'
AND `password` = '$_POST[password]'
LIMIT 1

Looks straight-forward enough, but if the user were to fill out his username and password both as something like…

‘ OR ” = ‘

…the SQL query we’re generating in PHP would in effect become:

SELECT *
FROM `users`
WHERE `username` = '' OR '' = ''
AND `password` = '' OR '' = ''
LIMIT 1

Which would evaluate as true for both parts of the WHERE clause. As we’ve not specified an order, the most likely outcome would be that the first row in the database would be returned by the query.

If the script allowed us to log in as a website / content administrator, the consequences could be devastating.

My website doesn’t have any sensitive information so I don’t have to worry!

Well… potentially, no. Say we had a script on our website called news.php which we use to keep our visitors up to date with company events. Let’s also say that we pass the database ID of news articles in the query-string with the key “id”:

http://example.flashbulldog.co.uk/news.php?id=666

We may load up the news article using PHP to generate a database query like this:

SELECT *
FROM `news`
WHERE `id` = $_GET[id]

It’s relatively easy for me as an end-user to manipulate the query-string to read:

http://example.flashbulldog.co.uk/news.php?id=666; DROP TABLE news;

Which would result in two queries being executed, instead of one:

SELECT *
FROM `news`
WHERE `id` = 666; DROP TABLE news;

The dangers of this type of attack should now be evident.

Yikes! How do I protect my code?

The solution is fairly simple. Firstly, escape all user-supplied data. Turn all of those single and double quotes from string delimiters into escaped characters and the first example above becomes moot.

Secondly, check that all user-supplied data is in the expected format. With regards to my second example, escaping our input does nothing to stop a script-kiddie from dropping our database table. We need to manually check that the data supplied in the query string is valid and in the format which we expect.

We expect $_GET[id] to be numeric, but it is unsafe to assume that it always will be. In PHP, we can use the is_numeric function to make sure that we are dealing with a number instead of a string:

if (is_numeric($_GET['id'])) {
	// Do stuff
}

Bear in mind that there are plenty of malicious web users out there. Always sanitise user input and always check that user-supplied data is in an expected format and you will be at least two steps towards having a secure system.

Share Button