PHP/MySQL: Using array elements in WHERE clause using prepared statements

Question

I want to make a "dynamic" WHERE clause in my query based on a array of strings. And I want to run the created query using Mysqi's prepared statements.

My code so far, PHP:

$searchArray = explode(' ', $search);
$searchNumber = count($searchArray);
$searchStr = "tags.tag LIKE ? ";
for($i=1; $i<=$searchNumber-1 ;$i++){
    $searchStr .= "OR tags.tag LIKE ? ";
}

My query:

SELECT tag FROM tags WHERE $searchStr;

More PHP:

$stmt -> bind_param(str_repeat('s', count($searchArray)));

Now this obviously gives me an error since the bind_param part only contains half the details it need.

How should I proceed?

Are there any other (better) way of doing this?

Is it secure?

Can't you create an array as the secondary argument and call bind_param with that? — tadman
– tadman, Commented Jan 10, 2013 at 22:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Regarding the security part of the question, prepared statements with placeholders are as secure as the validation mechanism involved in filling these placeholders with values up. In the case of mysqli prepared statements, the documentation says:

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. It's not allowed to compare marker with NULL by ? IS NULL too. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

This clearly excludes any possibility of modifying the general semantic of the query, which makes it much harder (but not impossible) to divert it from its original intent.

Regarding the dynamic part of your query, you could use str_repeat in the query condition building part, instead of doing a loop:

 $searchStr = 'WHERE tags.tag LIKE ?' . 
                        str_repeat($searchNumber - 1, ' OR tags.tag LIKE ?');

For the bind_param call, you should use call_user_func_array like so:

$bindArray[0] = str_repeat('s', $searchNumber);
array_walk($searchArray,function($k,&$v) use (&$bindArray) {$bindArray[] = &$v;});
call_user_func_array(array($stmt,'bind_param'), $bindArray);

Hopefully the above snippet should bind every value of the $bindArray with its corresponding placeholder in the query.

Addenum:

However, you should be wary of two things:

call_user_func_array expects an integer indexed array for its second parameter. I am not sure how it would behave with a dictionary.
mysqli_stmt_bind_param requires its parameters to be passed by reference.

For the first point, you only need to make sure that $bindArray uses integer indices, which is the case in the code above (or alternatively check that call_user_func_array doesn't choke on the array you're providing it).

For the second point, it will only be a problem if you intend to modify the data within $bindArray after calling bind_param (ie. through the call_user_func_array function), and before executing the query. If you wish to do so - for instance by running the same query several times with different parameters' values in the same script, then you will have to use the same array ( $bindArray) for the following query execution, and update the array entries using the same keys. Copying another array over won't work, unless done by hand:

foreach($bindArray as $k => $v)
    $bindArray[$k] = some_new_value();

or

foreach($bindArray as &$v)
    $v = some_new_value();

The above would work because it would not break the references on the array entries that bind_param bound with the statement when it was called earlier. Likewise, the following should work because it does not change the references which have been set earlier up.

array_walk($bindArray, function($k,&$v){$v = some_new_value();});

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 11, 2013 at 0:21

didierc

14.8k3 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oskar Persson Over a year ago

Tested

array_unshift($searchArray, str_repeat('s', $searchNumber)); 							call_user_func_array(array($stmt, 'bind_param'), $searchArray);

Though I get this error and I don't get how to fix it: Error message: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given

Oskar Persson Over a year ago

Tested

$bindArray[0] = str_repeat('s', $searchNumber); 							array_walk($searchArray,function($k,&$v){$bindArray[] = &$v;}); 							call_user_func_array(array($stmt,'bind_param'), $bindArray);

And it gives this error Error message: Wrong parameter count for mysqli_stmt::bind_param()

didierc Over a year ago

glad you found your own answer.

Konrad Neuwirth · Accepted Answer · 2013-01-10 22:37:30Z

1

A prepared statement needs to have a well-defined number of arguments; it can't have any element of dynamic functionality. That means you'll have to generate the specific statement that you need and prepare just that.

What you can do – in case your code actually gets called multiple times during the existence of the database connection - is make cache of those prepared statements, and index them by the number of arguments that you're taking. This would mean that the second time you call the function with three arguments, you already have the statement done. But as prepared statements don't survive the disconnect anyway, this really only makes sense if you do multiple queries in the same script run. (I'm deliberately leaving out persistent connections, because that opens up an entirely different can of worms.)

By the way, I'm not an MySQL expert, but would it not make a difference to not have the where conditions joined,but rather writing WHERE tags in (tag1, tag2, tag3, tag4)?

answered Jan 10, 2013 at 22:37

Konrad Neuwirth

9085 silver badges9 bronze badges

1 Comment

Oskar Persson Over a year ago

I could try WHERE tags in (tag1, tag2, tag3, tag4), though I still don't know how to write the bind_param function

Community · Accepted Answer · 2017-05-23 12:04:34Z

1

Solved it by the help of an answer found here.

$query = "SELECT * FROM tags WHERE tags.tag LIKE CONCAT('%',?,'%')" . str_repeat(" OR tags.tag LIKE CONCAT('%',?,'%')", $searchNumber - 1)

$stmt = $mysqli -> prepare($query);
$bind_names[] = str_repeat('s', $searchNumber);

for ($i = 0; $i < count($searchArray); $i++){
   $bind_name = 'bind'.$i; //generate a name for variable bind1, bind2, bind3...
   $$bind_name = $searchArray[$i]; //create a variable with this name and put value in it
   $bind_names[] = & $$bind_name; //put a link to this variable in array
}

call_user_func_array(array($stmt, 'bind_param'), &$bind_names);

$stmt -> execute();

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Jan 11, 2013 at 20:09

Oskar Persson

6,85517 gold badges71 silver badges134 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL: Using array elements in WHERE clause using prepared statements

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related