在 SQL Server 中编写安全的动态 SQLWriting Secure Dynamic SQL in SQL Server

SQL 注入是恶意用户输入 Transact-SQL 语句来取代有效输入的过程。SQL Injection is the process by which a malicious user enters Transact-SQL statements instead of valid input. 如果输入的语句没有经过验证直接传递到服务器,并且应用程序不慎执行了注入的代码,这种攻击有可能损坏或毁坏数据。If the input is passed directly to the server without being validated and if the application inadvertently executes the injected code, the attack has the potential to damage or destroy data.

任何构造 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,请确保将命令参数化,绝不要在查询字符串中直接包括参数值。If you use dynamic SQL, be sure to parameterize your commands, and never include parameter values directly into the query string.

SQL 注入攻击剖析Anatomy of a SQL Injection Attack

注入过程的工作原理是过早终止某一文本字符串并追加一个新命令。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. 通过使用分号 (;) 分隔符可以插入多个命令。Multiple commands can be inserted using a semicolon (;) delimiter.

只要注入的 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. 切勿连接未经验证的用户输入。Never concatenate user input that is not validated. 字符串连接是脚本注入的主要入口点。String concatenation is the primary point of entry for script injection.

下面是几条有帮助的准则:Here are some helpful guidelines:

  • 切勿直接从用户输入生成 Transact-SQL 语句,应使用存储过程来验证用户输入。Never build Transact-SQL statements directly from user input; use stored procedures to validate user input.

  • 通过测试类型、长度、格式和范围来验证用户输入。Validate user input by testing type, length, format, and range. 使用 Transact-SQL QUOTENAME() 函数转义系统名称,或使用 REPLACE() 函数转义字符串中的任何字符。Use the Transact-SQL QUOTENAME() function to escape system names or the REPLACE() function to escape any character in a string.

  • 在您的应用程序的每个层中实现多层验证。Implement multiple layers of validation in each tier of 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.

  • 如果使用 XML 文档,则在输入时对照其架构验证所有数据。When you are working with XML documents, validate all data against its schema as it is entered.

  • 在多层环境中,在允许数据进入受信任区域之前所有数据都应该进行验证。In multi-tiered environments, all data should be validated before admission to the trusted zone.

  • 在可以构造文件名的字段中不要接受以下字符串: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.

  • 使用带有存储过程和命令的 SqlParameter 对象以提供类型检查和长度验证。Use SqlParameter objects with stored procedures and commands to provide type checking and length validation.

  • 在客户端代码中使用 Regex 表达式以筛选无效字符。Use Regex expressions in client code to filter invalid characters.

动态 SQL 策略Dynamic SQL Strategies

在过程代码中动态执行已创建的 SQL 语句会中断所属权链,使 SQL Server 按照由动态 SQL 访问的对象检查调用方的权限。Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.

SQL Server 提供了使用存储过程和可执行动态 SQL 的用户定义函数来授予用户对数据的访问权限的方法。SQL Server has methods for granting users access to data using stored procedures and user-defined functions that execute dynamic SQL.

EXECUTE ASEXECUTE AS

EXECUTE AS 子句用 EXECUTE AS 子句中指定的用户的权限替换调用方的权限。The EXECUTE AS clause replaces the permissions of the caller with that of the user specified in the EXECUTE AS clause. 嵌套的存储过程或触发器在代理用户的安全上下文下执行。Nested stored procedures or triggers execute under the security context of the proxy user. 这可能会中断依赖于行级安全性或要求审核的应用程序。This can break applications that rely on row-level security or require auditing. 某些可返回用户标识的函数会返回 EXECUTE AS 子句中指定的用户的标识,而不是原始调用方的标识。Some functions that return the identity of the user return the user specified in the EXECUTE AS clause, not the original caller. 只有在执行该过程或发出 REVERT 语句后,执行上下文才会恢复到原始调用方。Execution context is reverted to the original caller only after execution of the procedure or when a REVERT statement is issued.

证书签名Certificate Signing

在执行使用证书进行签名的存储过程时,授予给证书用户的权限会与调用方的权限合并。When a stored procedure that has been signed with a certificate executes, the permissions granted to the certificate user are merged with those of the caller. 执行上下文保持不变,证书用户不模拟调用方。The execution context remains the same; the certificate user does not impersonate the caller. 为存储过程签名需要执行多个步骤才能实现。Signing stored procedures requires several steps to implement. 每次修改过程时,都必须重新签名。Each time the procedure is modified, it must be re-signed.

跨数据库访问Cross Database Access

在执行动态创建的 SQL 语句时,跨数据库的所属权链接不起作用。Cross-database ownership chaining does not work in cases where dynamically created SQL statements are executed. 在 SQL Server 中,可以通过创建一个可访问另一个数据库中数据的存储过程并用两个数据库中都存在的证书为此过程签名来解决这个问题。You can work around this in SQL Server by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. 这可为用户提供访问该过程所使用的数据库资源的权限,而不必向他们授予数据库访问权或权限。This gives users access to the database resources used by the procedure without granting them database access or permissions.

外部资源External Resources

有关更多信息,请参见以下资源。For more information, see the following resources.

资源Resource 描述Description
SQL Server 联机丛书中的存储过程SQL 注入Stored Procedures and SQL Injection in SQL Server Books Online 说明如何创建存储过程和 SQL 注入工作原理的主题。Topics describe how to create stored procedures and how SQL Injection works.

请参阅See also