如何使用 PDO 动态构建查询 [英] How to dynamically build queries with PDO

问题描述

我正在使用 PDO 并且想做这样的事情:

$query = $dbh->prepare("SELECT * FROM :table WHERE :column = :value");$query->bindParam(':table', $tableName);$query->bindParam(':column', $columnName);$query->bindParam(':value', $value);

PDO 会允许我像这样绑定表名和列名吗?似乎允许它，但即使我使用 PDO::PARAM_INT 或 PDO::PARAM_BOOL 作为数据类型，它也会在我的参数周围加上引号.

如果这不起作用，我怎样才能安全地转义我的变量以便我可以在查询中插入它们?

解决方案

很遗憾，您无法通过列名绑定参数.

您可以尝试动态创建 SQL 命令:

$sql = "SELECT * FROM $tableName WHERE $columnName = :value";$query = $dbh->prepare($sql);$query->bindParam(':value', $value);

只要确保对来自其他地方的参数/变量进行清理，以防止 SQL 注入.在这种情况下，$value 在一定程度上是安全的，但 $tableName 和 $columnName 不是 -- 再次，尤其是如果这些变量的值不是由 you 提供，而是由您的用户/访问者/等提供...

另外一件事；请避免使用 * 并命名您的列... 查看原因:

http://www.jasonvolpe.com/topics/sql/>

使用 SELECT * 时的性能问题?

在此处查看其他类似帖子:

为什么 ORDER BY 子句中的绑定参数不对结果进行排序?

如何设置 ORDER BY 参数使用准备好的 PDO 语句?

I am using PDO and want to do something like this:

$query = $dbh->prepare("SELECT * FROM :table WHERE :column = :value");
$query->bindParam(':table', $tableName);
$query->bindParam(':column', $columnName);
$query->bindParam(':value', $value);

Will PDO allow me to bind the table name and the column name like this? It seems to allow it, but it puts quotes around my parameters even if I use PDO::PARAM_INT or PDO::PARAM_BOOL as the data type.

If this won't work, how can I safely escape my variables so that I can interpolate them in the query?

解决方案

Unfortunately, you can't bind parameters by column names.

What you could try is to dynamically create your SQL command:

$sql = "SELECT * FROM $tableName WHERE $columnName = :value";
$query = $dbh->prepare($sql);
$query->bindParam(':value', $value);

Just make sure to sanitize your parameters/variables if they are coming from elsewhere, to prevent SQL Injection. In this case, $value is safe to a degree but $tableName and $columnName are not -- again, that is most especially if the values for these variables are not provided by you and instead by your users/vistors/etc...

One other thing; please avoid using * and name your columns instead... See some reasons why:

http://www.jasonvolpe.com/topics/sql/

Performance issue in using SELECT *?

See other similar posts here:

Why doesn't binding parameter in ORDER BY clause order the results?

How do I set ORDER BY params using prepared PDO statement?

这篇关于如何使用 PDO 动态构建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！