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 :
- Java(EE) : PreparedStatement( ) with bind variable .
- .NET : Use parametrized Queries like SQLCommand( ) or oleDbCommand( ) with bind variables.
- PHP : BindParam( ) and PDO with strongly typed parametrized Queries .
- 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 .