Playbook for SQL injection

A SQL injection attack exploits vulnerabilities in input validation to run arbitrary commands in the database. It can occur when your application uses input to construct dynamic SQL statements to access the database. It can also occur if your code uses stored procedures that are passed strings that contain unfiltered user input.

Using the SQL injection attack, the attacker can execute arbitrary commands in the database. The issue is magnified if the application uses an over-privileged account to connect to the database. In this instance it is possible to use the database server to run operating system commands and potentially compromise other servers, in addition to being able to retrieve, manipulate, and destroy data.

Example

Example of SQL Injection

Your application may be susceptible to SQL injection attacks when you incorporate unvalidated user input into database queries. Particularly susceptible is code that constructs dynamic SQL statements with unfiltered user input. Consider the following code:

SqlDataAdapter myCommand = new SqlDataAdapter(
         "SELECT * FROM Users 
          WHERE UserName ='" + txtuid.Text + "'", conn);
  

Attackers can inject SQL by terminating the intended SQL statement with the single quote character followed by a semicolon character to begin a new command, and then executing the command of their choice. Consider the following character string entered into the txtuid field.

'; DROP TABLE Customers–  

This results in the following statement being submitted to the database for execution.

SELECT * FROM Users WHERE UserName=''; DROP TABLE Customers --'
  

This deletes the Customers table, assuming that the application’s login has sufficient permissions in the database (another reason to use a least privileged login in the database). The double dash (–) denotes a SQL comment and is used to comment out any other characters added by the programmer, such as the trailing quote.

Note   The semicolon is not actually required. SQL Server will execute two commands separated by spaces.

Other more subtle tricks can be performed. Supplying this input to the txtuid field:

' OR 1=1–  

builds this command:

SELECT * FROM Users WHERE UserName='' OR 1=1–  

Because 1=1 is always true, the attacker retrieves every row of data from the Users table.

Some more real examples

[1] File upload and sql injection

Mitigation / Countermeasures

• Perform thorough input validation. Your application should validate its input prior to sending a request to the database.
• Use parameterized stored procedures for database access to ensure that input strings are not treated as executable statements. If you cannot use stored procedures, use SQL parameters when you build SQL commands.
• Use least privileged accounts to connect to the database.

References