SQL Server Best Practices – Series 4
1.5 Preventing SQL Injection Attacks
1.5.1 Explanation of the problem
Here is an excerpt from Microsoft documentation that neatly explains this problem:
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.
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 (written in C# but applicable to other technologies too):
SqlDataAdapter myCommand = new SqlDataAdapter(“SELECT * FROM Employee WHERE FirstName = ‘” + 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 Employee —
This results in the following statement being submitted to the database for execution:
SELECT * FROM Employee WHERE FirstName=”; DROP TABLE Employee –‘
This deletes the Employee 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 that 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 Employee WHERE FirstName=” OR 1=1 —
Because 1=1 is always true, the attacker retrieves every row of data from the Employee table, thereby viewing data that the attacker is not entitled to view.
Countermeasures to prevent SQL injection include:
- 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.
1.5.2 Input Validation
If you are building a dynamic SQL string to execute, then you must ensure that any variables or parameters included in the string do not contain malicious content like the examples given above. The best
way to do this is to force the variables into the exact format you want like so:
1.5.2.1 Strings
‘Where FirstName = ”’ + Replace(@FirstName, ””, ”””) + ””
This code ensures that any single quotes present in the string do not terminate the string. If the attacker cannot terminate the string then he/she cannot inject additional statements.
1.5.2.2 Integers
‘Where ID = ‘ + Convert(Varchar(10), convert(integer, @ID))
This code is only necessary if the variable or parameter is a string type (if it is already an integer then it is inherently safe). It forces the string to be converted to an integer then back again. This will remove any injected statements and instead raise a runtime “type mismatch” error.
1.5.2.3 Dates and Times
‘Where effective_date =convert(datetime, ”’ + convert(varchar(24), convert(datetime, @effective_date, 120), 120) + ”’, 120)’
This code is only necessary if the variable or parameter is a string type (if it is already a datetime then it is inherently safe). It forces the string to be converted to a datetime then back again. This will remove any injected statements and instead raise a runtime “type mismatch” error. Note that the 120 argument is required on all CONVERT() functions to ensure that the datetime is always interpreted correctly regardless of the SQL Server locale setting.
Regards
Bhagwan Singh Jatav
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
