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?

Answer 1

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();});

Answer 2

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

Answer 3

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();