What are prepared statements? How to use it?

Many mature databases support the concept of prepared statements (Prepared Statements). What are they? You can think of them as a compiled SQL statement template to be executed, which can be customized with different variable parameters. Prepared statements have two main advantages:

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database analyzes, compiles, and optimizes its plan for executing the query. For complex queries, if you have to repeatedly execute queries with different parameters but the same structure many times, this process will take up a lot of time and make your application slow down. By using a prepared statement you can avoid repeated analysis, compilation, and optimization. Simply put, prepared statements use fewer resources and execute faster.

The parameters passed to the prepared statement do not need to be quoted, the underlying driver will handle this for you. If your application exclusively uses prepared statements, you can be sure that no SQL injection will occur. (However, if you are still building other parts of the query based on untrusted input, this is still risky.)

Because prepared statements are so useful, they are the only way to use PDO for Mock implementations provided by databases that do not support this feature. This allows you to use a unified data access specification without having to worry about whether the database itself has this feature.

/*
使用预处理语句重复插入数据(1)
此示例演示了一个通过向命名占位符代入一个name和一个value值来执行的INSERT查询
*/
连接数据库:$dbh = new PDO("mysql:host=127.0.0.1;dbname=dbname",'username','123');
注:如果插入数据后出现乱码的话,注意检查各个页面的字符集设置,尤其是关于pdo处理的php页面仅有header头设置为utf-8有时也不行,这时就在执行SQL语句前设置数据库的字符集$dbh->query("set names utf8");
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value); //插入一行
$name = 'one';
$value = 1;
$stmt->execute();//使用不同的值插入另一行
$name = 'two';
$value = 2;
$stmt->execute();

/*
使用预处理语句重复插入数据(2)
此示例演示了一个通过向用?表示的占位符代入一个name和一个value值来执行的INSERT查询
*/
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value); // 插入一行
$name = 'one';
$value = 1;
$stmt->execute(); // 使用不同的值插入另一行
$name = 'two';
$value = 2;
$stmt->execute();

/*
通过预处理语句获取数据
此示例演示使用从表单获取的数据为关键值来执行查询获取数据。用户的输入会被自动添加引号，所以这儿不存在SQL注入攻击的危险。
*/
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
    if ($stmt->execute(array($_GET['name']))) {
        while ($row = $stmt->fetch()) {
        print_r($row);
    }
}

If the database driver supports it, you can also bind output parameters like input parameters. Output parameters are often used as return values ​​from stored procedures. Output parameters are slightly more complicated to use than input parameters. When using them, you must know the size of the parameter value it returns. If it returns a larger parameter value than you suggested, an error will occur.

//调用一个带有输出参数的存储过程
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); //执行存储过程
$stmt->execute();
print "procedure returned $return_value/n";

You can also specify parameters that represent both input and output. The syntax is similar to output parameters. In the next code example, the "hello" string is passed into the stored procedure, and when it returns, hello is replaced by the return value of the stored procedure.

//调用一个带有输入/输出参数的存储过程
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// 执行存储过程
$stmt->execute();
print "procedure returned $value/n";


//占位符的错误使用
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name'])); // 占位符必须用于整个值的位置（下面是正确的用法）
$stmt = $dbh->prepare(”SELECT * FROM REGISTRY where name LIKE ?”);
$stmt->execute(array(”%$_GET[name]%”));

If there is an error, please point it out. If you want to know more related issues, please visit the PHP Chinese website: PHP Video Tutorial,

I believe it is rich in content and explanations. Will give you a satisfactory answer.

The above is the detailed content of What are prepared statements? How to use it?. For more information, please follow other related articles on the PHP Chinese website!