There are many detailed articles available on web on SQL Injection topic and hence I’ll just try to keep it short and simple in this post.
SQL Injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. This can allow an attacker to steal the data as well as modify and delete it. Conventional security measures like use of SSL and IPSec, do not protect the application from SQL injection attacks.
The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.
For example, if we have following code in our application
string sql = "select * from Sales.SalesOrderHeader OH inner join " +
" Person.Address A on OH.ShipToAddressID = A.AddressID " +
" where City = '" + txtCity.Text + "'";
Here we are just expecting a name of the city unaware that an attacker may enter following,
Paris'; drop table Sales.SalesOrderHeader--
In this case the following query will be executed.
select * from Sales.SalesOrderHeader OH
inner join Person.Address A
on OH.ShipToAddressID = A.AddressID
where City = 'Paris';drop table Sales.SalesOrderHeader--'
As you can see, as per the attacker’s input, the semicolon starts a new statement to execute and conveniently ignores the single quote which is concatenated in the code. SQL Server will run this code and drop the table.
Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.
To counter SQL injection attacks, you need to:
- Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
- Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
- Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
- Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.
This MSDN white-paper explains these measures further in detail.