Prepared statement

最新推荐文章于 2024-04-17 18:37:43 发布

kezhen

最新推荐文章于 2024-04-17 18:37:43 发布

阅读量3k

点赞数

分类专栏：渗透测试相关

渗透测试相关专栏收录该内容

37 篇文章 0 订阅

订阅专栏

http://en.wikipedia.org/wiki/Prepared_statement

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

在数据库管理系统中，准备好的声明（prepared statement）或者参数化的声明是一大特色，该声明可以高效的执行相同或者相似的数据库语句。通常使用SQL语句（如查询或者更新）时，prepared statement以模板的形式替换具体的常量值来执行。

The typical workflow of using a prepared statement is as follows:

使用prepared statement的典型工作流程如下：

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders orbind variables (labelled "?" below):
- INSERT INTO PRODUCT (name, price) VALUES (?, ?)
The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.

1、准备：应用程序创建声明模板并发送至数据库管理系统。不指定具体的查询值，这种值被称之为参数，占位符或者绑定变量（下面的例子指“？”）：

insert into product (name,price) values (?,?)

2、数据库管理系统解析、编译并对声明模板进行查询优化，并存储非执行的结果。

3、执行：一段时间后，应用程序为参数提供具体的数值，数据库管理系统执行数据库语句（可能返回结果）。应用程序可以以不同的值执行多次声明。在这个例子中可以提供name为'Bread' ，price为1.00

As compared to executing SQL statements directly, prepared statements offer two main advantages:^[1]

The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times. Not all optimization can be performed at the time the prepared statement is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.^[2]
Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

跟直接执行SQL语句相比，prepared statement 有两大优势：

多次执行，仅需要一次编译和优化语句。在编译prepared statement时不进行所有的优化，主要有两个原因：最好的优化方法依赖于参数的具体数值，并且随着时间的推移可能需要改变表格和索引。
Prepared statement可以防御SQL注入攻击，因为参数化值会以不同的协议进行传送，所以不能被准确的泄露。如果从外部输入不能推断出源声明模板，那么SQL注入就不会发生。

On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server.^[3] Implementation limitations may also lead to performance penalties: some versions of MySQL did not cache results of prepared queries,^[4] and some DBMSs such as PostgreSQL do not perform additional query optimization during execution.^[5]^[6]

另一方面，如果查询仅执行一次，服务端的prepared statements可能会变慢，因为需要两次向服务器传送信息。实现的局限性可能导致性能的下降：Mysql的一些版本不缓存准备好的查询结果、一些管理系统如PostgreSQL在执行时不进行额外的查询优化。

A stored procedure, which is also precompiled and stored on the server for later execution, has similar advantages. Unlike a stored procedure, a prepared statement is not normally written in a procedural language and cannot use or modify variables or use control flow structures, relying instead on the declarative database query language. Due to their simplicity and client-side emulation, prepared statements are more portable across vendors.

存储过程也是一种在执行前事先在服务器端编译并存储的一种方式，故具有相似的优势。与存储过程不同的是，prepared statement不是以过程语言实现的，也不会使用或者修改变量，不会使用控制流程结构，而是依赖于数据库查询语言。由于prepared statement的简易和客户端的竞争，使其具有更好的可移植性。

Software support[edit]

Prepared statements are widely supported by major DBMSs, including MySQL,^[7] Oracle,^[8] DB2,^[9] Microsoft SQL Server,^[10] and PostgreSQL.^[5] Prepared statements are normally executed through a non-SQL binary protocol, for efficiency and protection from SQL injection, but with some DBMSs such as MySQL are also available using a SQL syntax for debugging purposes.^[11]

大多数的数据库管理系统（如MySQL/Oracle/DB2/Microsoft SQL Server/PostgreSQL）都会广泛支持Prepared statement。因Prepared statement一般以非SQL二进制协议执行，所以可以有效的预防SQL注入攻击。但是一些数据库管理系统（如Mysql）为了调试方便，也提供SQL语法。

A number of programming languages support prepared statements in their standard libraries and will emulate them on the client side even if the underlying DBMS does not support them, includingJava's JDBC,^[12] Perl's DBI,^[13] PHP's PDO ^[1] and Python's DB-API.^[14] Client-side emulation can be faster for queries which are executed only once, by reducing the number of round trips to the server, but is usually slower for queries executed many times. It resists SQL injection attacks equally effectively.

一些编程语言（包括java的JDBC、Perl的DBI、PHP的PDO和Python 的DB-API）在他们的标准库中也支持prepared statement，即使数据库管理系统不支持，他们也会在客户端模拟prepared statement。因为客户端模拟避免的两次向服务器传送信息，所以客户端模拟在执行一次时的速度比较快。当然，这种模拟对防御SQL注入攻击也是等效的。

Many types of SQL injection attacks can be eliminated by disabling literals, effectively requiring the use of prepared statements; as of 2007 only H2 supports this feature.^[15]

