I did have this code working but it was not secure from SQL injection so I tried to update it. The user submit filter requirements which come from check boxes, I take them from post, and replace them with ?, I then implode, and bind them back together for the IN clause.

I feel like I am a couple of lines of code away from getting this. What am I doing wrong? I have echoed out after implode and it shows the right amount of ? corresponding to the selections. Is the problem in my execute statement?

if(isset($_POST['songgenre'])){
        $songgenre =  $_POST['songgenre'];
        $placeholderssonggenre = array_fill(1, count($songgenre), '?');
        $songgenrefilter = implode(',', $placeholderssonggenre);
                    
    }else{
        $songgenre ='';
        $genreempty = '';
    }
    
    


    $sql = "SELECT * FROM music_db WHERE songgenre IN ($songgenrefilter)";
    $stmt = $conn->prepare($sql);
    $res = $stmt->execute($songgenre);
    
    if ($res !== FALSE) {
    $results = $res->rowCount();
    echo($results);
    } else {
    echo "Code Failed";
    }

CodePudding user response：

I know really how frustrating it could be. Well, it seems that following things are missing there. Try it:

1. When you're using the IN clause, you need to pass an array of values to the execute() method, not a single variable. This means that you should be passing $songgenre directly to the execute() method, instead of using it to create the $placeholderssonggenre variable.
2. You should bind the parameters to the statement before executing it, not after. You can use the bindValue() method to bind the values to the placeholders in the query.
3. When you execute the statement, you should use the fetchAll() method to retrieve the rows, instead of rowCount().

Here's the correct code:

if(isset($_POST['songgenre'])){
    $songgenre =  $_POST['songgenre'];
    $placeholders = array_fill(0, count($songgenre), '?');
    $placeholders = implode(',', $placeholders);
} else {
    $songgenre ='';
    $placeholders = '';
}

$sql = "SELECT * FROM music_db WHERE songgenre IN ($placeholders)";
$stmt = $conn->prepare($sql);

foreach ($songgenre as $i => $value) {
    $stmt->bindValue($i   1, $value);
}

$stmt->execute();
$results = $stmt->fetchAll();

CodePudding user response：

Another option more elegant

if(isset($_POST['songgenre'])){

    $postfilter = [
        'songgenre' => ['filter' => FILTER_SANITIZE_STRING] // always sanitize
    ];
    $filter_post_array = filter_var_array($_POST, $postfilter);
    $filtered = $filter_post_array['songgenre'];

} else {
    $filtered = [];
}
$sql = "SELECT * FROM music_db WHERE songgenre IN (:songgenrefilter)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':songgenrefilter',implode(',',$filtered);
$stmt->execute();
$result = $stmt->fetchAll();