Are Dynamic Prepared Statements Bad? (with php + mysqli)

Question

I like the flexibility of Dynamic SQL and I like the security + improved performance of Prepared Statements. So what I really want is Dynamic Prepared Statements, which is troublesome to make because bind_param and bind_result accept "fixed" number of arguments. So I made use of an eval() statement to get around this problem. But I get the feeling this is a bad idea. Here's example code of what I mean

// array of WHERE conditions
$param = array('customer_id'=>1, 'qty'=>'2');
$stmt = $mysqli->stmt_init();

$types = ''; $bindParam = array(); $where = ''; $count = 0;

// build the dynamic sql and param bind conditions
foreach($param as $key=>$val)
{
    $types .= 'i';
    $bindParam[] = '$p'.$count.'=$param["'.$key.'"]'; 
    $where .= "$key = ? AND ";
    $count++;
}

// prepare the query -- SELECT * FROM t1 WHERE customer_id = ? AND qty = ?
$sql = "SELECT * FROM t1 WHERE ".substr($where, 0, strlen($where)-4);
$stmt->prepare($sql);

// assemble the bind_param command
$command = '$stmt->bind_param($types, '.implode(', ', $bindParam).');';

// evaluate the command -- $stmt->bind_param($types,$p0=$param["customer_id"],$p1=$param["qty"]);
eval($command);

Is that last eval() statement a bad idea? I tried to avoid code injection by encapsulating values behind the variable name $param.

Does anyone have an opinion or other suggestions? Are there issues I need to be aware of?

Answer 1

I think it is dangerous to use eval() here.

Try this:

• iterate the params array to build the SQL string with question marks "SELECT * FROM t1 WHERE p1 = ? AND p2 = ?"
• call prepare() on that
• use call_user_func_array() to make the call to bind_param(), passing in the dynamic params array.

The code:

call_user_func_array(array($stmt, 'bind_param'), array($types)+$param);

Answer 2

You don't really need prepared statements and bound arguments, because you can always use mysql_real_escape_string(). And you're right; dynamically generated SQL is far more flexible and valuable.

Here's a simple example using the regular mysql_* interface:

// Array of WHERE conditions
$conds = array("customer_id" => 1, "qty" => 2);

$wherec = array("1");
foreach ($conds as $col=>$val) $wherec[] = sprintf("`%s` = '%s'", $col, mysql_real_escape_string($val));

$result_set = mysql_query("SELECT * FROM t1 WHERE " . implode(" AND ", $wherec);

Of course, this is a simplistic example, and to make it useful you have to build and refine it a lot, but it shows the ideas and it's really very very useful. For example, here is a completely generic function to insert a new row into an arbitrary table, with the columns filled with the values from an associative array and completely SQL-injection safe:

function insert($table, $record) {
    $cols = array();
    $vals = array();
    foreach (array_keys($record) as $col) $cols[] = sprintf("`%s`", $col);
    foreach (array_values($record) as $val) $vals[] = sprintf("'%s'", mysql_real_escape_string($val));

    mysql_query(sprintf("INSERT INTO `%s`(%s) VALUES(%s)", $table, implode(", ", $cols), implode(", ", $vals)));
}

// Use as follows:
insert("customer", array("customer_id" => 15, "qty" => 86));