PHP PDO 语句可以接受表名或列名作为参数吗? [英] Can PHP PDO Statements accept the table or column name as parameter?

问题描述

为什么我不能将表名传递给准备好的 PDO 语句?

Why can't I pass the table name to a prepared PDO statement?

$stmt = $dbh->prepare('SELECT * FROM :table WHERE 1');
if ($stmt->execute(array(':table' => 'users'))) {
    var_dump($stmt->fetchAll());
}

是否有另一种安全的方法将表名插入到 SQL 查询中?有了安全，我的意思是我不想做

Is there another safe way to insert a table name into a SQL query? With safe, I mean that I don't want to do

$sql = "SELECT * FROM $table WHERE 1"

推荐答案

表名和列名不能被 PDO 中的参数替换.

在这种情况下，您只需手动过滤和清理数据.一种方法是将速记参数传递给将动态执行查询的函数，然后使用 switch() 语句创建用于表名的有效值白名单或列名.这样，用户输入就不会直接进入查询.例如:

In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch() statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:

function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
            $tbl = 'users';
            break;
    }

    $sql = "SELECT * FROM $tbl";
}

通过不保留默认值或使用返回错误消息的默认值，您可以确保仅使用您想要使用的值.

By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.

这篇关于PHP PDO 语句可以接受表名或列名作为参数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！