SQL Injection

           

What is 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 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 that are 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.

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.

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:

 

var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks similar to the following:

 

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'

 

However, assume that the user enters the following:

Redmond'; drop table OrdersTable--

In this case, the following query is assembled by the script:

 

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'

 

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 OrdersTable where ShipCity is Redmond. Then, SQL Server will drop OrdersTable.

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. Coding best practices are described in the following sections in this topic.

 

Best practices for prevention of sql injection?

Use validation for input values: -

 

 You can check for User input inside the textboxes and validate them according to the expected value so no other value will be inserted into the database. In below example database expects only Numeric value so we have use a RegularExpressionValidator which will allow only numeric value to be entered inside the textBox

  <asp:TextBox ID="txtid" runat="server"></asp:TextBox></span> <asp:RegularExpressionValidator ID="regExp" runat="server" ErrorMessage="*" ValidationExpression="^(-)?\d+(\.\d\d)?$" ControlToValidate="txtid"></asp:RegularExpressionValidator>

Make no assumptions about the size, type, or content of the data that is received by your application. For example, you should make the following evaluation: 

How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code?

How will your application behave if a DROP TABLE statement is embedded in a text field?

Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.

Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.

When you are working with XML documents, validate all data against its schema as it is entered.

Never build Transact-SQL statements directly from user input.

Use stored procedures to validate user input.      

  

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value instead of as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside the range will trigger an exception. The following code fragment shows using the Parameters collection:

 

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
     SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @au_id parameter is treated as a literal value instead of as executable code. This value is checked for type and length. If the value of @au_id does not comply with the specified type and length constraints, an exception will be thrown.

       

• Use of Stored Procedure: Another way of preventing SQL Injection is using Stored Procedure, where you pass the required parameters with values to the stored procedure which is defined for the stored procedure. As to stored procedure you pass value stored procedure takes it as Parameter so there is no risk of Database attack.

SqlConnection conn = new SqlConnection(connectionString)        
DataSet ds = new DataSet();        
SqlDataAdapter da= new SqlDataAdapter("p_get_user_details", conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11); da.SelectCommand.Parameters["@au_id"].Value = txtUsername.Text; da.Fill(ds);

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still must be escaped:

 

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");