Skip to content

SQL Injection Prevention Cheat Sheet / SQL注入预防备忘单

Introduction / 介绍

SQL injection occurs when untrusted data becomes part of a query's SQL syntax. It can expose or modify database contents and, depending on database features and privileges, affect files or execute operating system commands. See the OWASP Web Security Testing Guide for the attack model and testing guidance.

SQL注入发生在不受信任的数据成为查询SQL语法的组成部分时。它可能暴露或修改数据库内容，并可能根据数据库功能和权限影响文件或执行操作系统命令。参见OWASP Web安全测试指南以了解攻击模型和测试指南。

Use parameterized queries for data values, fixed application-owned choices for dynamic query structure, and least privilege to limit damage. These controls apply regardless of whether data comes from an HTTP request, another service, or previously stored data. For other injection types, see the Injection Prevention Cheat Sheet.

使用参数化查询处理数据值，使用应用程序拥有的固定选择来动态构建查询结构，并使用最小权限原则以限制损害。这些控制措施适用于数据来自HTTP请求、其他服务或先前存储数据的情况。对于其他注入类型，请参阅注入预防速查表。

Primary Defenses / 主要防御

Defense Option 1: Prepared Statements (with Parameterized Queries) / 防御选项 1：预编译语句（带参数化查询）

Define the SQL structure separately from its data values, then bind those values through the database API. Prepared statements treat bound values as data rather than SQL syntax. Concatenating untrusted values into the query before preparing it defeats this protection.

将SQL结构与其数据值分开定义，然后通过数据库API绑定这些值。预编译语句将绑定值视为数据而不是SQL语法。在准备查询之前将不受信任的值连接到查询中会破坏这种保护。

This illustrative Java example assumes an existing connection and a customerName value supplied by the application. It shows parameter binding; resource cleanup and error handling are omitted.

此Java示例假设存在一个连接和一个由应用程序提供的customerName值。它展示了参数绑定；省略了资源清理和错误处理。

String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, customerName);
ResultSet results = statement.executeQuery();

An input such as tom' or '1'='1 is treated as the literal username to match, not as a condition added to the query. Parameter binding prevents this value from changing SQL syntax; it does not decide whether the caller may access the matching account.

输入如tom' 或 '1'='1被视为要匹配的文本用户名，而不是添加到查询中的条件。参数绑定防止此值改变SQL语法；它不决定调用者是否可以访问匹配的账户。

When using object-relational mapping (ORM) frameworks, bind untrusted values in query strings instead of concatenating them; Hibernate queries can be vulnerable to injection. For language and framework examples, see the Query Parameterization Cheat Sheet.

当使用对象关系映射（ORM）框架时，在查询字符串中绑定不受信任的值，而不是将它们连接起来；Hibernate查询可能容易受到注入攻击。有关语言和框架示例，请参阅查询参数化速查表。

Stored Procedures / 存储过程

Stored procedures keep SQL code in the database, but are not automatically safe from injection. Bind arguments when calling a procedure; see Oracle's JDBC stored-procedure documentation for the API.

存储过程将SQL代码保留在数据库中，但并不自动免受注入攻击。在调用存储过程时绑定参数；请参阅Oracle JDBC 存储过程文档以了解API。

Binding procedure arguments protects the call, not dynamic SQL constructed inside the procedure. Use static SQL with parameters, or separately bind data values in internal dynamic queries instead of concatenating them into SQL text. Microsoft's secure dynamic SQL guidance calls for reviewing procedures that construct SQL and parameterizing their commands.

绑定过程参数保护调用，而不是过程内部构造的动态 SQL。 使用带参数的静态 SQL，或者在内部动态查询中单独绑定数据值，而不是将它们连接到 SQL 文本中。Microsoft 的安全动态 SQL 指南建议审查构造 SQL 的过程并参数化它们的命令。

For example, PostgreSQL documents EXECUTE ... USING for binding values and format('%I', ...) for quoting identifiers. Select identifiers from fixed application-owned mappings. Identifier quoting protects SQL syntax, but does not restrict which tables the application may select.

例如，PostgreSQL 文档中描述了 EXECUTE ... USING 用于绑定值 和 format('%I', ...) 用于引号标识符。从固定应用程序拥有的映射中选择标识符。标识符引号保护 SQL 语法，但不会限制应用程序可以选择的表。

Allow-List Input Validation / 允许列表输入验证

Parameter markers represent data values, not table names, column names, or SQL keywords such as ASC and DESC; see the PDO parameter-marker limitations. Keep these structural choices fixed in code where possible. When users need to select among them, map their selection to a small set of application-owned tokens allowed for that query and reject unknown choices. For example, map a requested sort direction to the literal ASC or DESC, rather than appending the supplied text.

参数标记代表数据值，而不是表名、列名或 SQL 关键字（如 ASC 和 DESC）；参见 PDO 参数标记限制。尽可能在代码中保持这些结构选择的固定。当用户需要在它们之间进行选择时，将他们的选择映射到允许该查询的应用程序拥有的少量令牌集合，并拒绝未知的选择。例如，将请求的排序方向映射到字面量 ASC 或 DESC，而不是附加提供的文本。

This mapping complements parameter binding. Continue binding all data values, even when they pass type, length, or format checks. Validation of a value does not make it safe to concatenate into SQL. See the Input Validation Cheat Sheet for validation guidance.

这种映射补充了参数绑定。继续绑定所有数据值，即使在它们通过类型、长度或格式检查的情况下。值的验证并不使其安全地连接到 SQL。参见 输入验证速查表 以获取验证指南。

Escaping All Dynamic Inputs (STRONGLY DISCOURAGED) / 转义所有动态输入（强烈不建议）

Do not use manual escaping as a substitute for parameter binding. Escaping depends on the SQL dialect and configuration: for example, MySQL's NO_BACKSLASH_ESCAPES mode disables backslash escaping. Replace query construction that relies on escaping with parameterized queries.

不要将手动转义用作参数绑定的替代品。转义取决于 SQL 方言和配置：例如，MySQL 的 NO_BACKSLASH_ESCAPES 模式 禁用反斜杠转义。用参数化查询替换依赖于转义的查询构造。

Hardening / 加固

Use least privilege alongside parameterized queries and allow-list mapping. It limits the damage an attacker can cause through a database account; it does not prevent SQL injection.

使用最小权限与参数化查询和允许列表映射一起使用。它限制了攻击者通过数据库账户造成的损害；它并不能防止 SQL 注入。

Least Privilege / 最小权限

To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away.

Make sure that accounts that only need read access are only granted read access to the tables they need access to. DO NOT ASSIGN DBA OR ADMIN TYPE ACCESS TO YOUR APPLICATION ACCOUNTS. We understand that this is easy, and everything just "works" when you do it this way, but it is very dangerous.

Minimizing Application and OS Privileges

SQL injection is not the only threat to your database data. Attackers can simply change the parameter values from one of the legal values they are presented with, to a value that is unauthorized for them, but the application itself might be authorized to access. As such, minimizing the privileges granted to your application will reduce the likelihood of such unauthorized access attempts, even when an attacker is not trying to use SQL injection as part of their exploit.

While you are at it, you should minimize the privileges of the operating system account that the DBMS runs under. Don't run your DBMS as root or system! Most DBMSs run out of the box with a very powerful system account. For example, MySQL runs as system on Windows by default! Change the DBMS's OS account to something more appropriate, with restricted privileges.

Details Of Least Privilege When Developing

If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead of the underlying table. Rarely, if ever, grant create or delete access to database accounts.

If you adopt a policy where you use stored procedures everywhere, and don't allow application accounts to directly execute their own queries, then restrict those accounts to only be able to execute the stored procedures they need. Don't grant them any rights directly to the tables in the database.

Least Admin Privileges For Multiple DBs / 多个数据库的最小管理权限

The designers of web applications should avoid using the same owner/admin account in the web applications to connect to the database. Different DB users should be used for different web applications.

Web应用程序的设计者应避免在Web应用程序中使用相同的所有者/管理员账户连接到数据库。应使用不同的数据库用户为不同的Web应用程序。

In general, each separate web application that requires access to the database should have a designated database user account that the application will use to connect to the DB. That way, the designer of the application can have good granularity in the access control, thus reducing the privileges as much as possible. Each DB user will then have select access to only what it needs, and write-access as needed.

一般来说，每个需要访问数据库的独立Web应用程序都应该有一个指定的数据库用户账户，该应用程序将使用该账户连接到数据库。这样，应用程序的设计者可以在访问控制方面具有很好的粒度，从而尽可能减少权限。然后，每个数据库用户将只能选择访问它需要的部分，并根据需要具有写入访问。

As an example, a login page requires read access to the username and password fields of a table, but no write access of any form (no insert, update, or delete). However, the sign-up page certainly requires insert privilege to that table; this restriction can only be enforced if these web apps use different DB users to connect to the database.

例如，登录页面需要读取用户名和密码字段的读取访问权限，但不需要任何形式的写入访问（没有插入、更新或删除）。然而，注册页面肯定需要对该表的插入权限；只有当这些Web应用程序使用不同的数据库用户连接到数据库时，这种限制才能得到执行。

Enhancing Least Privilege with SQL Views / 通过SQL视图增强最小权限

You can use SQL views to further increase the granularity of access by limiting the read access to specific fields of a table or joins of tables. It could have additional benefits.

您可以使用SQL视图通过限制对表或表连接的特定字段的读取访问来进一步增加访问的粒度。这可能会带来额外的优势。

For example, a product catalog can use a view that exposes product names and retail prices while omitting supplier costs. Grant the application's database account only SELECT access to that view, without access to the underlying table or other objects that expose the omitted fields. Verify your database's view privilege rules; for example, PostgreSQL normally checks underlying table access using the view owner's privileges.

例如，产品目录可以使用一个视图，该视图公开产品名称和零售价格，而省略供应商成本。仅授予应用程序的数据库账户对该视图的SELECT访问权限，而不允许访问底层数据表或其他公开省略字段的其它对象。验证您数据库的视图权限规则；例如，PostgreSQL通常使用视图所有者的权限来检查底层数据表的访问权限。

This limits the data exposed through a compromised application account. Queries against views still require parameterization to prevent SQL injection.

这限制了通过受损害的应用程序账户暴露的数据。针对视图的查询仍然需要参数化以防止SQL注入。

Detection / 检测

Review query construction in application code and stored procedures. Check that untrusted values are bound rather than concatenated into SQL, and that dynamic identifiers and sort directions come from the query's fixed mapping. Preparing the outer call is not evidence that a procedure's internal SQL is safe.

审查应用程序代码和存储过程中的查询构造。确保不受信任的值是绑定而不是连接到 SQL，动态标识符和排序方向来自查询的固定映射。外部调用的准备并不能证明存储过程的内部 SQL 是安全的。

Use the OWASP Web Security Testing Guide's SQL injection tests for manual and automated testing, including blind and out-of-band techniques.

使用 OWASP 网络安全测试指南的 SQL 注入测试 进行手动和自动化测试，包括盲注和带外技术。

References / 参考