Dynamic parameter binding using prepared statements

Question

In building prepared statements for my site, I found that a lot of redundant code could be iterated over. mysqli::prepare was a pretty straight forward one to iterate, but when I got to mysqli::bind_param and mysqli::bind_result, I ran into the following combination of issues:

1) I do not know how many parameters into mysqli::bind_param
2) Using extract(Array) into the arguments of mysqli::bind_param will not work, as the arguments for mysqli::bind_param are passed by reference
3) The EXTR_REF flag on extract(Array) won't help either, given that the value of the element passed cannot itself be a reference.

At this point, I've given up and am using eval().

$statements[
    's_records_by_parent'=>[
        'sql'=>
            "select * from table where id=?",
        'params'=>[
                '"i"',
                '$id'
             ]
        ],
];
foreach($statements as $name=>$statement){
    if(!$name=$this->mysql->prepare(
        $statement['sql']
    ))
    {
        echo"Error preparing statement $name.";
        exit;
    }
    if(!eval("return \$name->bind_param(".implode(',',$statement['params']).");"))
    {
        echo"Error binding parameters for statement $name.";
        exit;
    }
}

The above code does exactly what I want it to do, with the exception of having an eval() statement which is ultimately going to be preparing statements based off of user input, which is concerning to me.

Have a look [how to dynamically bind parameters using prepared statement?](https://stackoverflow.com/questions/51010509/how-to-dynamically-bind-parameters-using-prepared-statement) — Masivuye Cokile, Jan 14 '19 at 12:31
Ok, that solved issue #1. The link you provided also shows how to create the references. Should have this one in a few minutes. — Evan Hendler, Jan 14 '19 at 12:45
On a modern version of php you can do something like `bind_param(implode('', array_keys($params)), ...$params)`, see http://php.net/manual/en/functions.arguments.php#functions.variable-arg-list — jeroen, Jan 14 '19 at 12:48
Ok, I have it working with `call_user_func_array()`. I'll post the final code. — Evan Hendler, Jan 14 '19 at 13:42
This is why I use PDO instead of mysqli when I code in PHP. You can just pass your array of parameter values as an argument to [PDOStatement::execute()](http://php.net/manual/en/pdostatement.execute.php). No need to do any binding. It's way easier for this kind of problem. Check out Examples #2 and #3 in the doc page I linked to. — Bill Karwin, Jan 14 '19 at 14:58

Evan Hendler · Answer 1 · 2019-01-14T13:52:18.780

This is the code using call_user_func_array():

$statements[
    's_records_by_parent'=>[
        'sql'=>
            "select * from table where id=?",
        'params'=>[
                'i',
                'id'
        ]
    ],
];
foreach($statements as $name=>&$statement){
    if(!$name=$this->mysql->prepare(
        $statement['sql']
    ))
    {
        echo"Error preparing statement $name.";
        exit;
    }
    foreach($statement['params']as$k=>$param)
         if($k)$statement['params'][$k]=&$$param;

    if(!call_user_func_array([$name,'bind_param'],$statement['params']))
    {
        echo"Error binding parameters for statement $name.";
        exit;
    }
}

Dynamic parameter binding using prepared statements

1 Answers1