INSERT helper function for PDO Mysql

• First of all - escaping identifiers
• PDO INSERT helper function
• Notes
• Comments

It is often happens that we have an array consists of fields and their values that represents a row to be inserted into a database. And naturally it would be a good idea to have a function to convert such an array into a correct SQL INSERT statement and execute it. So here it goes.

First of all - escaping identifiers

When creating such a function, one must be acutely aware of the SQL Injection through field names. Because we are going to add not only values but also identifiers (i.e. table and field names), which cannot be substituted with placeholders, we will need another technique to protect them.

YES, we need to protect them. Your intended usage scenario, when all identifiers are intended to be hardcoded, is not an excuse. An application is never a static piece of code. It evolves. Other people join to the project, knowing absolutely nothing of your notions and intents. They see a function, consider it safe and use it. There even is a real lot of people thinking that certain form elements are "not coming from the client side". This is why the entire function must be safe, not some unspoken ideas on how it should be used.

There are basically two ways to protect identifiers

1. The preferred one is called whitelisting. It is a bullet-proof technique that protects both from injections and also from accessing the columns that are not intended to be updated by the user. But this method is a bit tedious and more suitable for the OOP approach, where we can list the table and the column names a the class properties and then use it in the insert method. After all, the perfect white-listing is implemented when we are writing a conventional SQL INSERT query without any helper functions, and I encourage you to strongly consider this approach.
2. Escaping. Yes, we can escape identifiers too, pretty much the same way as we used to escape strings. This method has its drawbacks, as it doesn't filter out columns that may be forbidden for the user to change. And also this method is not universal, as it must be different for different SQL flavors. Despite that, I would use this method, as I think it is more suitable for a simple helper function.

Hence, this function will need a helper function of its own (limited to MySQL):

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

PDO INSERT helper function

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));
}

Then it can be used like this

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

What are we doing here?

• In the first three lines we are creating a correct list of correctly formatted column names, using keys from the $data array, to get a string like this:

  `name`,`password`

• Then we are formatting the table name

• then we are creating a list of placeholders, like

  (?,?)

• then we are creating the final SQL,

  "INSERT INTO `users` (`name`,`password`) VALUES (?,?)";

• and finally preparing and executing it, using values from the $data array.

Note that I am using positional placeholders, because column names can potentially contain characters that are disallowed in the placeholder names, such as spaces or dashes. Besides, for a helper function it doesn't really matter which kind of placeholders we are using.

In the end, it will make a bullet-proof insert query protected from SQL injections.

Notes

Please note that this function prevents you from using SQL functions such as NOW(), DEFAULT(), inet_aton() and such. In case you will need one, just fall back to raw SQL for this particular query. Or just get such values in PHP, such as date('Y-m-d H:i:s') instead of NOW(), etc. I strongly advise you against going down the rabbit hole of making such functions available - it will make your code too complex, clumsy an error-prone.