How to use php array in a Prepared Statement for SQL IN Operator using SQLi?

Question

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.

[*PHP PDO: Can I bind an array to an IN() condition*](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — M Khalid Junaid, Jan 05 '14 at 19:35
thanks for the link. Is there a SQLi method for it? I havent used PDOs and all of my scripts atm are using sqli method. — Neel, Jan 05 '14 at 19:37

nice ass · Accepted Answer · 2014-01-05T20:29:37.837

2

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 :)

edited Jan 05 '14 at 20:29

answered Jan 05 '14 at 19:42

nice ass

16,471
7
50
89

I like your approach. But when I try this I am getting the error `mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement` – Neel Jan 05 '14 at 20:08
It should work now. I overestimated `mysqli_stmt_bind_param` :) – nice ass Jan 05 '14 at 20:19
sorry..tried the updated code but it shows this error `mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, string given`. This error is referring to this line: `call_user_func_array('mysqli_stmt_bind_param', $params);` – Neel Jan 05 '14 at 20:27
I think it works now. The last edit with `str_repeat()` did it. After executing the above prepared statement, when I bind the results like this: `$stm->bind_result($col1, $col2); while ($stm->fetch()) { $id = $col1; $name = $col2; } $stmt->close();`, it is only printing the first row of id and name and not the full list. Am I missing something with the `bind_result` statement that follows the `mysqli_stmt_execute`? – Neel Jan 05 '14 at 20:44
Try the procedural version: `mysqli_stmt_bind_result($stm, $col1, $col2)` – nice ass Jan 05 '14 at 21:02
I changed it to the procedural version like this and when I echo the array, its only printing the last row (sorry not the first row like I mentioned in my last comment): `mysqli_stmt_bind_result($stmt, $col); while (mysqli_stmt_fetch($stmt)) { $name[] = $col1; } foreach($name as $v) { echo $v; }`. I am really sorry if I am being a pain. :( – Neel Jan 05 '14 at 21:11
Aha.. would you be able to give an example please if I want to store the names inside an array so I can loop again later? – Neel Jan 05 '14 at 21:21
When I added `echo $name;` inside the while loop, it is also printing only the last one. So I dont understand where I am doing it wrong. – Neel Jan 05 '14 at 21:23
You should edit the question and post the code in it – nice ass Jan 05 '14 at 21:25
I've added my code in my question. :) – Neel Jan 05 '14 at 21:41
Hi onetrickpony.. I have made the swtich to PDO after learning about that today and it was totally worth it. Like you suggested, I was able to achieve what I wanted but very little number of lines of codes. Thank you so much for your help. I have updated my question above on this too... I have accepted your answer since your method of using arrays in mysqli prepared statement is a good solution. Thanks again for your support! :) – Neel Jan 06 '14 at 16:23

How to use php array in a Prepared Statement for SQL IN Operator using SQLi?

1 Answers1