Sanitise your SQL

When I find myself looking through other people’s databasing code, or answering questions on databases, there is one vulnerability that gets ignored far too often - SQL injections.  This class of attack is executed with surprising frequency and, even in the past week or so, an exploit found in Microsoft’s IIS server has been exploited over 500,000 times.

With this post, I aim to explain what SQL injections are, and what the biggest mistakes are when it comes to designing a query structure that is protected against injections.

An SQL injection, according to Wikipedia, is:

A technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.

An example of one such vulnerability, would be the following SQL statement:

SELECT hasAccess FROM `users` WHERE uname='$eUsername'

The reason?  The variable $eUsername could, very easily, be unverified, unsanitised and (therefore) take any value.  This value can then be used to manipulate the SQL query.  If, for example:

$eUsername = "' OR 'i'='i'"

Then the query that will be executed will be:

SELECT hasAccess FROM `users` WHERE uname='' OR 'i'='i'

This presents a key vulnerability.  Now anyone entering the data, as seen above, will gain unrestricted access to my super-fancy, all-powerful, world-dominating, fictional, control panel.  The input variable has been used to form a new, valid, SQL statement - and the use of the OR keyword, negates the relevance of the condition on the username attribute.

This is one of the most common vulnerabilities exploited with SQL.  On some systems, multiple queries can be executed at once, by separating statements by using a semi-colon.  This is often restricted, however, to prevent multiple injections (i.e. you would not be able to append a DROP statement to a SELECT).

The example shown above is incredibly simple, and is by no means the limit of potential for SQL injection and, if you search around, you’ll find many different possibilites - but I’m not going to go into that here.

So how do you protect your system against SQL injection?  Validation and Sanitisation.

There are a number of ways to protect yourself, here are a few:

  1. Limit Input Characters - By limiting the characters that users can make use of, you limit the potential there is for injection.  Removing the available use of slashes and single/double quotes removes most main vulnerabilities.  However, be careful when using this idea with things such as name fields, where strings such as “Dave O’Shea” are entirely valid.
  2. Use Range/Domain Checking - If you expect input within a certain range or domain, accept only these inputs - this drastically limits potential for attack.  Numeric fields, then, pose no threats and a finite domains provide only finite options.
  3. Escape Quotes/Slashes - Escaped characters are largely useless.
  4. Use Different Users - Set up your database with multiple users which are used for different activities.  One may be used for UPDATE/INSERT, but another may only have access to SELECT . This can prevent against malicious database updates when performing a SELECT statement.
  5. Use Stored Procedures - Stored procedures reduce vulnerability, by pre-determining the queries that are used by the database.  Not everything is possible by stored queries, but much can be secured using them.

So, remember, kids… sanitise your SQL and protect yourself against SQL injections.

Permalink

Leave a Reply