如何使用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屋！