Blog

Dynamically Bind Params in Prepared Statements with MySQLi

Tags: , , , , , , , , , ,
You are currently viewing Dynamically Bind Params in Prepared Statements with MySQLi
Credits

Prepared statements must be used in application development with databases. There are two main reasons:

  • performance: parameterized queries with prepared statements reduces the database load, reusing access plans that were already prepared
  • security: using prepared statements, there is no need to escape user input. Binding parameters, the driver does the work for you. This is an ideal method to avoid SQL Injection attacks.

Fortunately, it is possible to use prepared statements with MySQL and PHP using MySQLi extension. Old MySQL extension does not support prepared statements. Moreover, it is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future.

More information about MySQLi you can find in this post.

PDO (PHP Data Objects) also supports prepared statements (and even better than MySQLi).

A simple Example

I will use the following query for example. Two parameters are used: $category_id (customer category) and $lastname, a string which customer lastname contains.

First, connect to database

$conn = new mysqli('db_server', 'db_user', 'db_passwd', 'db_name');
if($conn->connect_error) {
        $this->last_error = 'Cannot connect to database. ' . $conn->connect_error;
}

Without prepared statements

$sql = 'SELECT id, lastname FROM customers WHERE ' .
        'category=' . $category_id . ' AND ' .
        'lastname LIKE ' . "'%" . $conn->real_escape_string($lastname) . "%'";

$res = $conn->query($sql);
if($res === false) {
        $this->last_error = 'Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg();
} else {
        $res->data_seek(0);
        while($row = $res->fetch_assoc()) {
                array_push($a_data, $row);
        }
}

With prepared statements

$sql = 'SELECT id, lastname FROM customers WHERE ' .
        'category = ? AND ' .
        'lastname LIKE ?';

/* Prepare statement */
$stmt = $conn->prepare($sql);
if($stmt === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
}

$category_id = 1;
$lastname = '%Smith%';

/* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
$stmt->bind_param('is', $category_id, $lastname);

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

/* Fetch result to array */
$res = $stmt->get_result();
while($row = $res->fetch_array(MYSQLI_ASSOC)) {
        array_push($a_data, $row);
}

The problem

$stmt->bind_param() does not accept params array. So, how to bind params, if their number is variable, depending on user input in your application?

A workaround is to use call_user_func_array to pass dynamically the params array.

The solution

In the following code:

  • $conn is the connection object
  • $a_bind_params is the array of the parameters you want to bind
  • $a_param_type is an array with the type of each parameter (Types: s = string, i = integer, d = double, b = blob). This is another disadvantage of MySQLi API. You have to maintain this array some way in your application.

With call_user_func_array, array params must be passed by reference. See notes in manual page.

The code:

/* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
$a_params = array();

$param_type = '';
$n = count($a_param_type);
for($i = 0; $i < $n; $i++) {
        $param_type .= $a_param_type[$i];
}

/* with call_user_func_array, array params must be passed by reference */
$a_params[] = & $param_type;

for($i = 0; $i < $n; $i++) {
        /* with call_user_func_array, array params must be passed by reference */
        $a_params[] = & $a_bind_params[$i];
}

/* Prepare statement */
$stmt = $conn->prepare($sql);
if($stmt === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
}

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

/* Fetch result to array */
$res = $stmt->get_result();
while($row = $res->fetch_array(MYSQLI_ASSOC)) {
        array_push($a_data, $row);
}

REMARK: get_result() is available only with mysqlnd (MySQL Native Driver). Otherwise you have to create the $a_data array manually with code.

Other approaches

This problem doen not exist with PHP with PDO or PHP with PostgreSQL. IT’S SO SIMPLE:

PHP with PDO

$sql = 'SELECT id, lastname FROM customers WHERE ' .
        'category = ? AND ' .
        'lastname LIKE ?';

try {
        $stmt = $conn->prepare($sql);
        $stmt->execute($a_bind_params);
        $a_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $e->getMessage(), E_USER_ERROR);
}

PHP with PostgreSQL

$sql = 'SELECT id, lastname FROM customers WHERE ' .
        'category = $1 AND ' .
        'lastname LIKE $2';

$rs = pg_query_params($conn, $sql, $a_bind_params);
if($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . pg_last_error(), E_USER_ERROR);
} else {
        $a_data = pg_fetch_all($rs);
}

It would be ideal if PHP mysqli_stmt API offer the ability to pass an array of params to bind in prepared statements (as PDO or POSTGRES). Until then, you may use a workaround like the above one.