PHP PDO 语句可以接受表名或列名作为参数吗？

Can PHP PDO Statements accept the table or column name as parameter?

提问by Jrgns

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

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

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

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 查询中？有了安全，我的意思是我不想做

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

采纳答案by Noah Goodrich

Table and Column names CANNOT be replaced by parameters in PDO.

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

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:

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

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.

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

回答by IMSoP

To understand whybinding a table (or column) name doesn't work, you have to understand how the placeholders in prepared statements work: they are not simply substituted in as (suitably escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

要了解为什么绑定表（或列）名称不起作用，您必须了解准备语句中的占位符是如何工作的：它们不是简单地替换为（适当转义的）字符串，并且会执行生成的 SQL。相反，要求“准​​备”一条语句的 DBMS 会提出一个完整的查询计划，说明它将如何执行该查询，包括它将使用哪些表和索引，无论您如何填充占位符，这些计划都是相同的。

The plan for SELECT name FROM my_table WHERE id = :valuewill be the same whatever you substitute for :value, but the seemingly similar SELECT name FROM :table WHERE id = :valuecannot be planned, because the DBMS has no idea what table you're actually going to select from.

SELECT name FROM my_table WHERE id = :value无论您替换什么，其计划都将相同:value，但SELECT name FROM :table WHERE id = :value无法计划看似相似的计划，因为 DBMS 不知道您实际上要从哪个表中进行选择。

This is not something an abstraction library like PDO can or should work around, either, since it would defeat the 2 key purposes of prepared statements: 1) to allow the database to decide in advance how a query will be run, and use the same plan multiple times; and 2) to prevent security issues by separating the logic of the query from the variable input.

这也不是像 PDO 这样的抽象库可以或应该解决的问题，因为它会破坏准备语句的两个关键目的：1）允许数据库提前决定查询将如何运行，并使用相同的多次计划；和 2) 通过将查询逻辑与变量输入分离来防止安全问题。

回答by Don

I see this is an old post, but I found it useful and thought I'd share a solution similar to what @kzqai suggested:

我看到这是一篇旧帖子，但我发现它很有用，并认为我会分享一个类似于@kzqai 建议的解决方案：

I have a function that receives two parameters like...

我有一个接收两个参数的函数，例如...

function getTableInfo($inTableName, $inColumnName) {
    ....
}

Inside I check against arrays I've set up to make sure only tables and columns with "blessed" tables are accessible:

在里面，我检查我设置的数组，以确保只有带有“blessed”表的表和列可以访问：

$allowed_tables_array = array('tblTheTable');
$allowed_columns_array['tblTheTable'] = array('the_col_to_check');

Then the PHP check before running PDO looks like...

然后在运行 PDO 之前的 PHP 检查看起来像......

