Dynamically build a prepared statement with call_user_func_array()

Question

I need to dynamically build up the SQL statement and the parameters based on user input. The length of the sql statement and the number of parameters changes based on user input. I am trying to use this tutorial and apply it to my code. Here is the code:

$query = "SELECT p.*, s.* 
        FROM product p 
        INNER JOIN product_shop ps 
        ON ps.p_id = p.p_id 
        INNER JOIN shop s 
        ON s.s_id = ps.s_id 
        WHERE s.country = ?";
        $a_params[] = $place['country'];
        $a_param_type[] = "s";
    // prepare and bind

    $param_type = '';

    foreach ($place as $key => $value) {
        if ($key === "country") {
            continue;
        }
        $query .= " and s.$key = ?";
        $a_params[] = $value;
        $a_param_type[] = "s";
    }
    /* Prepare statement */
    $stmt = $conn->prepare($query);
    if($stmt === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
    }

    $a_params[] =  $a_param_type;

    /* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
    call_user_func_array(array($stmt, 'bind_param'), $a_params);

    /* Execute statement */
    $stmt->execute();   
    $meta = $stmt->result_metadata();

I know $place['country'] will always be populated. The sql statement is correct. It is:

"SELECT p.*, s.* \n        FROM product p \n        INNER JOIN product_shop ps \n        ON ps.p_id = p.p_id \n        INNER JOIN shop s \n        ON s.s_id = ps.s_id \n        WHERE s.country = ? and s.suburb = ? and s.city = ? and s.province = ?"

Don't mind the "\n" chars, they have no effect on the sql statement.

In:

call_user_func_array(array($stmt, 'bind_param'), $a_params);

the value of $a_params is:

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"

In:

$meta = $stmt->result_metadata();

the value of $meta becomes:

current_field:0
field_count:13
lengths:null
num_rows:0
type:1

Meaning that no rows were selected from the database. I have executed this sql on the database manually and it returns rows. What is wrong with my code, that makes it return no rows from the database?

EDIT: I saw that this answer says to put the "ssss" at the start of the $params so I did that and got this error in the $stmt object:

errno:2031
error:"No data supplied for parameters in prepared statement"
error_list:array(1)
propertyNode.hasAttribute is not a function

Shouldn't your `call_user_func_array()` arguments be a little different? More like `string of types, value1,value2` and so on. — RST, May 01 '16 at 08:15
@RST I tried it like this: `0:"New Zealand" 1:"Grey Lynn" 2:"Auckland" 3:"Auckland" 4:"ssss"` and this: `0:"ssss" 1:"New Zealand" 2:"Grey Lynn" 3:"Auckland" 4:"Auckland"` but it didn't return any rows. — BeniaminoBaggins, May 01 '16 at 08:26
What happens when you do use `$stmt->bind_param('s', $param);` and `implode()` your arrays before using them — RST, May 01 '16 at 08:36
@RST Could you give me a code example of that? I wanna make sure I try it correctly. — BeniaminoBaggins, May 01 '16 at 08:43
@u_mulder I've tried it as `['s','s','s','s'];` and `"ssss"` and `["ssss"]`. Not sure if it goes at the start or the end of the `$a_params` though — BeniaminoBaggins, May 01 '16 at 09:20
String `"ssss"` should be the __first__ element of `$a_params` — u_mulder, May 01 '16 at 09:44
@u_mulder Thanks. Good to clear that up. I have tried it like that. — BeniaminoBaggins, May 01 '16 at 09:47

score 4 · Accepted Answer · edited May 23 '17 at 10:28

I don't understand what ways you've tried, but I will try to answer:

according to bind_param manual:

first argument of bind_param is a string, like 'ssss'.

second and other arguments - are values to be inserted into a query.

So, your $a_params array should be not

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"

But:

0:"ssss"
1:"New Zealand"
2:"Grey Lynn"
3:"Auckland"
4:"Auckland"

See? All values are strings. And placeholders' types are the first one.

Also take into consideration that order of arguments in $a_params must be the same as order of parameters in bind_param. This means that, i.e., $a_params like

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:"ssss"

is wrong. Because first element of $a_params will be the first argument of bind_param and in this case it's not a "ssss" string.

So, this means that after you filled $a_params with values, placeholders' string should be added to the beginning of $a_params, with array_unshift for example:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

// try to call
call_user_func_array(array($stmt, 'bind_param'), $a_params);

In case this didn't work, you can refer to a part of answer you provided, where values of $a_params are passed by reference to another array $tmp, in your case you can try something like:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

$tmp = array();
foreach ($a_params as $key => $value) {
    // each value of tmp is a reference to `$a_params` values
    $tmp[$key] = &$a_params[$key];  
}

// try to call, note - with $tmp, not with $a_params
call_user_func_array(array($stmt, 'bind_param'), $tmp);

score 2 · Answer 2 · answered May 05 '16 at 09:40

REMEMBER: The second parameter of call_user_func_array needs to be referenced, not just a normal array. This is key. The accepted answer is good but just missing one thing, making the parameter referenced:

    function makeValuesReferenced($arr){
    $refs = array();
    foreach($arr as $key => $value)
        $refs[$key] = &$arr[$key];
    return $refs;
    }

$stmt = $conn->prepare($query);
//$stmt->bind_param($queryParamTypes, $queryParams); 
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($queryParams));
$stmt->execute();

Dynamically build a prepared statement with call_user_func_array()

2 Answers2

Linked