postgresql 如何在 Postgres 中使用准备好的语句

How to use prepared statements with Postgres

提问by Léo Léopold Hertz ??

I know that I need prepared statements because I make more than one call to my database during one script.

我知道我需要准备好的语句，因为我在一个脚本中多次调用我的数据库。

I would like to get concrete examples about the following sentence

我想得到关于以下句子的具体例子

Look at typecasting, validating and sanitizing variables and using PDO with prepared statements.

查看类型转换、验证和清理变量以及将 PDO 与准备好的语句一起使用。

I know what he mean by validating and sanitizing variables. However, I am not completely sure about prepared statements. How do we prepare statements? By filters, that is by sanitizing? Or by some PDO layer? What is the definition of the layer?

我知道他所说的验证和清理变量是什么意思。但是，我对准备好的语句并不完全确定。我们如何准备报表？通过过滤器，也就是通过消毒？还是通过某些 PDO 层？层的定义是什么？

What do prepared statements mean in the statement?Please, use concrete examples.

准备好的声明在声明中是什么意思？请用具体的例子。

采纳答案by karim79

What do prepared statements mean in the statement?

准备好的声明在声明中是什么意思？

From the documentation:

从文档：

This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed.

此功能允许将重复使用的命令仅解析和计划一次，而不是每次执行时。

See pg_prepare

见pg_prepare

Example from the page linked above:

来自上面链接页面的示例：

<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");

// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = ');

// Execute the prepared query.  Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

// Execute the same prepared query, this time with a different parameter
$result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));
?>

The MySQL documentation for Prepared Statementsnicely answers the following questions:

Prepared Statements的MySQL 文档很好地回答了以下问题：

• Why use prepared statements?
• When should you use prepared statements?

• 为什么要使用准备好的语句？
• 什么时候应该使用准备好的语句？

回答by Glass Robot

It means it will help you prevent SQL injection attacks by eliminating the need to manually quote the parameters.

这意味着它将通过消除手动引用参数的需要来帮助您防止 SQL 注入攻击。

Instead of placing a variable into the sql you use a named or question mark marker for which real values will be substituted when the statement is executed.

不是将变量放入 sql 中，而是使用命名或问号标记，在执行语句时将替换实际值。

Definition of PDOfrom the PHP manual:
'The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.'

PHP 手册中PDO 的定义：
“PHP 数据对象 (PDO) 扩展定义了一个轻量级、一致的接口，用于在 PHP 中访问数据库。”

See the php manual on PDOand PDO::prepare.

请参阅有关PDO和PDO::prepare的 php 手册。

An example of a prepared statement with named markers:

带有命名标记的准备语句示例：

<?php
$pdo = new PDO('pgsql:dbname=example;user=me;password=pass;host=localhost;port=5432');

$sql = "SELECT username, password
FROM users
WHERE username = :username
AND password = :pass";

$sth = $pdo->prepare($sql);
$sth->execute(array(':username' => $_POST['username'], ':pass' => $_POST['password']));
$result = $sth->fetchAll();

An example of a prepared statement with question mark markers:

带有问号标记的准备语句示例：

<?php
$pdo = new PDO('pgsql:dbname=example;user=me;password=pass;host=localhost;port=5432');

$sql = "SELECT username, password
FROM users
WHERE username = ?
AND password = ?";

$sth = $pdo->prepare($sql);
$sth->execute(array($_POST['username'], $_POST['password']));
$result = $sth->fetchAll();

回答by Léo Léopold Hertz ??

Reply to Karim79's answer

回复 Karim79 的回答

This

这个

$result = pg_prepare($dbconn, "query1", 'SELECT passhash_md5 FROM users WHERE email = ');

seems to be the same as this

好像和这个一样

$result = pg_prepare($dbconn, "query1", 'SELECT passhash_md5 FROM users WHERE email = ?');

---

---

Conclusion:the use of pg_prepareand pg_executemakes PHP much more efficient, since you do not need to consider sanitizing. It also helps you in the use of PDO.

结论：使用pg_prepare和pg_execute使 PHP 更加高效，因为您不需要考虑清理。它还可以帮助您使用 PDO。

回答by Rufinus

How do we prepare statements:

我们如何准备报表：

You define a query one time, and can called it as often as you like with different values. (eg. in a loop)

您可以一次定义一个查询，并且可以根据需要使用不同的值随时调用它。（例如，在一个循环中）

$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = ');
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));
$result = pg_execute($dbconn, "my_query", array("row two"));
$result = pg_execute($dbconn, "my_query", array("row three"));

see: http://us2.php.net/manual/en/function.pg-execute.php

见：http: //us2.php.net/manual/en/function.pg-execute.php