How to Prevent a SQL Injection Attack

A SQL injection is a common programming error the consequences of which can be really devastating. Many successful hacking attacks start when a hacker discovers a vulnerability that gives an opportunity to inject SQL code.

When an SQL injection occurs, the structure of an SQL query is compromised and as a result you are left at the mercy of the potential hackers. If there is a vulnerability found, hackers can exploit it to gain access not only to your site and database but in extreme cases also to your corporate network. When hackers can inject their code in your code, they can do what they want.

Why do SQL injections happen so often?

The shortest answer is that SQL injections are so popular because of poor programming. Hackers know about the potential of a successful SQL injection attack and they search for vulnerabilities. Unfortunately, very often they don’t have to search hard – vulnerabilities pop right in their face. On the other hand, the risk for a hacker from executing an SQL injection is minor, while the potential rewards are lucrative. What else could a criminal ask for?
Therefore, don’t rely that hackers won’t bother with your site and don’t leave the door widely open. Nobody says that each vulnerability of this type will turn into an attack but it’s not wise to take any chances. The good news is that fortunately, SQL injections are also relatively easy to prevent.

Steps to prevent a SQL injection attack

Basically, there are two fire-proof ways to make an SQL injection impossible:

1. Don’t use dynamic database queries.
2. Don’t accept user input in queries.

However, obviously these two steps can’t be done because if you follow them, then the sites you can create will be very static and in today’s Web this is not a solution. What you can do, is minimize the risks when using dynamic queries and user input. Here are some basic principles that apply to any programming language:

1. Patch your SQL server regularly

Before we get into the coding part of the advice how to prevent an SQL injection, we need to start with the fundamental issues. SQL injections might be a frequent programming error but they aren’t the only way for a hacker to break into. If your underlying software – i.e. the database and the operating system have vulnerabilities, then your efforts to secure your code become obsolete. This is why you should always patch your system, especially your SQL server.

2. Limit the use of dynamic queries

As I already mentioned, dynamic queries are the door to SQL injections. Of course, it might not be realistic to expect that all dynamic queries can be trashed right away but some of the ways out are stored procedures, parameterized queries, and above all – prepared statements. The exact approaches vary from one programming language to the other but basically any programming language offers good substitutes of dynamic queries.
For instance, the PreparedStatement() with bind variables in Java, or the SqlCommand() and OleDbCommand() with bind variables in PHP, or strongly typed parameterized queries with bindParam() in PHP are all possibilities to explore.

3. Escape user input

The second biggest evil for SQL injections is user input. While you can’t always avoid user input completely, the next best thing is to escape it. Escaping user input doesn’t do as good job as limiting dynamic queries but still it can stop many SQL injection attacks. For instance, if you are using PHP, for GET and POST, use htmlspecialchars() to escape XSS characters and addslashes(), in case you using database. Alternatively, you can escape user input from inside your database but since the exact code varies from one database to the next, you should check with the docs of your database for the exact syntax to use.

4. Store database credentials in a separate file

In order to minimize the damage in case of an SQL injection attack, always store database credentials in a separate file. This way even if a hacker manages to break in, he or she won’t benefit much.

5. Use the principle of least privilege

The principle of least privilege is a security cornerstone and it applies to SQL injections as well. For instance, when you grant a user access only to the tables he or she needs rather to the whole database; this drastically reduces the damage potential.

6. Turn magic quotes off

Turning the magic_quotes_gpc variable off can also stop some SQL injection attacks. Unfortunately, this isn’t always a reliable measure because sometimes magic quotes might be off and you are unaware of this but still it is better than nothing. In any case, you need to have code to substitute quotes with slashes. Here is the simplest way to do it:
if (!get_magic_quotes_gpc()) {
$username = addslashes($username);
$password = addslashes($password);
}

7. Disable shells

Many databases offer shell access which essentially is what an attacker needs. This is why you need to close this door. Consult your DB’s documentation about how to disable shell access for your particular database.

8. Disable any other DB functionality you don’t need

In addition to the shells there are many other functions in a database you don’t need. While not all of them are a security risk, the general rule here is that less is more. Just remove or at least disable any functionality you can do without.

9. Test your code

Finally, the last step to ensure your code is SQL injections-proofed is to test it. There are automated tools you can use to do this and one of the most universal is the SQL Inject Me Firefox extension. This tool has many options and many tests the best is if you have the time to run all of them.

All these steps to prevent an SQL injection are relatively easy to implement but failing to do so could make a huge difference. If you stick to these rules, you will drastically reduce the risk of your site being compromised via a SQL injection. Still, you can never be 100 per cent sure that you are completely protected against such an attack (or any other type of attack, to be more precise) and this is why you need to keep an eye on your logs so if a breach occurs, you will know it right away and react appropriately to minimize the damage.

Reference: http://www.developerdrive.com/2011/10/how-to-prevent-a-sql-injection-attack/