Handling SQL Injection

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. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.
 
The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:
 
CS
 
string vUserId = null;
vUserId = Request.Form ("UserId");
string vSql = null;
vSql = "select * from Users where UserId = ‘" + vUserId + "’";
 
VB
 
Dim vUserId As String = Nothing
vUserId = Request.Form("UserId")
Dim vSql As String = Nothing
vSql = "select * from Users where UserId = ‘" & vUserId & "’"
 
The user is prompted to enter the name of a ‘User Id’. If the user enters "John", the query assembled by the script looks similar to the following:
 
SELECT * FROM Users WHERE UserId = ‘John’
 
However, assume that the user enters the following:
 
John’; drop table Users–
 
In this case, the following query is assembled by the script:
 
SELECT * FROM Users WHERE UserId = ‘John’; drop table Users–‘
 
The semicolon (;) denotes the end of one query and the start of another. The double hyphen (–) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in Users where UserId is John. Then, SQL Server will drop Users.
 
As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. Therefore, you must validate all user input and carefully review code that executes constructed SQL commands in the server that you are using.
 
Here goes my tips to handle SQL Server. You should always reject input that contains the following characters programmatically.
 
1. ; (Query delimiter.)
2. ‘- (Character data string delimiter.)
3. — (Comment delimiter.)
4. /* … */ (Comment delimiters. Text between /* and */ is not evaluated by the server.)
5. xp_ (Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: