Query Parameterization Cheat Sheet / 查询参数化备忘单¶

Introduction / 介绍¶

SQL Injection) （SQL注入） is one of the most dangerous web vulnerabilities. As of the OWASP Top 10:2025, it is categorized under A05:2025-Injection.

It represents a serious threat because SQL Injection allows evil attacker code to change the structure of a web application's SQL statement in a way that can steal data, modify data, or potentially facilitate command injection to the underlying OS.

它代表了一个严重的威胁，因为SQL注入允许恶意攻击者代码以窃取数据、修改数据或潜在地便利底层操作系统命令注入的方式改变Web应用程序的SQL语句结构。

This cheat sheet is a derivative work of the SQL Injection Prevention Cheat Sheet.

这份速查表是SQL注入预防速查表的衍生作品。

Parameterized Query Examples / 参数化查询示例¶

SQL Injection is best prevented through the use of parameterized queries. The following chart demonstrates, with real-world code samples, how to build parameterized queries in most of the common web languages. The purpose of these code samples is to demonstrate to the web developer how to avoid SQL Injection when building database queries within a web application.

SQL注入的最佳预防方法是使用参数化查询。以下图表通过实际的代码示例展示了如何在大多数常见的Web语言中构建参数化查询。这些代码示例的目的是向Web开发者展示如何在构建Web应用程序中的数据库查询时避免SQL注入。

Please note, many client side frameworks and libraries offer client side query parameterization. These libraries often just build queries with string concatenation before sending raw queries to a server. Please ensure that query parameterization is done server-side!

请注意，许多客户端框架和库提供了客户端查询参数化。这些库通常在发送原始查询到服务器之前，只是通过字符串连接构建查询。请确保在服务器端进行查询参数化！

Prepared Statement Examples / 预处理语句示例¶

Using Java built-in feature / 使用Java内置功能¶

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

Using Java with JPA/Hibernate¶

// HQL
@Entity // declare as entity;
@NamedQuery(
 name="findByDescription",
 query="FROM Inventory i WHERE i.productDescription = :productDescription"
)
public class Inventory implements Serializable {
 @Id
 private long id;
 private String productDescription;
}

// Use case
// This should REALLY be validated too
String userSuppliedParameter = request.getParameter("Product-Description");
// Perform input validation to detect attacks
List<Inventory> list =
 session.getNamedQuery("findByDescription")
 .setParameter("productDescription", userSuppliedParameter).list();

// Criteria API
// This should REALLY be validated too
String userSuppliedParameter = request.getParameter("Product-Description");
// Perform input validation to detect attacks
Inventory inv = (Inventory) session.createCriteria(Inventory.class).add
(Restrictions.eq("productDescription", userSuppliedParameter)).uniqueResult();

Using .NET built-in feature / 使用.NET内置功能¶

String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
try {
   OleDbCommand command = new OleDbCommand(query, connection);
   command.Parameters.Add(new OleDbParameter("customerName", CustomerName.Text));
   OleDbDataReader reader = command.ExecuteReader();
   // …
} catch (OleDbException se) {
   // error handling
}

Using ASP .NET built-in feature / 使用 ASP .NET 内置功能¶

string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));
command.Parameters["@CustomerId"].Value = 1;

Using Ruby with ActiveRecord / 使用 Ruby 与 ActiveRecord¶

## Create
Project.create!(:name => 'owasp')
## Read
Project.all(:conditions => "name = ?", name)
Project.all(:conditions => { :name => name })
Project.where("name = :name", :name => name)
## Update
project.update_attributes(:name => 'owasp')
## Delete
Project.delete(:name => 'name')

Using Ruby built-in feature / 使用 Ruby 内置功能¶

insert_new_user = db.prepare "INSERT INTO users (name, age, gender) VALUES (?, ? ,?)"
insert_new_user.execute 'aizatto', '20', 'male'

Using PHP with PHP Data Objects / 使用 PHP 与 PHP 数据对象¶

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Using Cold Fusion built-in feature / 使用 Cold Fusion 内置功能¶

<cfquery name = "getFirst" dataSource = "cfsnippets">
    SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID =
    <cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER">
</cfquery>

Using PERL with Database Independent Interface / 使用 PERL 与数据库无关接口¶

