SQL Injection attack

               A SQL Injection attack is a form of attack that comes from user input that has not been checked to see that it is valid. The objective is to fool the database system into running malicious code that will reveal sensitive information or otherwise compromise the server. 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.

            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.

·         Make no assumptions about the size, type, or content of the data that is received by 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. 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.

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

Wrapping Parameters with QUOTENAME () and REPLACE ()

Data that comes from the input parameters of the stored procedure or that is read from a table should be wrapped in QUOTENAME () or REPLACE(). Remember that the value of @variable that is passed to QUOTENAME () is of sysname, and has a maximum length of 128 characters.

SET @command= 'update Users set password=' + QUOTENAME(@new, '''')