How To: Thwart SQL Injection Attack

Hi,

 

For more info, please see: http://msdn2.microsoft.com/en-us/library/ms161953.aspx

 

What is SQL Injection Attack?

SQL Injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server for parsing and execution.

The primary form of SQL injection consists of direct insertion of code into user input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

Example of SQL Injection

Consider following piece of code

 

// Accept Employee Id in a text box

string Name = textBoxID.Text;

// Concatenate ID with a SELECT query

string query = "SELECT * FROM Employee WHERE Name = '" + Name + "';"

// Code to connect to a data source goes here

SqlCommand cmd = new SqlCommand(query, connection);

SqlDataReader reader = cmd.ExecuteReader();

reader.Close();

 

A potential attacker could enter a value of  “John’;DELETE FROM Employee;--“ in the text box. Due to this:-

 

1. John becomes the value for WHERE clause.

2. Single quote after John completes WHERE clause.

3. Semi colon after that completes the SELECT command

4. DELETE FROM Employee; is a new command representing SQL Injection Attack

5. Double-hyphens indicate that whatever follows must be treated as a comment thereby supressing the single quote-semi colon combination concatenated in the original code.

 

Hence the server executes following statements:

 

 SELECT * FROM Employee WHERE Name=’John’;DELETE FROM Employee;--‘;

 

 

Ways to Prevent SQL Injection

 

1.       Validate Input Data: Check the data for type, lenth, format and range.

 

2.       Run under least-privileged Account: Ideally, stored procedures should be written and granted the execute permission. No direct table access should be provided.

 

3.       Avoid Disclosing Sensitive Database Info through Errors: Attackers often use information from an exception, such as the name of server, database, or table to mount an attack on your system.

 

4.       Use type-safe SQL Parameters: Use parameterized stored procedures and queries. ParameterCollections such as SQLParameterCollection provide type checking and length validation. They throw an exception if the data is not of proper type or length, saving the trip to the server. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. Following is the modified version of code snippet given above.

 

// Accept Employee Id in a text box

string Name = textBoxID.Text;

 

string queryString = "SELECT * FROM Employee WHERE Name = @name";

 

SqlCommand cmd = new SqlCommand(queryString, conn);

cmd.Parameters.Add("@name", SqlDbType.VarChar, 10).Value = Name;

 

// Code to connect to a data source goes here

SqlDataReader reader = cmd.ExecuteReader();

reader.Close();

 

Thanks & Regards,

Arun Manglick

SMTS || Microsoft Technology Practice || Bridgestone - Tyre Link || Persistent Systems || 3023-6258

 

DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Pvt. Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Pvt. Ltd. does not accept any liability for virus infected mails.