很多类型的SQL注入攻击可以通过禁止文字的方式进行消除，有效的促进prepared statement的使用，可是截止2007年，仅H2数据库支持这种特征。

Examples[edit]

Java JDBC[edit]

This example uses Java and the JDBC API:

java.sql.PreparedStatement stmt = connection.prepareStatement(
               "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?");
stmt.setString(1, username);
stmt.setInt(2, roomNumber);
stmt.executeQuery();

Java PreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.

PHP PDO[edit]

This example uses PHP and PHP Data Objects (PDO):

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $password));

PERL DBI[edit]

This example uses Perl and DBI:

my $stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?');
$stmt->execute($username, $password);

C# ADO.NET[edit]

This example uses C# and ADO.NET:

using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
 
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);
 
    using (SqlDataReader dataReader = command.ExecuteReader())
    {
        // ...
    }
}

ADO.NET SqlCommand will accept any type for the value parameter of AddWithValue, and type conversion occurs automatically. Note the use of "named parameters" (i.e. "@username") rather than "?" - this allows you to use a parameter multiple times and in any arbitrary order within the query command text.

ADO.NET 中的SqlCommand可以通过AddWithValue函数向value传递任何类型的参数，并且类型可以自动转换。注意使用名字化的参数（如@username）而不是"?"，这一点可以在查询命令中多次且任意次序的使用同一个参数。

However, the AddWithValue method should not be used with variable length data types, like varchar and nvarchar. This is because .NET assumes the length of the parameter to be the length of the given value, rather than getting the actual length of from the database via reflection. The consequence of this is that a different query plan is compiled and stored for each different length. In general, the maximum number of 'duplicate' plans is the product of the lengths of the variable length columns as specified in the database. For this reason, it is important to use the standard Add method for variable length columns:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, where ParamLength is the length as specified in the database.

Since the standard Add method needs to be used for variable length data types, it is a good habit to use it for all parameter types.

但是，AddWithValue方法不允许使用变长类型的变量，如varchar和nvarchar。这是因为.NET假设参数长度满足给定值的长度，而不是通过映射从数据库获取实际的长度。这就导致了查询语句依据不同的长度以多个方案的形式进行编译和存储。一般情况下，最大长度的方案是根据数据库中可变长度列的长度而产生的。因此，为可变长度的列使用标准的Add函数至关重要：

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue

其中ParamLength是数据库中指定的长度。

因为Add方法可用于可变长度的列，所以对所有的参数使用Add函数是一个不错的习惯。

Python DB-API[edit]

This example uses Python DB-API with SQLite and paramstyle='qmark':

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
 
_users = [('mother', 'red'),
          ('father', 'green'),
          ('me', 'blue')]
c.executemany('INSERT INTO users VALUES (?,?)', _users)
 
params = ('B', 'green')
c.execute('SELECT * FROM users WHERE username=? AND room=?', params)
c.fetchone()

References[edit]

^ Jump up to:^a ^b The PHP Documentation Group. "Prepared statements and stored procedures". PHP Manual. Retrieved 25 September 2011.
Jump up^ Petrunia, Sergey (28 April 2007). "MySQL Optimizer and Prepared Statements". Sergey Petrunia's blog. Retrieved 25 September 2011.
Jump up^ Zaitsev, Peter (2 August 2006). "MySQL Prepared Statements". MySQL Performance Blog. Retrieved 25 September 2011.
Jump up^ "7.6.3.1. How the Query Cache Operates". MySQL 5.1 Reference Manual. Oracle. Retrieved 26 September 2011.
^ Jump up to:^a ^b "PREPARE". PostgreSQL 9.0.5 Documentation. PostgreSQL Global Development Group. Retrieved 26 September 2011.
Jump up^ Smith, Lukas Kahwe (14 May 2008). "Prepared statement gotchas". Poo-tee-weet. Retrieved 26 September 2011.
Jump up^ Oracle. "20.9.4. C API Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
Jump up^ "13 Oracle Dynamic SQL". Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. Retrieved 25 September 2011.
Jump up^ "Using the PREPARE and EXECUTE statements". i5/OS Information Center, Version 5 Release 4. IBM. Retrieved 25 September 2011.
Jump up^ "SQL Server 2008 R2: Preparing SQL Statements". MSDN Library. Microsoft. Retrieved 25 September 2011.
Jump up^ Oracle. "12.6. SQL Syntax for Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
Jump up^ "Using Prepared Statements". The Java Tutorials. Oracle. Retrieved 25 September 2011.
Jump up^ Bunce, Tim. "DBI-1.616 specification". CPAN. Retrieved 26 September 2011.
Jump up^ "Python PEP 289: Python Database API Specification v2.0".
Jump up^ "SQL Injections: How Not To Get Stuck". The Codist. 8 May 2007. Retrieved February 1, 2010.