Best practises for preventing SQL injection?

Question

SQL injection is always a hot topic particularly when it comes to web security. In this regard I am interested in what are the steps that should always be taken to prevent SQL injection within any web application? Also in addition to these normal steps is there anything else people do beyond the normal to prevent it?

Answer 1

Already very good answers on this question, however I would like to mention some more:

• Secure coding (which is already been mentioned by many already)
  • Escaping user input
  • Parameterized queries (prepared statements, predefined queries where you only bind variables)
  • Code defensively
• Monitoring for attacks
  • Network Intrusion Detection System (NIDS)
  • Host Intrusion Detection System (HIDS)
  • Application Intrusion Detection System (AppIDS)
• Block attacks
  • Application firewall
  • Database firewall
  • Web application firewall
    • Apache ModSecurity
    • Cisco Application Velocity System (AVS)
• Probe for vulnerabilities
  • Automated blackbox injection testing
  • Static source code analysis
  • Manual penetration testing

This is only for prevention and I have not taken sql server hardening into account. There is however many similarities.

Additional defenses in case you are already vulnerable to injection would be:

• Running your application with the least amount of grants necessary
  • Specifically grant only access to the database and tables you need
  • Be sure to only grant the privilege it needs (usually select, insert, update)

Answer 2

Prepared statements, parameterized queries, escaping all user input, for a good starter see http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet.

Answer 3

The key defence is to only use API's that securely escape database queries - these are generally referred to as parameterised or prepared statements. These can't be used in all cases (for example where a SQL identifier such as the table or column name is supplied at runtime), but is the best approach where possible (the majority of cases).

Note - this can lead to harmful data being put into the database, so be aware of that when using this data elsewhere in the application

The second defence is to take an escaping approach. This is the "replace a single quote with two quotes" approach. If you need to go this way, you must escape every potentially harmful character, which means more than single quotes in some cases. I'd advise using a higher level library such as OWASP ESAPI if possible to do this for you, or read the OWASP SQL Injection Cheat Sheet (referenced above) closely.

Answer 4

• Here you can find some SQL Injection Resources
• Here are some SQL injection attacks by example
• And here are some basics how to protect your application against SQL injection

The main step to protect web application against SQL injection is to properly sanitize any user input (especially input used in SQL queries). In some languages/frameworks, there are standard ways how to deal with these values - for example using parameterized queries instead of composing the query by joining string values.

Answer 5

From the side of web developer attention should be pointed to what Weber already has mentioned.

Additionally, database firewall could be set up, like GreenSQL: http://www.greensql.net/. It works like a proxy between you application and user input, watches what should be passed through and etc. However, this comes in cost of increased response time.

Answer 6

I am teaching IT security in polytechnics. Often for students they have some confusion over the terms used for SQL injection so let me try to clarify.

In the context of a web application like Facebook,

SQL injection is when the normal web user enters SQL code into data fields Eg

 ' OR '1'='1 into the textboxes of a login form.

The best person to prevent SQL injection is the web developer aka the person in charge of writing code for the web application to read/write data to the database.

The easiest way to prevent SQL injection by the web developer is to use parameterized queries.

Prepared statements and parameterized queries mean the same thing.

I shall use parameterized queries from this point onwards.

Parameterized queries refer to the way SQL code are first defined and then the data is placed inside the appropriate parameters.

Eg in .Net

Update `users` set `name` = @name where `id` = @id

the parameters @name and @id are the data. The rest are the SQL code.

Bear in mind the parameterized queries are usually but not always done at the web application code.

There are 2 common ways to write out parameterized queries.

1. In the web application code depending on the language used
2. In the database using stored procedures

So in some sense, yes, stored procedures are a form of parameterized queries.

There are other ways to prevent SQL injection that is by escaping special characters like single quotes. For eg, in PHP, you can use mysql_real_escape_string which basically just puts slashes before the single quotes.

This is not ideal because of issues with % and underscore for LIKE operator in certain database like MySQL. see this pdf for more details.

Long story short, all options suggested have to do with sanitizing (clean up) user input to clean away the SQL code.

Best way is to use parameterized queries depending on the programming language used.

End of story.

Answer 7

Use decent data-access APIs which make it easy to do the right thing, safely.

Here's ActiveRecord v3:

# basic usage
@user = User.where(:username => 'joe@example.com').first

# with a SQL-string fragment, but using parameters
@projects = @user.projects.where('status = ?', params[:status])