my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";
my $sth = $dbh->prepare( $sql );
$sth->execute( $bar, $baz );

Using Rust with SQLx / 使用 Rust 与 SQLx¶

// Input from CLI args but could be anything
let username = std::env::args().last().unwrap();

// Using build-in macros (compile time checks)
let users = sqlx::query_as!(
        User,
        "SELECT * FROM users WHERE name = ?",
        username
    )
    .fetch_all(&pool)
    .await 
    .unwrap();

// Using built-in functions
let users: Vec<User> = sqlx::query_as::<_, User>(
        "SELECT * FROM users WHERE name = ?"
    )
    .bind(&username)
    .fetch_all(&pool)
    .await
    .unwrap();

Stored Procedure Examples / 存储过程示例¶

The SQL you write in your web application isn't the only place that SQL injection vulnerabilities can be introduced. If you are using Stored Procedures, and you are dynamically constructing SQL inside them, you can also introduce SQL injection vulnerabilities.

您在Web应用程序中编写的SQL并非唯一可能引入SQL注入漏洞的地方。如果您使用存储过程，并在其中动态构建SQL，也可能引入SQL注入漏洞。

Dynamic SQL can be parameterized using bind variables, to ensure the dynamically constructed SQL is secure.

动态SQL可以使用绑定变量进行参数化，以确保动态构建的SQL是安全的。

Here are some examples of using bind variables in stored procedures in different databases.

以下是不同数据库中在存储过程中使用绑定变量的示例。

Oracle using PL/SQL¶

Normal Stored Procedure / 常规存储过程¶

No dynamic SQL being created. Parameters passed in to stored procedures are naturally bound to their location within the query without anything special being required:

没有创建动态SQL。传递给存储过程的参数会自然绑定到查询中的位置，无需特殊要求：

PROCEDURE SafeGetBalanceQuery(UserID varchar, Dept varchar) AS BEGIN
   SELECT balance FROM accounts_table WHERE user_ID = UserID AND department = Dept;
END;

Stored Procedure Using Bind Variables in SQL Run with EXECUTE / 使用EXECUTE运行的带有绑定变量的SQL存储过程¶

Bind variables are used to tell the database that the inputs to this dynamic SQL are 'data' and not possibly code:

绑定变量用于告诉数据库，此动态SQL的输入是'数据'而不是可能为代码：

PROCEDURE AnotherSafeGetBalanceQuery(UserID varchar, Dept varchar)
          AS stmt VARCHAR(400); result NUMBER;
BEGIN
   stmt := 'SELECT balance FROM accounts_table WHERE user_ID = :1
            AND department = :2';
   EXECUTE IMMEDIATE stmt INTO result USING UserID, Dept;
   RETURN result;
END;

SQL Server using Transact-SQL / 使用Transact-SQL的SQL Server¶

Normal Stored Procedure / 常规存储过程¶

No dynamic SQL being created. Parameters passed in to stored procedures are naturally bound to their location within the query without anything special being required:

没有创建动态SQL。传递给存储过程的参数会自然绑定到查询中的位置，无需特殊要求：

PROCEDURE SafeGetBalanceQuery(@UserID varchar(20), @Dept varchar(10)) AS BEGIN
   SELECT balance FROM accounts_table WHERE user_ID = @UserID AND department = @Dept
END

Stored Procedure Using Bind Variables in SQL Run with EXEC / 使用绑定变量的 SQL 存储过程通过 EXEC 运行¶

Bind variables are used to tell the database that the inputs to this dynamic SQL are 'data' and not possibly code:

绑定变量用于告诉数据库，此动态SQL的输入是'数据'而不是可能为代码：

PROCEDURE SafeGetBalanceQuery(@UserID varchar(20), @Dept varchar(10)) AS BEGIN
   DECLARE @sql VARCHAR(200)
   SELECT @sql = 'SELECT balance FROM accounts_table WHERE '
                 + 'user_ID = @UID AND department = @DPT'
   EXEC sp_executesql @sql,
                      '@UID VARCHAR(20), @DPT VARCHAR(10)',
                      @UID=@UserID, @DPT=@Dept
END

References / 参考¶

Oracle JDBC: Using Prepared Statements) （Oracle JDBC：使用预编译语句）
PHP PDO: Prepared Statements and Stored Procedures) （PHP PDO：预处理语句和存储过程）