Protecting Your Software

Guarding Against SQL Injection

The following defenses against SQL injection attacks are vital to consider when designing your Web application:

  • All input to the application from a user, a component, or another program should be validated. This helps to ensure that the input is free from characters that cause SQL injection attacks. It not only helps to protect against SQL injection attacks but also other attacks, such as XSS attacks and buffer overflows.
  • Use parameterized SQL queries. Applications accessing a database must do so using only parameterized queries. Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application.
  • Use stored procedures. Using stored procedures helps to mitigate the SQL injection threat to a great extent because type checking is available for parameters. If the attacker supplies input that does not match the type constraints, the stored procedures throw an exception. In the vast majority of the cases, this should be properly handled within the application. However, if the stored procedures perform string manipulation in the code and then execute that query using the "exec@sql" construct, incorrect handling of user input can produce the same SQL injection vulnerability as would be seen at the application layer.
    Note: Stored procedures by themselves do not remove SQL injection vulnerabilities. They only raise the bar on the attacker by hiding much of the underlying database schema.
  • Use SQL execute-only permission. This is a defense-in-depth method. This defense assumes the attacker has successfully found a SQL injection bug in your code. Now what? Thankfully, this defense stops almost every attack dead in its tracks. From the SDL, “Only grant ‘execute' permission on all stored procedures, and grant that permission only for the application domain group. Ensure that this group is granted execute permissions only on your stored procedures. Do not grant any other permission on your database to any other user or group.” This is a great defense because if the attacker attempts to access any other database object other than through a stored procedure (you can use views also), the underlying database permissions model prevents the attack by denying the attacker access. In general, database applications should be using a low-privileged account that has the minimum permissions required to execute the statements submitted to SQL Server. One should never use high-privileged accounts like dbo or sysadmin. If high-privileged operations need to be performed, then wrap those operations in a stored procedure and sign that stored procedure with a certificate that has the required high privileges and grant execute permission on the stored procedure.

When implemented, the first SQL injection requirement ensures that an application is secure by design, and thus protected from SQL injection attacks. The other two requirements provide security by default defenses to mitigate other attacks if existing protections were to fail.

There are few additional requirements that one must follow:

  • Use proper escaping for SQL identifiers and Data Definition Language (DDL) statements. There may be times when SQL identifiers (table names, column names, and so on) might be constructed dynamically, or there may be some DDL statements for which one cannot use parameterized SQL statements. In these situations, one should use proper escaping techniques to mitigate SQL injection attacks.
  • Handle buffer lengths properly. If dynamic SQL statements are being constructed for the type of scenarios mentioned earlier, buffer lengths need to be handled properly to prevent SQL injections through truncation. This topic is discussed at length in the MSDN article New SQL Truncation Attacks and How to Avoid Them.

See “Quick Security Reference on SQL Injection” more information and prevention tips.

Managing Risk

Locations

United States Change All Microsoft Sites

Search

Feedback:

Was the information in this article helpful?