SQL injection attack mode and avoidance method

SQL injection is an attack in which malicious code is inserted into a string and then passed to an instance of SQL Server for analysis and execution. Any procedure that makes up an SQL statement should be injected into the vulnerability check, because SQL Server executes all the syntactically valid queries it receives.

The primary form of SQL injection involves inserting code directly into a user input variable that is concatenated with the SQL command and made executable. An indirect attack injects malicious code into a string to be stored in a table or stored as metadata. The malicious code is executed when the stored string is subsequently concatenated into a dynamic SQL command.

The injection process works by terminating the text string prematurely, and then appending a new command. Because the inserted command may append additional strings before execution, the attacker will mark "--" with a comment to terminate the injected string. When executed, the text thereafter is ignored.

The following script shows a simple SQL injection. This script generates an SQL query by concatenating hard-coded strings and user-entered strings:

var Shipcity = Request.Form. ("ShipCity");
var sql = "Select * from orderstable where ShipCity = '" + ShipCity + "'"; the user will be prompted to enter a city name. If the user enters Redmond, the query will consist of a script that resembles the following:

SELECT * from orderstable WHERE ShipCity = ' Redmond '

However, assume that the user enters the following: Redmond '; drop table orderstable--

At this point, the script will comprise the following query: SELECT * from orderstable WHERE ShipCity = ' Redmond ';d rop table orderstable--'

Semicolon (;) Represents the end of a query and the start of another query. A double hyphen (--) indicates that the remainder of the current line is a comment and should be ignored. If the modified code is syntactically correct, the server executes the code. When SQL Server processes the statement, SQL Server first selects all records in Orderstable (where ShipCity is Redmond). Then, SQL Server deletes the orderstable.

as long as the injected SQL code is syntactically correct, it is not possible to programmatically detect tampering. Therefore, you must validate all user input and carefully examine the code that executes the constructed SQL command on the server that you are using. The following sections in this topic describe best practices for writing code.

Verify all Inputs
User input is always validated by test type, length, format, and scope. When implementing the prevention of malicious input, be aware of the application's architecture and deployment scenarios. Note that programs that are designed to run in a secure environment may be copied to an insecure environment. The following recommendations should be considered as best practices:

Do not make any assumptions about the size, type, or content of the data received by the application. For example, you should make the following assessment:

What happens if a user accidentally or maliciously enters a ten MB MPEG file where the ZIP code is needed?

What happens to the application if a DROP TABLE statement is embedded in the text field?

test the size and data type of the input to enforce the appropriate restrictions . This helps to prevent intentional buffer overflows.

Tests the contents of a string variable, accepting only the desired value. Rejects input that contains binary data, escape sequences, and comment characters. This helps prevent script injection and prevents certain buffer overflow attacks.

When using an XML document, all data entered is validated against the schema of the data.

Never use user input directly to generate Transact-SQL statements.

Use stored procedures to validate user input.

In a multi-tier environment, all data should be validated before it is allowed to enter a trusted zone. Data that does not pass the validation process should be rejected and an error is returned one level ahead.

Implement multilayer validation. Precautions taken against rogue malicious users may be ineffective against a determined attacker. A better approach is to validate the input at the user interface and at subsequent points across all cross-trust boundaries.
For example, validating data in a client application can prevent simple script injection. However, if the next layer believes its input has passed validation, any malicious user who can bypass the client can access the system without restrictions.

never concatenate user input that is not validated. string concatenation is the primary input point for script injection.

The following strings are not accepted in fields where the file name may be constructed: AUX, clock$, COM1 to COM8, CON, config$, LPT1 to LPT8, NUL, and PRN.

If possible, reject the input that contains the following characters.

