SQL Injection Prevention Best Practices

SQL Injection is one of the Most Dangerous Attacks faced by Web Applications today . SQL injection is ranked as #1 in OWASP Top 10 . SQL injection prevention best practices are among the guide lines which are to be followed during the life cycle of any web application . We also recommend that the penetration testers also use these SQL Injection Prevention Best practices as the recommendations to the Development Team .

SQL Injection Prevention Best Practices

• Use Stored Procedures : This requires the developer to build SQL statements with the parameters which are automatically parametrized .

The SQL code for the stored procedures is defined and stored within the database itself and then called by the application .

Though there is a risk in using the stored procedure . The risk of using the stored procedure is that these instructions require execution rights (db_owner). This means that if an attacker has breached the database , attacker will have full rights to the database.

• Prepared Statements  : The Prepared Statements aka Parametrized Queries , are simple and easier to write than the dynamic queries . This requires the developer to first define all the queries in the SQL Database and pass each parameter to the query latter .

This coding style allows to distinguish between between the code and data regardless of what user input is supplied . Prepared statements ensure that the attacker is not able to change the intent of the Query even if attacker inserts SQL injection commands in the Query .

An example to make Prepared Statements more clear , if an attacker uses tom’ or ‘1’-‘1 , the parametrized query will not be vulnerable and will look for the string that completely matches entire string tom’ = ‘1’=’1 .

Language Specific Recommendations for Prepared Statements :

1.  Java(EE) : PreparedStatement( ) with bind variable .
2. .NET : Use parametrized Queries like SQLCommand( ) or oleDbCommand( ) with bind variables.
3. PHP : BindParam( ) and PDO with strongly typed parametrized Queries .
4. SQLITE : Use SQLite3_prepare( )

• Escape All User Supplied Input : Escape all user input before inserting into a SQL Query . However this has no guarantee . It is always better to use Parametrized Queries .

• Use OWASP ESAPI

• Least Privilege : This is best technique to minimise the post exploitation damage to an SQL injection attack . In this method we minimise the privileges to any Database account .
  Don not assign admin rights to the DBA or any other application account on the SQL server .

• White Listing  / Input Validation : Input validation can be used to detect malicious input before passed to an SQL query .

• Avoid Constructing SQL Queries with User Input .

• Do not use Dynamic Queries .

• For .NET Applications use ADO Command Objects  .

• Make sure Interpreter separates all untrusted data from command and Query .