Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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?

share|improve this question

7 Answers 7

up vote 18 down vote accepted

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)
share|improve this answer
5  
+1, very good answer. One comment, though, wrt your last paragraph - you're better off not granting any access directly on the tables, instead using stored procedures and granting access only on them. –  AviD Dec 25 '10 at 20:15

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])
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
Cool - never heard of greenSQL before. –  symcbean Dec 30 '10 at 10:39

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

share|improve this answer
3  
excellent link. Any chance of popping the core points in the body of your answer - it makes it easier for someone to find content, and also makes search engine indexing of this Q&A better. –  Rory Alsop Dec 21 '10 at 10:13
1  
I agree with @Rory - can you put some more info in the answer? Especially some reference to stored procs - noone else has mentioned this, I see... –  AviD Dec 25 '10 at 20:02
1  
@AviD: Stored procedures are a specific type of prepared statement –  symcbean Dec 30 '10 at 10:36
    
@symcbean - I guess semantically thats true, but typically "prepared statement" refers to in the application, as opposed to in the database. –  AviD Dec 30 '10 at 10:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.