MySQLi prepared statements with IN operator

Question 1

I have to select some rows from the database using IN operator. I want to do it using prepared statement. This is my code:

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$in_statement = '"' . implode('", "', $lastnames) . '"'; //"braun", "piorkowski", "mason", "nash"

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN (?)');
$data_res->bind_param('s', $in_statement);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}
?>

But returns nothing although all data exists in the database.

And one more: if i pass $in_statement directly to query and execute it, the data will be returned. So the problem appears on preparing.

I was looking for the question in Google but it wasn't' successful. What's wrong with my code?
Thanks for the help!

Question 2

I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();

foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int); 

$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}

$result->free();
$data_res->close();
?>

Question 3

Yes, you would have to bind it twice. If you are opposed to that for some reason, you could rephrase the query as:

SELECT *
FROM `login` l cross join
      (select ? as thename) const
WHERE l.`username` = thename OR `emailAddress` = thename;

This is using a subquery to name the parameter so it can be referred to multiple times in the query.

Question 4

A slight variation on Your Common Sense's answer, and something I didn't realise you could do (although it makes sense in a way)...

$cat=1;
$lastNames = $ids;

$count_params = count($lastNames);
$int = str_repeat('i',$count_params+1);
$q = array_fill(0,$count_params,'?');
$params = implode(',',$q);
$qry1=$dblink->prepare("SELECT * FROM course_details WHERE category=? and cat_id IN ( $params )");

$qry1->bind_param( $int, $cat, ...$lastNames);
$qry1->execute();
$qry1_res=$qry1->get_result();
while($rowset1=$qry1_res->fetch_array()){
        print_r($rowset1);
}

The only real different is the call to bind_param, rather than merging the item into the array, just list it as another parameter before using the array fill (...) from PHP 5.6+.

Update: From the comment, the version of PHP doesn't support splat :(... so going back to original...

$cat=1;
$lastnames = $ids;
$arParams = array();

array_unshift($lastnames,$cat);
foreach($lastnames as $key => $value)   {
    $arParams[] = &$lastnames[$key];
}

$count_params = count($arParams);
$int = str_repeat('i',$count_params);
array_unshift($arParams,$int);
$q = array_fill(0,$count_params-1,'?');
$params = implode(',',$q);

$qry1=$dblink->prepare("SELECT * FROM course_details WHERE category=? and cat_id IN ( $params )");
call_user_func_array(array($qry1, 'bind_param'), $arParams);
$qry1->execute();
$qry1_res=$qry1->get_result();

while($rowset1=$qry1_res->fetch_array()){
    print_r($rowset1);
}

This adds the category into the list of items, but note the array_fill() uses count-1 as the ? for the cat is already there.

Question 5

You should use

mysqli_insert_id($link);

Because of this note on the PHP manual you referred us to

mysqli_stmt_insert_id

It should be noted that using mysqli_stmt->insert_id will not result in a unique ID being returned for each execution of a prepared insert statement. In practice, it appears that the first insertion ID is returned. If you are performing multiple inserts with the same prepared statement (one invocation of mysqli_stmt::prepare and multiple invocations of mysqli_stmt::execute() for a given statement), and need to keep the unique ID for each insert, use mysqli_connection->insert_id.

Question 6

That's ok, no any kind of SQL injection here, you do not see list of all products (at least code you provided cannot show it)

you do not need to cast to int, lets go deeper what bind_param actually do:

it has string "i" which means 1 integer argument

you're passing value from $_GET which is string

bind_param tries to convert String to int, so check this php code:

echo intval('a', 10); // output 0
echo intval('1a', 10); // output 1
echo intval('12a', 10); // output 12
echo intval('b1', 10); // output 0
echo intval('1 or 1=1', 10); // output 1
echo intval("?s=1 OR 1=1", 10); // output 0

so, you output products with id=1, maybe you have some of them?

MySQLi prepared statements with IN operator

Answers

php,sql,mysqli,prepared-statement