简单的搜索功能，MySQL准备语句问题

Question

我目前正在为一个网站创建一个非常简单的搜索特性，在该特性中，用户可以使用许多不同的条件（从一个到多个，不同的数量）在数据库中搜索事件，我正在使用准备好的语句，特别是bind_param()遇到一个问题。

以下是相关的PHP代码：

...
...
$title = (empty($_POST['eventTitle'])) ? null : $_POST['eventTitle'];
$venue = (empty($_POST['venue'])) ? null : $_POST['venue'];
$catID = (empty($_POST['catID'])) ? null : $_POST['catID'];
$start = (empty($_POST['start'])) ? null : $_POST['start'];
$end = (empty($_POST['end'])) ? null : $_POST['end'];
$price = (empty($_POST['price'])) ? null : $_POST['price'];

include 'database_conn.php';

$sql = 'SELECT eventID, eventTitle, venueID, catID, eventStartDate, 
eventEndDate, eventPrice FROM te_events WHERE 1';

$sqlCondition = '';

$bindFirstArg = '"';
$bindSecondArg = '';

if($title !== null && !empty($title)) {

    $sqlCondition = $sqlCondition . " AND eventTitle LIKE \"%" 
    . $title . "%\"";

}

if($venue !== null && $venue !== '0') {

    $sqlCondition = $sqlCondition . " AND venueID=?";
    $bindFirstArg = $bindFirstArg . "s";
    $bindSecondArg = $bindSecondArg . ", " . $venue;

}

if($catID !== null && $catID !== '0') {

    $sqlCondition = $sqlCondition . " AND catID=?";
    $bindFirstArg = $bindFirstArg . "s";
    $bindSecondArg = $bindSecondArg . ", " . $catID;

}

if($start !== null && $start !== '0') {

    $sqlCondition = $sqlCondition . " AND eventStartDate=?";
    $bindFirstArg = $bindFirstArg . "s";
    $bindSecondArg = $bindSecondArg . ", " . $start;

}

if($end !== null && $end !== '0') {

    $sqlCondition = $sqlCondition . " AND eventEndDate=?";
    $bindFirstArg = $bindFirstArg . "s";
    $bindSecondArg = $bindSecondArg . ", " . $end;

}

if($price !== null && !empty($price)) {

    $sqlCondition = $sqlCondition . " AND eventPrice=?";
    $bindFirstArg = $bindFirstArg . "i";
    $bindSecondArg = $bindSecondArg . ", " . $price;

}

$sql = $sql . $sqlCondition;
$bindFirstArg = $bindFirstArg . '"';

$search_stmt = $conn -> prepare($sql);

if (false===$search_stmt) {

    die('prepare() failed: ' . htmlspecialchars($conn->error));

}


$search_stmt -> bind_param($bindFirstArg, $bindSecondArg);
$search_stmt -> execute();
$search_stmt -> bind_result($eventIDRes, $eventTitleRes, $venueIDRes, 
$catIDRes, $eventStartRes, $eventEndRes, $eventPriceRes);

while ($search_stmt->fetch()) {

    printf ("%s %s %s %s %s %s %i\n", $eventIDRes, $eventTitleRes, 
    $venueIDRes, $catIDRes, $eventStartRes, $eventEndRes, $eventPriceRes);

}

mysqli_stmt_close($search_stmt);

我收到的错误状态

Answer 1

您需要为查询中的每个？传递bind_param单独的参数，并将格式作为第一个参数。不能给它传递逗号分隔的字符串，那样不行。它只是将其作为第一个？读取，然后抱怨您没有发送其余部分。

另外，不要在$bindfirstarg字符串中添加引号。bind_param只需要所有数据类型的列表(I、D、S或B)，而不需要“字符。

您需要做的是将值推入数组，然后通过call_user_func_array调用bind_param。

$sqlCondition = '';

$bindFirstArg = '';
$bindParams = array();


// You need to bind $title as well, otherwise you are wide open to SQL
// injection and have just thrown out the benefits of prepared statements
if($title !== null && !empty($title)) {
    $sqlCondition .= " AND eventTitle LIKE ?";
    $bindFirstArg .= "s";

    // Add the `%` to the value, not the query
    $title = "%{$title}%";
   // bind_param wants these to be references
    $bindParams[] =& $title;

}

// Change all your ifs to look like this.
// They need to push into the $bindParams array
if($catID !== null && $catID !== '0') {
    $sqlCondition .= " AND catID=?";    
    $bindFirstArg .= "s";

    // bind_param wants these to be references
    $bindParams[] =& $catID;
}

// etc...

$sql .= $sqlCondition;
$search_stmt = $conn->prepare($sql);

// Call bind_param with the correct number of parameters
array_unshift($bindParams, $bindFirstArg);
// This will make sure the parameters are passed correctly.
// Each variable needs to be passed as a separate parameter
call_user_func_array(array($search_stmt, 'bind_param'), $bindParams);

$search_stmt->execute();
$search_stmt->bind_result($eventIDRes, $eventTitleRes, $venueIDRes, 
    $catIDRes, $eventStartRes, $eventEndRes, $eventPriceRes);

while ($search_stmt->fetch()) {
    printf ("%s %s %s %s %s %s %i\n", $eventIDRes, $eventTitleRes, 
        $venueIDRes, $catIDRes, $eventStartRes, $eventEndRes, $eventPriceRes);
}

$search_stmt->close();