如何在使用SQLi的SQL IN运算符的Prepared语句中使用php数组？ [重复]

Question

This question already has an answer here:

• Can I bind an array to an IN() condition? 21 answers

This is my code:

if(isset($_POST['abc'])) 
{   
    $things['abc'] =  mysqli_real_escape_string($connect, implode("','", $_POST['abc']));

    $result = mysqli_query($connect, "SELECT * FROM this_list WHERE abc_column IN ('{$things['abc']}')");

    if (!$result)  
    {       
        echo "Error fetching results: " . mysqli_error(); 

    }
    else
    {
        while ($row = mysqli_fetch_array($result))  
        {
           $abc[] = $row['description'];  
        }
    }
}

The above code uses mysqli_real_escape_string(), and $things is an array with checkbox values that is received via POST. This array contains the list of strings separated by comma that I am using in the query.

When I was searching on the net, I noticed that some people say mysqli_real_escape_string() may prevent sql injection, I was thinking maybe prepared statement for checkbox values might be more safer against sql injection.

I have used prepared statement with separate parameters to prevent sql injection. But I am stuck on this one and I dont know how to change the above code to a prepare() statement since it uses an array $things['abc']. I tried searching and everytime I search array in prepared statement, I am getting info on Java, etc.. Can someone enlighten me on how I can do this with php please?

EDIT:

After the help from onetrickpony code below, this is what I have now:

if(isset($_POST['abc'])) 
    {   
        $ph = rtrim(str_repeat('?,', count($_POST['abc'])), ',');
        $query = sprintf("SELECT col1 FROM abc_table WHERE col2 IN (%s)", $ph); 

        $stmt = mysqli_prepare($connect, $query);

        // bind variables 
        $params = array();
        foreach($_POST['abc'] as $v)
          $params[] = &$v;

        array_unshift($params, $stmt, str_repeat('s', count($_POST['abc'])));  // s = string type
        call_user_func_array('mysqli_stmt_bind_param', $params);

        mysqli_stmt_execute($stmt);

        // Get the data result from the query. 
        mysqli_stmt_bind_result($stmt, $col1);

        /* fetch values and store them to each variables */
        while (mysqli_stmt_fetch($stmt)) {
           $name[] = $col1;
           echo $name;         
        }

        //loop to echo and see whats stored in the array above
        foreach($name as $v) {  
               echo $v;
        }


        // Close the prepared statement.
        $stmt->close();

    }

In the above code, the sqli method for prepare statement seems to work which is great. However, when I use the mysqli_stmt_bind_result(), the $name[] array inside the while loop only seems to print the last row.

UPDATE:

onetrickpony's code with the mysqli method for using php array in a Prepared Statement worked fine and it was a very good approach he had suggested. However, I have been having nightmare with the second half of the code which is trying to get the fetched array results to work. After trying for more than a day, I have given up on that and I have made the switch to PDO. Again onetrickpony's advise below was totally worth it. Making the switch to PDO made the code so much easier and simpler and couldnt believe it.

</div>

Answer 1

Try this:

// build placeholder string (?,?...)
$ph = rtrim(str_repeat('?,', count($_POST['abc'])), ',');
$query = sprintf("SELECT * FROM this_list WHERE abc_column IN (%s)", $ph);

$stm = mysqli_prepare($connect, $query);

// bind variables (see my notes below)
$params = array();
foreach($_POST['abc'] as $v)
  $params[] = &$v;

                                      // s = string type
array_unshift($params, $stm, str_repeat('s', count($_POST['abc'])));  
call_user_func_array('mysqli_stmt_bind_param', $params);

mysqli_stmt_execute($stm);

It appears that mysqli_stmt_bind_param cannot be called multiple times to bind multiple variables. And even worse, it requires referenced variables. I'd recommend you switch to PDO, just because of these limitations that force you to write ugly code :)