添加bind_parameter以防止SQL注入(inject)后，Javascript和PHP代码不再起作用

Question

我创建了一个代码，使用 JavaScript 和 PHP 中的 ajax 和 Json 从数据库检索数据。最后，下拉列表中将填充查询中的数据。它工作得很好，直到我添加了 bind_parameter 函数来防止 SQL 注入(inject)。知道我在这里做错了什么吗？

JavaScript:

function getCompetitie()
{
   seizoen = $("#Seizoen-text").val();
$.ajax({
    type:'POST',
    url:'get_competitie.inc.php',
    dataType: 'json', 
    data: {seizoen: seizoen},
    success: function(response){
        $("#Competitie-list").empty();
        $("#Competitie-list").append("<option>Competitie</option>");
        var len = response.length;
        alert(len);
        for(var i = 0; i < len; i++){
            var comp = response[i]['Competitie'];
            $("#Competitie-list").append("<option value='"+comp+"'>"+comp+"</option>");
        }
    }
});
}

没有绑定(bind)参数的 PHP 代码(工作正常):

<?php
include "includes/dbh.inc.php";

$sql = "SELECT DISTINCT Competitie FROM kalender WHERE Seizoen='".$_POST['seizoen']."' ORDER BY Seizoen DESC;";    

$result = mysqli_query($conn, $sql);

$result_array = array();

while($row = mysqli_fetch_array($result)){
    $competitie = $row['Competitie'];
    $result_array[] = array("Competitie"=>$competitie);
}
echo json_encode($result_array);

?>

带有绑定(bind)参数的 PHP 代码(不起作用):

<?php

include "includes/dbh.inc.php";
$seiz= $_POST['seizoen'];
if (empty($seiz)) {
    exit ();
}
else {
    $sql = "SELECT DISTINCT Competitie FROM kalender WHERE Seizoen=? ORDER BY Seizoen DESC;";    
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt,$sql)) {
        exit ();
    }
    else {
        mysqli_stmt_bind_param($stmt, "s", $seiz);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        if ($row = mysqli_fetch_assoc($result)) {
            $result_array = array();
            while($row = mysqli_fetch_assoc($result)){
                $result_array = array();
                $seizoen = $row['Seizoen'];
                $result_array[] = array("Seizoen"=>$seizoen);
            }
            mysqli_stmt_close($stmt);
            echo json_encode($result_array);
        }
        else {
            exit ();                    
        }
    }
}

?>

我似乎得到的只是下拉框中的“未定义”。有人建议我哪里出错了吗？提前致谢!

Answer 1

您正在 $sql = "SELECT DISTINCT Competitie..

中选择列 Competitie

所以，解决这个问题:

//if ($row = mysqli_fetch_assoc($result)) { //remove this line
$result_array = array();
while($row = mysqli_fetch_assoc($result)){
    $competitie = $row['Competitie'];
    $result_array[] = array("Competitie"=>$competitie);
}
mysqli_stmt_close($stmt);
echo json_encode($result_array);

或者也选择 Seizoen 列。