SQL 注入SQL Injection

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL 注入是一种攻击方式,在这种攻击方式中,恶意代码被插入到字符串中,然后将该字符串传递到 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. 构成 SQL 语句的任何过程都应进行注入漏洞审阅,因为 SQL Server 将执行其接收到的所有语法有效的查询。Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. 一个有经验的、坚定的攻击者甚至可以操作参数化数据。Even parameterized data can be manipulated by a skilled and determined attacker.

SQL 注入工作原理How SQL Injection Works

SQL 注入的主要形式包括直接将代码插入到与 SQL 命令串联在一起并使其得以执行的用户输入变量。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. 在存储的字符串随后串连到一个动态 SQL 命令中时,将执行该恶意代码。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. 由于插入的命令可能在执行前追加其他字符串,因此攻击者将用注释标记“--”来终止注入的字符串。Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". 执行时,此后的文本将被忽略。Subsequent text is ignored at execution time.

以下脚本显示了一个简单的 SQL 注入。The following script shows a simple SQL injection. 此脚本通过串联硬编码字符串和用户输入的字符串而生成一个 SQL 查询: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. 如果用户输入 Redmond,则查询将由与下面内容相似的脚本组成: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. SQL ServerSQL Server 处理该语句时, SQL ServerSQL Server 将首先选择 OrdersTable 中的所有记录(其中 ShipCityRedmond)。When SQL ServerSQL Server processes this statement, SQL ServerSQL Server will first select all records in OrdersTable where ShipCity is Redmond. 然后, SQL ServerSQL Server 将删除 OrdersTableThen, SQL ServerSQL Server will drop OrdersTable.

只要注入的 SQL 代码语法正确,便无法采用编程方式来检测篡改。As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. 因此,必须验证所有用户输入,并仔细检查在您所用的服务器中执行构造 SQL 命令的代码。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.

验证所有输入Validate All 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:

    • 如果一个用户在需要邮政编码的位置无意中或恶意地输入了一个 10 MB 的 MPEG 文件,应用程序会做出什么反应?How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code?

    • 如果在文本字段中嵌入了一个 DROP TABLE 语句,应用程序会做出什么反应?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.

  • 使用 XML 文档时,根据数据的架构对输入的所有数据进行验证。When you are working with XML documents, validate all data against its schema as it is entered.

  • 绝不直接使用用户输入内容来生成 Transact-SQLTransact-SQL 语句。Never build Transact-SQLTransact-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.

  • 不接受以下来自可构造文件名的字段中的字符串:AUX、CLOCK$、COM1 到 COM8、CON、CONFIG$、LPT1 到 LPT8、NUL 和 PRN。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.

如果可能,拒绝包含以下字符的输入。When you can, reject input that contains the following characters.

输入字符Input character 在 Transact-SQL 中的含义Meaning in Transact-SQL
;; 查询分隔符。Query delimiter.
”启用' 字符数据字符串分隔符。Character data string delimiter.
-- 字符数据字符串分隔符。Character data string delimiter.
实例时都提供 SQL Server 登录名。.
/* ... *//* ... */ 注释分隔符。Comment delimiters. 服务器不计位于 /**/ 之间的文本。Text between /* and */ is not evaluated by the server.
xp_xp_ 用于目录扩展存储过程的名称的开头,如 xp_cmdshellUsed at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

使用类型安全的 SQL 参数Use Type-Safe SQL Parameters

中的 Parameters SQL ServerSQL Server 集合提供了类型检查和长度验证。The Parameters collection in SQL ServerSQL Server provides type checking and length validation. 如果使用 Parameters 集合,则输入将被视为文字值而不是可执行代码。If you use the Parameters collection, input is treated as a literal value instead of as executable code. 使用 Parameters 集合的另一个好处是可以强制执行类型和长度检查。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. 以下代码段显示了如何使用 Parameters 集合: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;  

在此示例中, @au_id 参数被视为文字值而不是可执行代码。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. 如果 @au_id 值不符合指定的类型和长度约束,则将引发异常。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

存储过程如果使用未筛选的输入,则可能容易受 SQL Injection 攻击。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.

在动态 SQL 中使用参数集合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

筛选输入可以删除转义符,这也可能有助于防止 SQL 注入。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 子句LIKE Clauses

请注意,如果要使用 LIKE 子句,还必须对通配符字符进行转义:Note that if you are using a LIKE clause, wildcard characters still must be escaped:

s = s.Replace("[", "[[]");  
s = s.Replace("%", "[%]");  
s = s.Replace("_", "[_]");  

在代码中检查 SQL 注入Reviewing Code for SQL Injection

应审阅调用 EXECUTEEXECsp_executesql的所有代码。You should review all code that calls EXECUTE, EXEC, or sp_executesql. 可以使用类似如下的查询来帮助您标识包含这些语句的过程。You can use queries similar to the following to help you identify procedures that contain these statements. 此查询检查单词 EXECUTEEXEC后是否存在 1 个、2 个、3 个或 4 个空格。This query checks for 1, 2, 3, or 4 spaces after the words 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%';  

