SQL Server's SQL injection Chapter

SQL Injection

About the harm of SQL injection here is not much to do introduction, I believe we also know the strong relationship among them. Here are some SQL injection events that everyone interested can look at

There are several ways to prevent SQL injection:

1. Using type-safe SQL parameters
2. Using parameterized input stored procedures
3. Using parameter collections with dynamic SQL
4. Input filtering
5. Special characters that filter like clauses

... If there are omissions also welcome the garden of the big advice.

Sample:

var= Request.Form ("ShipCity"); var " SELECT * from orderstable where ShipCity = ' " " ' ";

Above is a simple example of SQL injection

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 WHERE = ' Redmond '

However, assume that the user enters the following:

Redmond '; drop table orderstable--

At this point, the script will make up the following query:

1 SELECT *  from WHERE = ' Redmond '; Drop 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.

here are some common ways to prevent SQL injection: 1. Verify all InputsUser 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
;	The query delimiter.
‘	Character data string delimiter.
--	The comment delimiter.
/* ... */	The comment delimiter. The server does not process the comment between/* and/*.
Xp_	The beginning of the name used for the directory extension stored procedure, such as xp_cmdshell.

Note: Validation input is the most commonly used and associative, but the personal feeling of this way not only the code looks fat, and the efficiency is not very good

2. Using type-safe SQL parameters

The Parameters collection in SQL Server provides 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:

 1  SqlDataAdapter mycommand = new  SqlDataAdapter (  "  2  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;  3  SqlParameter parm = myCommand.SelectCommand.Parameters.Add ( "  @au_id  "  ,  4  SqlDbType.VarChar, ); 5  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.

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.

Note: In my current project, this method is the most widely used

3. Using parameter collections in Dynamic SQL

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

 1  SqlDataAdapter mycommand = new  Span style= "color: #000000;" > SqlDataAdapter ( 2   " select au_lname, au_fname from Authors WHERE au_id = @au_id   " , conn);  3  SqlParameter parm = myCommand.SelectCommand.Parameters.Add ( "  @au_id  "  ,  4  SqlDbType.VarChar, 11  Span style= "color: #000000;" >); 5  parm.value = login.text; 

Note: And the second similarity, this method is to supplement the existence of Method 2, because often in many cases the business is simple do not need to use proc, you can use this method

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

1 Private string Safesqlliteral (string  inputsql)2{3   return Inputsql.replace ("'""" "); 4 }

Note: Filtering input has a similar approach 1

5.LIKE clause

Note that if you want to use a LIKE clause, you must also escape the wildcard character:

 1  2  s = s.replace ( " [ " ,  [[]  "   ); 3  s = s.replace ( %  , "  [%]   "  4  s = s.replace ( _  , "  [_]  ); 

Note: For the LIKE clause, in the use of efficiency here is not much to say, in short, to use with caution.

All of the above methods and their comments highlighted section, are I humble opinion, if the method has a supplement or a different opinion on the highlighted part, we welcome the comments, common progress.

This article is in the context of MSDN to add some of their own understanding and comments, reproduced please inject source. Thx.

SQL Server's SQL injection Chapter