PHP PDO - Passing and imploded array to a query placeholder

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.

Solution

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.