Defend against SQL Injection

SQL injection is an attack by which user input is used to modify the logic of a SQL statement. The attack may be used to disrupt the underlying database or bypass application logic but more typically it is used to extract additional information from the database which may be valuable in its own right or which may facilitate additional attacks.

SQL injection depends upon user input, obtained from textboxes, HTTP query strings, cookies, data residing in a database table, or some other source, which is then used to build a SQL statement which is then submitted to the database engine for execution.  The dynamic assembly of the statement may occur within any one of the layers of the application including the database itself.

DEMONSTRATION To see a highly simplistic example of SQL injection, please see this post.

The key to preventing a SQL injection attack is to avoid the dynamic assembly of SQL statements from within an application. While highly flexible and expedient, an application is only vulnerable to SQL injection when this design pattern is employed.

Instead of dynamic SQL, consider the use of parameterized SQL statements with user-input supplying the values of the parameters. Technologies such as LINQ actively promote this pattern while providing the developer some of the flexibility of dynamic SQL. 

That said, these technologies are not invulnerable to SQL injection.  Dynamic SQL statements can be submitted through LINQ using the ExecuteQuery() method and Entity-Framework employs a much more dynamic approach to statement assembly which has been shown to be vulnerable.  And with both technologies, stored procedures may be called which themselves employ dynamic SQL (as demonstrated in the demonstration associated with this post).

If dynamic SQL must be used, validate user-input to ensure it meets intended patterns and known elements of an injection attack are not present.  Justin Clarke’s SQL Injection Attacks and Defense provides very comprehensive guidance on input validation to block a SQL injection attack.

Next, apply the absolute minimal permissions to any code executing dynamic SQL. This can be done through a variety of techniques discussed in this post.

In addition to validating inputs, validate statement outputs.  If a certain number of result sets, columns, or rows are anticipated, anticipate these precise results and confirm them before displaying any data.

Finally, return and display the absolute minimal data required by the application.  This includes system metadata and exceptions but goes back to the application logicitself.  For example, if a query is intended to validate an entry in a table, the entry itself does not need to be returned. Instead, a value of 1 or 0 could be returned to confirm its existence.

Skip to main content