使用 QUOTENAME() 和 REPLACE() 包装参数Wrapping Parameters with QUOTENAME() and REPLACE()

在选择的每个存储过程中,验证是否对动态 Transact-SQL 中使用的所有变量都进行了正确处理。In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly. 来自存储过程的输入参数的数据或从表中读取的数据应包装在 QUOTENAME() 或 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(). 请记住,传递给 QUOTENAME() 的 @variable 值的数据类型为 sysname,且最大长度为 128 个字符。Remember that the value of @variable that is passed to QUOTENAME() is of sysname, and has a maximum length of 128 characters.

@variable 建议的包装Recommended wrapper
安全对象的名称Name of a securable QUOTENAME(@variable)
字符串 ≤ 128 个字符String of ≤128 characters QUOTENAME(@variable, '''')
字符串 > 128 个字符String of > 128 characters REPLACE(@variable,'''', '''''')

使用此方法时,可对 SET 语句进行如下修改:When you use this technique, a SET statement can be revised 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

如果分配给变量的任何动态 Transact-SQLTransact-SQL 比为该变量分配的缓冲区大,那么它将被截断。Any dynamic Transact-SQLTransact-SQL that is assigned to a variable will be truncated if it is larger than the buffer allocated for that variable. 如果攻击者能够通过将意外长度的字符串传递给存储过程来强制执行语句截断,则该攻击者可以操作该结果。An attacker who is able to force statement truncation by passing unexpectedly long strings to a stored procedure can manipulate the result. 例如,以下脚本创建的存储过程容易受到由截断启用的注入攻击。For example, the stored procedure that is created by the following script is vulnerable to injection enabled by truncation.

CREATE PROCEDURE sp_MySetPassword  
@loginname sysname,  
@old sysname,  
@new sysname  
AS  
-- Declare variable.  
-- Note that the buffer here is only 200 characters long.   
DECLARE @command varchar(200)  
-- Construct the dynamic Transact-SQL.  
-- In the following statement, we need a total of 154 characters   
-- to set the password of 'sa'.   
-- 26 for UPDATE statement, 16 for WHERE clause, 4 for 'sa', and 2 for  
-- quotation marks surrounded by QUOTENAME(@loginname):  
-- 200 - 26 - 16 - 4 - 2 = 154.  
-- But because @new is declared as a sysname, this variable can only hold  
-- 128 characters.   
-- We can overcome this by passing some single quotation marks in @new.  
SET @command= 'update Users set password='   
    + QUOTENAME(@new, '''') + ' where username='   
    + QUOTENAME(@loginname, '''') + ' AND password = '   
    + QUOTENAME(@old, '''')  
  
-- Execute the command.  
EXEC (@command)  
GO  

通过向 128 个字符的缓冲区传递 154 个字符,攻击者便可以在不知道旧密码的情况下为 sa 设置新密码。By passing 154 characters into a 128 character buffer, an attacker can set a new password for sa without knowing the old password.

EXEC sp_MySetPassword 'sa', 'dummy',   
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012'''''''''''''''''''''''''''''''''''''''''''''''''''   

因此,应对命令变量使用较大的缓冲区,或直接在 Transact-SQLTransact-SQL 语句内执行动态 EXECUTEFor this reason, you should use a large buffer for a command variable or directly execute the dynamic Transact-SQLTransact-SQL inside the EXECUTE statement.

使用 QUOTENAME(@variable, '''') 和 REPLACE() 时的截断Truncation When QUOTENAME(@variable, '''') and REPLACE() Are Used

如果 QUOTENAME() 和 REPLACE() 返回的字符串超过了分配的空间,该字符串将被自动截断。Strings that are returned by QUOTENAME() and REPLACE() will be silently truncated if they exceed the space that is allocated. 以下示例中创建的存储过程显示了可能出现的情况。The stored procedure that is created in the following example shows what can happen.

CREATE PROCEDURE sp_MySetPassword  
    @loginname sysname,  
    @old sysname,  
    @new sysname  
AS  
  
-- Declare variables.  
    DECLARE @login sysname  
    DECLARE @newpassword sysname  
    DECLARE @oldpassword sysname  
    DECLARE @command varchar(2000)  
  
-- In the following statements, the data stored in temp variables  
-- will be truncated because the buffer size of @login, @oldpassword,  
-- and @newpassword is only 128 characters, but QUOTENAME() can return  
-- up to 258 characters.  
    SET @login = QUOTENAME(@loginname, '''')  
    SET @oldpassword = QUOTENAME(@old, '''')  
    SET @newpassword = QUOTENAME(@new, '''')  
  
-- Construct the dynamic Transact-SQL.  
-- If @new contains 128 characters, then @newpassword will be '123... n  
-- where n is the 127th character.   
-- Because the string returned by QUOTENAME() will be truncated,   
-- it can be made to look like the following statement:  
-- UPDATE Users SET password ='1234. . .[127] WHERE username=' -- other stuff here  
    SET @command = 'UPDATE Users set password = ' + @newpassword   
     + ' where username =' + @login + ' AND password = ' + @oldpassword;  
  
-- Execute the command.  
EXEC (@command);  
GO  

因此,以下语句将把所有用户的密码都设置为在前面的代码中传递的值Therefore, the following statement will set the passwords of all users to the value that was passed in the previous code

EXEC sp_MyProc '--', 'dummy', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'  

使用 REPLACE() 时,可以通过超出分配的缓冲区空间来强迫字符串截断。You can force string truncation by exceeding the allocated buffer space when you use REPLACE(). 以下示例中创建的存储过程显示了可能出现的情况。The stored procedure that is created in the following example shows what can happen.

CREATE PROCEDURE sp_MySetPassword  
    @loginname sysname,  
    @old sysname,  
    @new sysname  
AS  
  
-- Declare variables.  
    DECLARE @login sysname  
    DECLARE @newpassword sysname  
    DECLARE @oldpassword sysname  
    DECLARE @command varchar(2000)  
  
-- In the following statements, data will be truncated because   
-- the buffers allocated for @login, @oldpassword and @newpassword   
-- can hold only 128 characters, but QUOTENAME() can return   
-- up to 258 characters.   
    SET @login = REPLACE(@loginname, '''', '''''')  
    SET @oldpassword = REPLACE(@old, '''', '''''')  
    SET @newpassword = REPLACE(@new, '''', '''''')  
  
-- Construct the dynamic Transact-SQL.  
-- If @new contains 128 characters, @newpassword will be '123...n   
-- where n is the 127th character.   
-- Because the string returned by QUOTENAME() will be truncated, it  
-- can be made to look like the following statement:  
-- UPDATE Users SET password='1234...[127] WHERE username=' -- other stuff here   
    SET @command= 'update Users set password = ''' + @newpassword + ''' where username='''   
     + @login + ''' AND password = ''' + @oldpassword + '''';  
  
-- Execute the command.  
EXEC (@command);  
GO  

与 QUOTENAME() 一样,可以通过声明对所有情况都足够大的临时变量来避免由 REPLACE() 引起的字符串截断。As with QUOTENAME(), string truncation by REPLACE() can be avoided by declaring temporary variables that are large enough for all cases. 应尽可能直接在动态 Transact-SQLTransact-SQL内调用 QUOTENAME() 或 REPLACE()。When possible, you should call QUOTENAME() or REPLACE() directly inside the dynamic Transact-SQLTransact-SQL. 或者,也可以按如下方式计算所需的缓冲区大小。Otherwise, you can calculate the required buffer size as follows. 对于 @outbuffer = QUOTENAME(@input)@outbuffer 的大小应为 2*(len(@input)+1)For @outbuffer = QUOTENAME(@input), the size of @outbuffer should be 2*(len(@input)+1). 使用 REPLACE() 和双引号时(如上一示例),大小为 2*len(@input) 的缓冲区便已足够。When you use REPLACE() and doubling quotation marks, as in the previous example, a buffer of 2*len(@input) is enough.

以下计算涵盖所有情况:The following calculation covers all cases:

WHILE LEN(@find_string) > 0, required buffer size =  
ROUND(LEN(@input)/LEN(@find_string),0) * LEN(@new_string)   
 + (LEN(@input) % LEN(@find_string))  

使用 QUOTENAME(@variable, ']') 时的截断Truncation When QUOTENAME(@variable, ']') Is Used

SQL ServerSQL Server 安全对象的名称被传递给使用 QUOTENAME(@variable, ']')形式的语句时,可能发生截断。Truncation can occur when the name of a SQL ServerSQL Server securable is passed to statements that use the form QUOTENAME(@variable, ']'). 下面的示例显示了这种情况。The following example shows this.

CREATE PROCEDURE sp_MyProc  
    @schemaname sysname,  
    @tablename sysname,  
AS  
  
-- Declare a variable as sysname. The variable will be 128 characters.  
-- But @objectname actually must allow for 2*258+1 characters.   
DECLARE @objectname sysname  
SET @objectname = QUOTENAME(@schemaname)+'.'+ QUOTENAME(@tablename)   
-- Do some operations.  
GO  

串联 sysname 类型的值时,应使用足够大的临时变量来保存每个值的最多 128 个字符。When you are concatenating values of type sysname, you should use temporary variables large enough to hold the maximum 128 characters per value. 应尽可能直接在动态 QUOTENAME() 内调用 Transact-SQLTransact-SQLIf possible, call QUOTENAME() directly inside the dynamic Transact-SQLTransact-SQL. 或者,也可以按上一部分所述来计算所需的缓冲区大小。Otherwise, you can calculate the required buffer size as explained in the previous section.

另请参阅See Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
REPLACE (Transact SQL) REPLACE (Transact-SQL)
QUOTENAME (Transact SQL) QUOTENAME (Transact-SQL)
sp_executesql (Transact-SQL) sp_executesql (Transact-SQL)
保护 SQL ServerSecuring SQL Server