PHP PDO - Passing and imploded array to a query placeholder

Question

First of all, I apologize if this is answered somewhere else, but I couldn't find anything.

I have problems with the following code:

function register_user ($register_data) {
    global $db;
    array_walk ($register_data, 'array_sanitize');
    $register_data ['password'] = md5 ($register_data ['password']); 

    $fields = '`' . implode ('`, `', array_keys ($register_data)) . '`';
    $data   = '\'' . implode ('\', \'', $register_data) . '\'';

    $query = $db -> prepare ("INSERT INTO `users` (:fields) VALUES (:data)");
    $query -> bindParam (':fields', $fields);
    $query -> bindParam (':data', $data);
    $query -> execute ();
}

The problem is that this is executed correctly but the query is not ran and the row is not inserted in the database.

Now, if I just do this:

$query = $db -> prepare ("INSERT INTO `users` ($fields) VALUES ($data)");
//$query -> bindParam (':fields', $fields);
//$query -> bindParam (':data', $data);
$query -> execute ();

everything works like a charm, so I am guessing the problem is with how I am passing data to the placeholders.

Can someone please explain to me why this is not working? I'd like to understand it properly in the first place.

Thanks in advance for any help.

Your Common Sense · Accepted Answer · 2020-06-16T17:00:59.420

There are two different use cases that could be described as Passing an imploded array to a query placeholder. One is using prepared statements with IN() clause in SQL. this case is already fully covered in this answer.

Another use case is an insert helper function, like one featured in your question. I've got an article that explains how to create an SQL injection proof insert helper function for PDO_MYSQL.

Given such a function is not only adding data values to the query but also table and column names, a prepared statement won't be enough to protect from SQL injection. Hence, such a function will need a helper function of its own, to protect table and field named. Here is one for MySQL:

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

And now we can finally have a function that accepts a table name and an array with data and runs a prepared INSERT query against a database:

function prepared_insert($pdo, $table, $data) {
    $keys = array_keys($data);
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    $pdo->prepare($sql)->execute(array_values($data));
}

that can be used like this:

prepared_insert($pdo, 'users', ['name' => $name, 'password' => $hashed_password]);

the full explanation can be found in the article linked above, but in brief, we are creating a list of column names from the input array keys and a list of comma separated placeholders for the SQL VALUES() clause. And finally we are sending the input array values into PDO's execute(). Safe, convenient and concise.

The code was not written using PDO from the beginning. I adapted it. I am actually learning PHP now so I have to adapt. Seeing that I have to move on to PDO because the old ways will become outdated, I started to change the code. As for the md5 encryption, that's just temporary. It is in my plans to better encrypt the passwords. Thanks for you help! I really appreciate it. — Tudorel Lucilius Ganea, Aug 21 '13 at 11:15
That explains a lot. Anyway, did you get to that code at the bottom of the tag wiki page? Do you have any questions regarding it? — Your Common Sense, Aug 21 '13 at 11:19
Yeah, I think I got the concept. I am now running ideas in my head. :) — Tudorel Lucilius Ganea, Aug 21 '13 at 12:32

PHP PDO - Passing and imploded array to a query placeholder

1 Answers1