Sql Injection in Sql Server

Sql Injection in Sql Server


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.


The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed.

The injection process works by prematurely terminating a text string and appending a new command. 

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 OrdersTablewhere 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.







How to Overcome this?

Always validate user input by testing type, length, format, and range. When you are implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. 

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.




Never build Transact-SQL statements directly from user input.
Use stored procedures to validate user input.



            Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

When you can, reject input that contains the following characters.




Input character
Meaning in Transact-SQL

;

Query delimiter.


'

Character data string delimiter.


--

Comment delimiter.


/* ... */

Comment delimiters. Text between /* and */ is not evaluated by the server.


xp_

Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Use Type-Safe SQL Parameters
The Parameters collection in SQL Server provides type checking and length validation. 

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;

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:











SqlDataAdapter myCommand = 
new SqlDataAdapter("LoginStoredProcedure '" + 
                               Login.Text + "'", conn);





If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown in the following code example:











SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id", 
                        SqlDbType.VarChar, 11);
Parm.Value = Login.Text;






Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.











private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}






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("_", "[_]");





http://msdn.microsoft.com/en-us/library/ms161953(v=sql.100).aspx