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屋！