Prepare (SQL)
在數據庫管理系統（DBMS）中，預處理語句（Prepared Statement，又稱參數化語句）是一種將SQL代碼預先編譯並存儲，從而實現代碼與數據分離的特性。其主要優勢包括：[1]
- 效率：由於無需重複編譯，可以多次重複使用。
- 安全：通過減少或消除SQL注入攻擊來提高安全性。
預處理語句採取預編譯模板的形式，在每次執行時替換其中的常量值。它通常用於INSERT、SELECT或 UPDATE等DML語句。
預處理語句常見工作流：
- 準備 (Prepare)：應用程序創建語句模板並發送給DBMS。某些值保持未指定狀態，稱為參數、占位符或綁定變量（如下文中的?）：
INSERT INTO products (name, price) VALUES (?, ?);
- 編譯 (Compile)：DBMS對模板進行編譯（解析、優化和翻譯），存儲結果但不執行。
- 執行 (Execute)：應用程序為參數提供（或綁定）具體值，DBMS 執行該語句。應用程序可以多次使用不同值請求執行。例如，先傳入 "bike" 和 "10900"，隨後傳入 "shoes" 和 "7400"。
如果不使用預處理語句，則直接調用合併了代碼與數據的 SQL。與上述例子等價的實現：
INSERT INTO products (name, price) VALUES ('bike', '10900');
預處理語句不能在編譯時完成所有優化，原因有二：最佳執行計劃可能取決於參數的具體值，且執行計劃會隨表和索引的變化而改變。[2]
此外，如果查詢只執行一次，由於增加了與服務器的往返次數，預處理語句可能更慢。[3]實現限制也會導致性能懲罰，例如一些版本的MySQL並不緩存預處理查詢的結果。[4]與存儲過程不同，預處理語句通常不使用過程化語言編寫，無法使用控制流結構（如 IF、LOOP），而是依賴聲明式查詢語言。
軟件支持
[編輯]大部分DBMS, 包括SQLite[5]、MySQL[6]、Oracle[7]、IBM DB2[8]、Microsoft SQL Server[9]、PostgreSQL[10]均支持此功能。預處理語句通常通過非SQL的二進制協議（non-SQL binary protocol）來執行，以實現高效率並防止SQL注入。但在某些數據庫管理系統（如MySQL）中，為了調試目的，也可以使用SQL語法來操作預處理語句。[11]
許多編程語言（如Java JDBC[12]、Perl的DBI,[13]、Python DB-API[14]、PHP的PDO[1]）在標準庫中支持預處理語句，甚至在數據庫不支持時進行客戶端模擬。對於僅執行一次的查詢，客戶端模擬（Client-side emulation）可以通過減少與服務器的往返次數（round trips）來提高速度；但對於需要多次執行的查詢，它的速度通常較慢。在抵禦 SQL 注入攻擊方面，客戶端模擬具有同樣有效的防護能力。
通過「禁用字面量（disabling literals）」可以消除多種類型的SQL注入攻擊，這實際上強制要求必須使用預處理語句；截至2007年，僅有H2數據庫支持這一特性。[15]
例子
[編輯]C++
[編輯]C++ MySQL Connector/C++ X DevAPI[16][17], 預處理語句通過庫的API隱式實現。
import <mysqlx/xdevapi.h>
import std;
using mysqlx::Schema;
using mysqlx::Session;
using mysqlx::Table;
int main() {
try {
Session session("localhost", 33060, "user", "password");
Schema db = session.getSchema("testdb");
Table users = db.getTable("users");
users.insert("name", "age")
.values("Alice", 30)
.execute();
} catch (const mysqlx::Error& e) {
std::println(stderr, "MySQL error: {}", e.what());
}
}
C# ADO.NET
[編輯]namespace Wikipedia.Examples;
using System;
using System.Data;
using Microsoft.Data.SqlClient;
public class Example
{
static void Main(string[] args)
{
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可以為AddWithValue的value參數接受任何類型，且類型轉換是自動進行的。請注意，這裡使用的是「命名參數」（例如"@username"）而非占位符"?"——這允許你在查詢命令文本中多次使用同一個參數，且無需考慮參數出現的先後順序。
然而，不應將AddWithValue方法用於變長數據類型（如varchar和nvarchar）。這是因為.NET會將參數長度假設為當前所賦數值的實際長度，而不是通過反射從數據庫獲取定義的實際長度。其後果是：對於每一個不同的數據長度，數據庫都會編譯並存儲一個不同的查詢計劃（Query Plan）。通常情況下，這類「重複」執行計劃的最大數量，是數據庫中指定的各變長列長度的乘積。基於上述原因，對於變長列，務必使用標準的 Add 方法： command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue;
其中，「ParamLength」應為數據庫中定義的長度。
由於變長類型必須使用標準Add方法，養成對所有參數類型都使用該方法的習慣是一個良好的編程實踐。
Go
[編輯]// Define a BookModel type which wraps a sql.DB connection pool.
type BookModel struct {
DB *sql.DB
}
// This will insert a new book into the database.
func (m *BookModel) Insert(title, author string) (int, error) {
stmt := "INSERT INTO book (title, author, created) VALUES(?, ?, UTC_TIMESTAMP())"
// The "Exec" function will automatically prepare the statement for you,
// which requires an additional round-trip to the database.
//
// It is possible to avoid prepared statements, if you are sure they are not needed.
// See ExecerContext for details. https://pkg.go.dev/database/sql/driver#ExecerContext
//
// Other functions such as "Query" work the same way,
// and have an equivalent interface.
result, err := m.DB.Exec(stmt, title, author)
if err != nil {
return 0, err
}
id, err := result.LastInsertId() // Not supported in the Postgres driver -- use RETURNING instead.
if err != nil {
return 0, err
}
// The ID returned has the type int64, so we convert it to an int type
// before returning.
//
// Keep in mind, on 32-bit machines, this can potentially truncate the value.
// It is usually more safe to return int64 from your function directly,
// which is still supported on 32-bit machines.
return int(id), nil
}
占位符參數的語法因數據庫而異。MySQL、SQL Server 和 SQLite 使用 ? 符號，而 PostgreSQL 則使用 $N 符號。例如，如果你使用的是 PostgreSQL，你會這樣寫：
_, err := m.DB.Exec("INSERT INTO ... VALUES ($1, $2, $3)", ...)
Java JDBC
[編輯]package org.wikipedia.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class Main {
public static void main(String[] args) throws SQLException {
MysqlDataSource ds = new MysqlDataSource();
ds.setDatabaseName("mysql");
ds.setUser("root");
try (Connection conn = ds.getConnection()) {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
}
try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
stmt.setString(1, "bike");
stmt.setInt(2, 10900);
stmt.executeUpdate();
stmt.setString(1, "shoes");
stmt.setInt(2, 7400);
stmt.executeUpdate();
stmt.setString(1, "phone");
stmt.setInt(2, 29500);
stmt.executeUpdate();
}
try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
stmt.setString(1, "shoes");
ResultSet rs = stmt.executeQuery();
rs.next();
System.out.println(rs.getInt(2));
}
}
}
}
Java PreparedStatement對所有主要內建數據類型使用"setters" (setInt(int), setString(String), setDouble(double),等等)。
PHP PDO
[編輯]<?php
// Connect to a database named "mysql", with the password "root"
$connection = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8mb4', 'root');
// Execute a request on the connection, which will create
// a table "products" with two columns, "name" and "price"
$connection->exec('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');
// Prepare a query to insert multiple products into the table
$statement = $connection->prepare('INSERT INTO products VALUES (?, ?)');
$products = [
['bike', 10900],
['shoes', 7400],
['phone', 29500],
];
// Iterate through the products in the "products" array, and
// execute the prepared statement for each product
foreach ($products as $product) {
$statement->execute($product);
}
// Prepare a new statement with a named parameter
$statement = $connection->prepare('SELECT * FROM products WHERE name = :name');
$statement->execute([
':name' => 'shoes',
]);
// Use array destructuring to assign the product name and its price
// to corresponding variables
[ $product, $price ] = $statement->fetch();
// Display the result to the user
echo "The price of the product {$product} is \${$price}.";
Perl DBI
[編輯]This example uses Perl and DBI:
#!/usr/bin/env perl -w
use strict;
use DBI;
my ($db_name, $db_user, $db_password) = ('my_database', 'moi', 'Passw0rD');
my $dbh = DBI->connect("DBI:mysql:database=$db_name", $db_user, $db_password,
{ RaiseError => 1, AutoCommit => 1})
or die "ERROR (main:DBI->connect) while connecting to database $db_name: " .
$DBI::errstr . "\n";
$dbh->do('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');
my $sth = $dbh->prepare('INSERT INTO products VALUES (?, ?)');
$sth->execute(@$_) foreach ['bike', 10900], ['shoes', 7400], ['phone', 29500];
$sth = $dbh->prepare("SELECT * FROM products WHERE name = ?");
$sth->execute('shoes');
print "$$_[1]\n" foreach $sth->fetchrow_arrayref;
$sth->finish;
$dbh->disconnect;
Python DB-API
[編輯]import mysql.connector
with mysql.connector.connect(database="mysql", user="root") as conn:
with conn.cursor(prepared=True) as cursor:
cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)")
params: list[tuple[str, int]] = [("bike", 10900), ("shoes", 7400), ("phone", 29500)]
cursor.executemany("INSERT INTO products VALUES (%s, %s)", params)
params = ("shoes",)
cursor.execute("SELECT * FROM products WHERE name = %s", params)
print(cursor.fetchall()[0][1])
參考文獻
[編輯]- ^ 1.0 1.1 The PHP Documentation Group. Prepared statements and stored procedures. PHP Manual. [25 September 2011]. （原始內容存檔於2022-04-08）.
- ^ Petrunia, Sergey. MySQL Optimizer and Prepared Statements. Sergey Petrunia's blog. 28 April 2007 [25 September 2011]. （原始內容存檔於2018-02-05）.
- ^ Zaitsev, Peter. MySQL Prepared Statements. MySQL Performance Blog. 2 August 2006 [25 September 2011]. （原始內容存檔於2014-03-23）.
- ^ 7.6.3.1. How the Query Cache Operates. MySQL 5.1 Reference Manual. Oracle. [26 September 2011]. （原始內容存檔於2011-09-25）.
- ^ Prepared Statement Objects. SQLite. 18 Oct 2021 [2026-03-13]. （原始內容存檔於2022-05-07）.
- ^ Oracle. 20.9.4. C API Prepared Statements. MySQL 5.5 Reference Manual. [27 March 2012].
- ^ 13 Oracle Dynamic SQL. Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. [25 September 2011]. （原始內容存檔於2011-10-26）.
- ^ SQL: Pengertian, Sejarah, Fungsi, dan Jenis Perintah SQL. （原始內容存檔於2024-03-01）.
- ^ SQL Server 2008 R2: Preparing SQL Statements. MSDN Library. Microsoft. [25 September 2011]. （原始內容存檔於2017-07-05）.
- ^ PREPARE. PostgreSQL 9.5.1 Documentation. PostgreSQL Global Development Group. [27 February 2016]. （原始內容存檔於2018-03-09）.
- ^ Oracle. 12.6. SQL Syntax for Prepared Statements. MySQL 5.5 Reference Manual. [27 March 2012]. （原始內容存檔於2019-07-16）.
- ^ Using Prepared Statements. The Java Tutorials. Oracle. [25 September 2011]. （原始內容存檔於2011-11-12）.
- ^ Bunce, Tim. DBI-1.616 specification. CPAN. [26 September 2011]. （原始內容存檔於2026-01-07）.
- ^ Python PEP 289: Python Database API Specification v2.0. [2026-03-13]. （原始內容存檔於2022-03-03）.
- ^ SQL Injections: How Not To Get Stuck. The Codist. 8 May 2007 [February 1, 2010]. （原始內容存檔於2011-07-16）.
- ^ Oracle Corporation. [dev.mysql.com/doc/dev/connector-cpp/latest/ MySQL Connector/C++ Documentation] 請檢查
|url=值 (幫助). dev.mysql.com. Oracle Corporation. 20 January 2026. - ^ Oracle Corporation. X DevAPI User Guide. dev.mysql.com. Oracle Corporation. [1 March 2026].
- ^ PHP manual entry for PDO::prepare(). [2026-03-13]. （原始內容存檔於2026-02-25）.