Input character     meaning   in Transact-SQL;
      ;                  Query delimiter.  
       '             Character data string delimiter.  
      --                comment delimiter.  
  /*. */       comment Delimiter. The server does not process the comment between/* and/*. &NBSP
      xp_         for the beginning of the name of the directory extension stored procedure, such as Xp_ Cmdshell. The
uses the type-safe SQL parameters
Parameters collection in SQL Server to provide type checking and length validation. If you use the Parameters collection, the input is treated as a literal value instead of an executable code. Another benefit of using the Parameters collection is the ability to enforce type and length checks. Values outside the range will trigger an exception. The following code snippet shows how to use 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 and not as an executable code. This value will be checked for type and length. If the @au_id value does not conform to the specified type and length constraints, an exception is thrown.

Using parameterized input in stored procedures
Stored procedures may be susceptible to SQL injection attacks if they use unfiltered input. For example, the following code can be vulnerable:

SqlDataAdapter mycommand =
New SqlDataAdapter ("Loginstoredprocedure" +
Login.text + "'", conn); If you use stored procedures, you should use parameters as input to the stored procedure.

Using parameter collections in 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;

Filter input
Filtering input can remove escape characters, which may also help prevent SQL injection. But because of the large number of characters that can cause problems, this is not a reliable method of protection. The following example searches for a string delimiter.

private string Safesqlliteral (String inputsql)
{
Return Inputsql.replace ("'", "" ");
}like clause
Note that if you want to use a LIKE clause, you must also escape the wildcard character:

s = S.replace ("[", "[[]");
s = s.replace ("%", "[%]");
s = S.replace ("_", "[_]"); Checking SQL injection in your code should check all code that calls EXECUTE, EXEC, or sp_executesql. You can use a query similar to the following to help you identify the procedure that contains these statements. This query checks whether there is one, 2, 3, or 4 spaces after the word EXECUTE or EXEC.

SELECT object_name (ID) from syscomments

WHERE UPPER (text) like '%execute (% '

OR UPPER (text) like '%execute (% ')

OR UPPER (text) like '%execute (% ')

OR UPPER (text) like '%execute (% ')

OR UPPER (text) like '%exec (% ')

OR UPPER (text) like '%exec (% ')

OR UPPER (text) like '%exec (% ')

OR UPPER (text) like '%exec (% ')

OR UPPER (text) like '%sp_executesql% '

Use QUOTENAME () and REPLACE () packing parameters
In each stored procedure that you select, verify that all variables used in dynamic Transact-SQL are handled correctly. Data from the input parameters of the stored procedure or data read from the table should be wrapped in QUOTENAME () or REPLACE (). Keep in mind that the @variable value passed to QUOTENAME () has a data type of sysname and a maximum length of 128 characters.

@variable Recommended Packaging
Name of the securable object QUOTENAME (@variable)
string ≤ 128 characters QUOTENAME (@variable, "")
String > 128 characters REPLACE (@variable, "", "")
When using this method, the SET statement can be modified as follows:

--before:

SET @temp = N ' select * from authors where au_lname= ' '

+ @au_lname + N ""

--after:

SET @temp = N ' select * from authors where au_lname= ' '

+ REPLACE (@au_lname, "", "") + N ""

Injection enabled by data truncation
If any dynamic Transact-SQL assigned to a variable is larger than the buffer allocated for the variable, it will be truncated. If an attacker is able to enforce statement truncation by passing a string of unexpected length to a stored procedure, the attacker can manipulate the result.

By passing 154 characters to a 128-character buffer, an attacker can set a new password for the SA without knowing the old password.

EXEC Sp_mysetpassword ' sa ', ' dummy ', ' 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012 "" ‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘

Therefore, you should use a larger buffer for the command variable, or EXECUTE dynamic Transact-SQL directly within the EXECUTE statement.

Truncation when using QUOTENAME (@variable, "") and REPLACE ()
If the string returned by QUOTENAME () and REPLACE () exceeds the allocated space, the string is automatically truncated. The stored procedure created in the following example shows what might happen.

When you concatenate values of type sysname, you should use a temporary variable that is large enough to hold a maximum of 128 characters per value. You should call QUOTENAME () directly within dynamic Transact-SQL whenever possible.

SQL2008 Related articles:

Injected attacks on SQL

Creation of a SQL Server 2008 logon name

SQL2008 Data Maintenance and management

SQL injection attack mode and avoidance method