if(in_array($inTableName, $allowed_tables_array) && in_array($inColumnName,$allowed_columns_array[$inTableName]))
{
    $sql = "SELECT $inColumnName AS columnInfo
            FROM $inTableName";
    $stmt = $pdo->prepare($sql); 
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

回答by Adam Bellaire

Using the former isn't inherently more safe than the latter, you need to sanitize the input whether it's part of a parameter array or a simple variable. So I don't see anything wrong with using the latter form with $table, provided you make sure that the content of $tableis safe (alphanum plus underscores?) before using it.

使用前者本质上并不比后者更安全，您需要清理输入，无论它是参数数组的一部分还是简单变量。因此，我认为将后一种形式与 一起使用没有任何问题$table，前提是您$table在使用它之前确保 的内容是安全的（字母数字加下划线？）。

回答by Funk Forty Niner

(Late answer, consult my side note).

（迟到的答案，请参阅我的附注）。

The same rule applies when trying to create a "database".

尝试创建“数据库”时也适用相同的规则。

You cannot use a prepared statement to bind a database.

您不能使用准备好的语句来绑定数据库。

I.e.:

IE：

CREATE DATABASE IF NOT EXISTS :database

will not work. Use a safelist instead.

不管用。改用安全列表。

Side note:I added this answer (as a community wiki) because it often used to close questions with, where some people posted questions similar to this in trying to bind a databaseand not a table and/or column.

旁注：我添加了这个答案（作为社区维基），因为它经常用来结束问题，有些人在尝试绑定数据库而不是表和/或列时发布了与此类似的问题。

回答by Phil LaNasa

Part of me wonders if you could provide your own custom sanitizing function as simple as this:

我的一部分想知道您是否可以像这样简单地提供自己的自定义消毒功能：

$value = preg_replace('/[^a-zA-Z_]*/', '', $value);

I haven't really thought through it, but it seems like removing anything except characters and underscores might work.

我还没有真正考虑过它，但似乎删除除字符和下划线之外的任何内容都可能起作用。

回答by man

As for the main question in this thread, the other posts made it clear why we can't bind values to column names when preparing statements, so here is one solution:

至于这个线程中的主要问题，其他帖子清楚地说明了为什么我们在准备语句时不能将值绑定到列名，所以这是一个解决方案：

class myPdo{
    private $user   = 'dbuser';
    private $pass   = 'dbpass';
    private $host   = 'dbhost';
    private $db = 'dbname';
    private $pdo;
    private $dbInfo;
    public function __construct($type){
        $this->pdo = new PDO('mysql:host='.$this->host.';dbname='.$this->db.';charset=utf8',$this->user,$this->pass);
        if(isset($type)){
            //when class is called upon, it stores column names and column types from the table of you choice in $this->dbInfo;
            $stmt = "select distinct column_name,column_type from information_schema.columns where table_name='sometable';";
            $stmt = $this->pdo->prepare($stmt);//not really necessary since this stmt doesn't contain any dynamic values;
            $stmt->execute();
            $this->dbInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    }
    public function pdo_param($col){
        $param_type = PDO::PARAM_STR;
        foreach($this->dbInfo as $k => $arr){
            if($arr['column_name'] == $col){
                if(strstr($arr['column_type'],'int')){
                    $param_type = PDO::PARAM_INT;
                    break;
                }
            }
        }//for testing purposes i only used INT and VARCHAR column types. Adjust to your needs...
        return $param_type;
    }
    public function columnIsAllowed($col){
        $colisAllowed = false;
        foreach($this->dbInfo as $k => $arr){
            if($arr['column_name'] === $col){
                $colisAllowed = true;
                break;
            }
        }
        return $colisAllowed;
    }
    public function q($data){
        //$data is received by post as a JSON object and looks like this
        //{"data":{"column_a":"value","column_b":"value","column_c":"value"},"get":"column_x"}
        $data = json_decode($data,TRUE);
        $continue = true;
        foreach($data['data'] as $column_name => $value){
            if(!$this->columnIsAllowed($column_name)){
                 $continue = false;
                 //means that someone possibly messed with the post and tried to get data from a column that does not exist in the current table, or the column name is a sql injection string and so on...
                 break;
             }
        }
        //since $data['get'] is also a column, check if its allowed as well
        if(isset($data['get']) && !$this->columnIsAllowed($data['get'])){
             $continue = false;
        }
        if(!$continue){
            exit('possible injection attempt');
        }
        //continue with the rest of the func, as you normally would
        $stmt = "SELECT DISTINCT ".$data['get']." from sometable WHERE ";
        foreach($data['data'] as $k => $v){
            $stmt .= $k.' LIKE :'.$k.'_val AND ';
        }
        $stmt = substr($stmt,0,-5)." order by ".$data['get'];
        //$stmt should look like this
        //SELECT DISTINCT column_x from sometable WHERE column_a LIKE :column_a_val AND column_b LIKE :column_b_val AND column_c LIKE :column_c_val order by column_x
        $stmt = $this->pdo->prepare($stmt);
        //obviously now i have to bindValue()
        foreach($data['data'] as $k => $v){
            $stmt->bindValue(':'.$k.'_val','%'.$v.'%',$this->pdo_param($k));
            //setting PDO::PARAM... type based on column_type from $this->dbInfo
        }
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);//or whatever
    }
}
$pdo = new myPdo('anything');//anything so that isset() evaluates to TRUE.
var_dump($pdo->q($some_json_object_as_described_above));

The above is just an example, so needless to say, copy->paste won't work. Adjust for your needs. Now this may not provide 100% security, but it allows some control over the column names when they "come in" as dynamic strings and may be changed on users end. Furthermore, there is no need to build some array with your table column names and types since they are extracted from the information_schema.

上面只是一个例子，不用说，copy->paste是行不通的。根据您的需要进行调整。现在这可能无法提供 100% 的安全性，但是当它们作为动态字符串“进入”并且可能在用户端更改时，它允许对列名称进行一些控制。此外，没有必要使用您的表列名和类型构建一些数组，因为它们是从 information_schema 中提取的。