具有动态参数的动态选择mysqli查询返回错误与绑定变量数不匹配 [英] Dynamic select mysqli query with dynamic parameters returns error doesn't match number of bind variables

问题描述

我正在尝试使用动态where子句和动态参数创建一个选择查询，但是我总是遇到错误:

I'm trying to create a select query with dynamic where clause and dynamic parameters but I always get error :

警告:mysqli_stmt :: bind_param():类型中的元素数 定义字符串与绑定变量的数量不匹配

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

我真诚地不理解，因为似乎计数还可以.因此，这就是原始代码的样子.我看不到我在做什么错.

Which I sincerely do not understand since it seems the count is alright. So this is what the code really looks like in its rude format. I can't see what I'm doing wrong.

//get variables
$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia = 'House';

//convert string to array
$socialArray = explode(',', $mediaArray)

//declare some variables to be used later
$andwhere = '';
$bp = '';
$socialmarray = ''

 //get every value from array of social media
foreach($socialArray as $socialmedia){

    $socialmarray .=$socialmedia.',';
    $andwhere .= " AND socialmedianame=?";
    $bp .='s';
}

//test strings
echo $wheres = $andwhere;//AND socialmedianame=? AND socialmedianame=? AND socialmedianame=?
echo $bip = $bp.'s';//ssss
echo $validarayy = rtrim($socialmarray,',');//Facebook,Twitter,Twitch

//select query
$selectquery = $conn->prepare("select * from mediaservices where socialmedianame=? $wheres");
$selectquery->bind_param("$bip",$otherMedia,$validarayy);
$selectquery->execute();
$resultquery = $selectquery->get_result();

推荐答案

原因:

1. 您使用的是用户提供的数据，必须假定您的查询容易受到恶意注入攻击
2. 要构建到查询中的数据量是可变/不确定的
3. 您仅在单个表列上写条件检查

您应该使用准备好的语句，并将所有WHERE子句逻辑合并到单个IN语句中.

You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.

(在语法上)构建动态准备语句比使用pdo更为复杂，但这并不意味着您仅由于此任务就需要放弃mysqli.

Building this dynamic prepared statement is more convoluted (in terms of syntax) than using pdo, but it doesn't mean that you need to abandon mysqli simply because of this task.

我已经遍历所有可能的错误检查点，以便您可以轻松地调试是否出现意外情况.我必须强调，作为最佳安全实践，您绝不能向公众展示错误的详细信息.

I have baked in every possible error check point so that you can easily debug if something unexpected arises. I must stress that you must never show error details to the public as a matter of best security practices.

我已经在本地主机上使用自己的db数据测试了该解决方案，这是一个可行的解决方案.

I have tested this solution with my own db data on my localhost to be a working solution.

$_POST['userMedia'] ='Facebook,Twitter,Twitch,';
$media = explode(',', rtrim($_POST['userMedia'], ','));  // build array from csv string
$presetMedia = 'House';
$media[] = $presetMedia;                                 // push known media string into array
$media = array_unique($media);                           // make sure there are no duplicates
var_export($media);                                       // see what this generates


if (!$count = count($media)) {
    echo "There are no values in media, so a prepared statement is overkill and IN won't work when empty";
    // handle this case however you wish (if it is even possible within your project)
} elseif (!$conn = new mysqli("localhost", "root", "", "myDB")) {  // use your own credentials
    echo "Database Connection Error: " , $conn->connect_error;
} else {
    $csph = implode(',', array_fill(0, $count, '?'));        // comma-separated placeholders e.g: ?,?,?,?
    echo "<div>Placeholders: $csph</div>";
    $query = "SELECT * FROM `mediaservices` WHERE `socialmedianame` IN ($csph)";
    echo "<div>Query: $query</div>";
    if (!$stmt = $conn->prepare($query)) {
        echo "<div>Syntax Error @ prepare: {$conn->error}</div>";      // NEVER show error details to the public
    }else{
        array_unshift($media, str_repeat('s', $count));      // prepend the type values string e.g: ssss
        var_export($media);                                   // see what this generates
        foreach ($media as &$v) {
            $ref[] = &$v;                                    // call_user_func_array requires array that is passed-by-reference
        }
        call_user_func_array([$stmt, 'bind_param'], $ref);   // call bind_param() upon stmt object, using each media value  

        if (!$stmt->execute() || !$result = $stmt->get_result()) { // execute statement and get the resultset while checking for falsey returned feedback
            echo "<div>Error @ execute/get_result: {$stmt->error}</div>";  // NEVER show error details to the public
        } elseif (!$result->num_rows) {                      // check for empty rows, if you wish
            echo "<div>No Rows Found</div>";
        } else {
            echo "<pre>";
            while ($row = $result->fetch_assoc()) {
                var_export($row);                            // do what you like with the associative-keyed elements
                echo "<br>";
            }
            echo "</pre>";
        }
        $stmt->close();
    }
}

输出应类似于:

array ( 0 => 'Facebook', 1 => 'Twitter', 2 => 'Twitch', 3 => 'House' )
Placeholders: ?,?,?,?
Query: SELECT * FROM `mediaservices` WHERE `socialmedianame` IN (?,?,?,?);
array ( 0 => 'ssss', 1 => 'Facebook', 2 => 'Twitter', 3 => 'Twitch', 4 => 'House', )
array (
    // whatever column names and values you have in the row
)
array (
    // whatever column names and values you have in the row
)
array (
    // whatever column names and values you have in the row
)
...

这篇关于具有动态参数的动态选择mysqli查询返回错误与绑定变量数不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！