PDO bindParam 问题

2021-12-26 00:00:00 php mysql pdo

可能的重复:
PHP PDO 语句可以接受表名作为参数吗?

我的班级中有一个函数遇到了一些麻烦.这里的功能

I have a function in my class which is doing some trouble. Here the function

function insert($table,$column = array(),$value = array())
{
    $array1 = implode(",", $column);
    $array2 = implode(",", $value);

    try 
    { 
        $sql = $this->connect->prepare("INSERT INTO :table (:date1) VALUES (:date2)");  
        $sql->bindParam(':table',$table, PDO::PARAM_STR);
        $sql->bindParam(':data1',$array1, PDO::PARAM_STR);
        $sql->bindParam(':data2',$array2, PDO::PARAM_STR);

        $sql->execute();

    }  
    catch(PDOException $e) 
    {  
        echo $e->getMessage();  
    }  
}

我调用函数:

-> insert('coupons',array('categorie','name','link','code','id'),array('test11','test','test','test','NULL'));

我得到的错误是:

警告:PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: 无效的参数号:参数未在 C:xampphtdocsMYFRAMEWORKlibdatabase.class.php 中定义在第 46 行

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:xampphtdocsMYFRAMEWORKlibdatabase.class.php on line 46

第 46 行是:

$sql->execute();

所以现在我真的不明白问题出在哪里.有什么指点吗?

So now I don't really see where the issue is. Any pointers?

推荐答案

PDO 绑定值数据，而不是表名和列名.

您误解了绑定的使用.您不能使用 PDO 绑定表名和列名.您绑定数据以插入 INTO 这些列.您需要使用字符串操作构造 SQL 以包含表名和列.

PDOs bind value data, not table and column names.

You are misunderstanding the use of bindings. You cannot bind table and column names with PDO. You bind data to insert INTO those columns. You need to construct the SQL to include the table names and columns using string operations.

我已将您的 $column 和 $value 重命名为 $column_array, $value_array 以说明它们是什么，并假设每个都是一个简单的数组:$column_array = array('column1', 'column2', ...) 等

I've renamed your $column and $value to $column_array, $value_array to make it clear what they are, and assumed that each is a simple array: $column_array = array('column1', 'column2', ...) etc.

$placeholders = array_map(function($col) { return ":$col"; }, $column_array);

$bindvalues = array_combine($placeholders , $value_array);

$placeholders 现在看起来像这样:

$placeholders now looks like this:

$placeholders = array(
        ':column1',
        ':column2',
         ...
    );

$bindvalues 现在看起来像这样:

$bindvalues now looks like this:

$bindvalues = array(
        ':column1'=>'value1',
        ':column2'=>'value2',
         ...
    );

构建、准备、执行

$sql = $this->connect->prepare("INSERT INTO $table (" .implode(",", $column_array) .") VALUES (". implode(",", $placeholders) . ")";

这将为您提供一份准备好的声明:

This will give you a prepared statement of the form:

$sql = INSERT INTO table_name (column1, column2, ...) VALUES (:column1, :column2, ...)

然后您可以执行准备好的语句并将 $values 作为参数传递.

You can then execute the prepared statement and pass the $values as an argument.

$sql->execute($bindValues);

注意:

  • 必须提到的一个警告.确保您的原始数据已针对 SQL 注入进行了清理. PDO 会处理绑定值的问题，但是如果您从 $_POST 数据构建列，这很容易受到攻击，需要进行消毒.

    • Note:

    • One caveat that must be mentioned. Make sure that your original data has been sanitized against SQL Injection. PDO's take care of that for the bound values, but if you are constructing the columns from, say, $_POST data this is vulnerable and needs to be sanitized.

相关文章