SQL Injections

SQL Injections
3 votes, 5.00 avg. rating (97% score)


This post covers various ways to handle sql injections 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 There are quite couple of ways and is advisable to implement checks to prevent application from being attacked by SQL injection. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives.

Skill Level – Advanced

SQL Injections

Following is one example how SQL injection will occur.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.

Best practices for SQL Injections

Validate Input

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. Remember that programs designed to run in a secure environment can be copied to an nonsecure environment. The following suggestions should be considered best practices:

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

>In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected and an error should be returned to the previous tier.

>Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against determined attackers. A better practice is to validate input in the user interface and at all subsequent points where it crosses a trust boundary.

For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.

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

>Do not accept the following strings in fields from which file names can be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

Reject input that contains the following characters.
; 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. 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 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("_", "[_]");


It is bad practice to recommend searching for actions like “SELECT”, “INSERT”, “UPDATE” etc.It is good practice to implement a function in your application which filters the data on application-entry, i.e. as soon as a user sends data, the first thing that your application should do is to filter out unwanted characters and data. When this is done you may safely use this data to build SQL-queries. Follow the above best practices and the outcome will be good and safe application.

January 16, 2014 В· Adi В· No Comments
Posted in: Best Practices, Security, SQL

Leave a Reply

What is 2 + 8 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)