How you can prevent an SQL injection

Placed in: Home

About the author:
The write up has been done by Joanna who works with WebHostingSearch and has been been part of web industry from quite some time now.

An SQL Injection (also known as "Failure to Preserve SQL Query Structure") is one of the most common and most dangerous security issues. SQL injections are dangerous because they are a door wide open to hackers to enter your system through your Web interface and to do whatever they please - i.e. delete tables, modify databases, even get hold of your corporate network. SQL injections are a programming error and they have nothing to do with your web site hosting provider. So, if you have been searching for a secure JSP hosting, PHP hosting or any other type of web hosting packages, you need to know that prevention of an SQL injection is not a responsibility of your web site hosting provider but of your web developers.

Why an SQL Injection Occurs

SQL injections might be common but, ironically, they are also easy to prevent. SQL injections are common because SQL injection vulnerabilities are abundant (i.e. it is so easy to do) and because if an SQL injection is successful, the reward for the hacker could be substantial (i.e. a whole database to tamper with).

SQL injection risks arise every time when a programmer creates a dynamic database query, which contains user input. This means that the ways to prevent an SQL injection are two:

Don't write dynamic database queries
Don't allow user input in your queries.

The solution might be simple but it is impractical because dynamic queries are too useful to be avoided and user input is inevitable. This doesn't mean that SQL injections can't be prevented. With some coding techniques applicable in any programming language, SQL injections can be prevented.

What Can Be Done to Prevent an SQL Injection

Though the exact code differs depending on the programming language you use, the basic principles to prevent an SQL injection are similar. Here are some examples how you can do it:

Use dynamic SQL only if absolutely necessary.

Dynamic SQL can almost always be replaced with prepared statements, parameterized queries, or stored procedures. For instance, instead of dynamic SQL, in Java you can use PreparedStatement() with bind variables, in .NET you can use parameterized queries, such as SqlCommand() or OleDbCommand() with bind variables, and in PHP you can use PDO with strongly typed parameterized queries (using bindParam()).

In addition to prepared statements, you can use stored procedures. Unlike prepared statements, stored procedures are kept in the database but both require first to define the SQL code, and then to pass parameters.
Escape user input.
Escaping user input is less effective than parameterized queries and stored procedures but if parameterized queries and stored procedures can't be used, escaping user input is still more than nothing. The exact syntax for escaping user input varies depending on the database, so you need to check your DB docs for the correct syntax and examples.
Assume magic quotes is always off.
When the magic_quotes_gpc variable is off, this can prevent some (but not all) SQL injection attacks. Magic quotes are not an ultimate defense and what is worse - sometimes they are off and you don't know about it. This is why it is necessary to have code for the substitution of quotes with slashes. Here is a neat piece of code Jon Lee suggests:
```
 
$username = $_POST['username'];
$password = $_POST['password'];
if (!get_magic_quotes_gpc()) {
   $username = addslashes($username);
   $password = addslashes($password);
}
```
Install patches regularly and timely.
Even if your code doesn't have SQL vulnerabilities, when the database server, the operating system, or the development tools you use have vulnerabilities, this is also risky. This is why you should always install patches, especially SQL vulnerabilities patches, right after they become available.
Remove all functionality you don't use.
Database servers are complex beasts and they have much more functionality than you need. As far as security is concerned, more is not better. For instance, the xp_cmdshell extended stored procedure in MS SQL gives access to the shell and this is just what a hacker dreams of. This is why you should disable this procedure and any other functionality, which can easily be misused.
Use automated test tools for SQL injections.
Even if developers follow the rules above and do their best to avoid dynamic queries with unsafe user input, you still need to have a procedure to confirm this compliance. There are automated test tools to check for SQL injections and there is no excuse for not using them to check all the code of your database applications.

One of the easiest tools (and a more or less a reliable one) to test SQL injections is the Firefox extension named SQL Inject ME. After you install the extension, the tool is available in the right-click context menu, as well as from Tools → Options. The sidebar of SQL Inject ME is shown in the next screenshot and as you can see there are many test you can run:

You can choose which tests to run and which values to test. When you press one of the Test buttons, the selected tests will start. When the tests are done, you will see a report of how the tests ended.

There are many options you can set for the SQL Inject ME extension, as shown in the next two screenshots:

As you see, there are many (and above all - simple) steps you can take in order to clean your code from potential SQL injection vulnerabilities. Don't neglect these simple steps because if you do, you will compromise the security not only of your sites but also of all the sites that are hosted with your web hosting provider.

About the author:
The write up has been done by Joanna who works with WebHostingSearch and has been been part of web industry from quite some time now.