How to avoid code repetition with PHP SQL prepared statements?

Question

In most examples of SQL PHP prepared statements that I see, such as:

$sql = 'INSERT INTO tasks(task_name, start_date, completed_date) VALUES(:task_name, :start_date, :completed_date)';
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
    ]);

the field names are nearly repeated ... 4 times!

• once after the INSERT INTO(...): task_name (column name in SQL)
• once after the VALUES(...): :task_name
• once in the dictionary key: :task_name
• once in the dictionary value: $taskName (local variable)

I understand that each of these have a different meaning, but still, this redundancy is really annoying: if we want to change something in the query, we have to change it 4 times!

How to have a better prepared statement avoiding so much redundancy in PHP?

Answer 1

That's a very good question and I have several answers for it.

Raw PHP

First of all, you can use several tricks to reduce the verbosity, like omitting the fields clause in the query (and adding default values in the values clause for the missing fields) and using positional placeholders:

$data = [$taskName, $startDate, $completedDate];
$sql = 'INSERT INTO tasks VALUES(null, ?, ?, ?)';
$db->prepare($sql)->execute($data);

I call them tricks because they aren't always applicable.

Note that you must provide a value for all the columns in the table. It could be simply a null value or, to make it 100% equivalent for the omitted field, you could have it as DEFAULT(field_name) so it will insert a default value defined in the table definition.

A helper function

The next level would be creation of a helper function for inserts. When doing this, one must be acutely aware of the SQL Injection through field names.

Hence, such a helper function must have a helper function of its own:

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

Having such a function we can create a helper function that accepts a table name and a data array contains field name => value pairs:

function prepared_insert($conn, $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)";
    $conn->prepare($sql)->execute(array_values($data));
} 

I am intentionally not using named placeholders here because it makes the code shorter, there could be characters disallowed in placeholder names while being perfectly valid for the column names, a space or a dash for example; and also because we generally don't care how it works inside.

Now your insert code will become

prepared_insert($db, 'tasks',[
    'task_name' => $taskName,
    'start_date' => $startDate,
    'completed_date' => $completedDate,
]);

with so much repetitions removed

A baby ORM

However I don't like the above solution either, there are some quirks in it.

To fulfill the need for the automation, we could implement such a great concept as Object Oriented Programming. This way, you'd have to write the list of columns exactly once per table.

I wrote a complete working example especially to demonstrate the concept.

First of all, you careate a prototype class that would create all common methods used for any table. Like

public function create($data): int
{
    $fields = $this->makeFieldList($data);
    $placeholders = str_repeat('?,', count($data) - 1) . '?';

    $sql = "INSERT INTO `$this->table` ($fields) VALUES ($placeholders)";
    $this->sql($sql,array_values($data));

    return $this->db->lastInsertId();
}

After that you will create actual classes for specific tables, where you will provide the table name and the list of columns:

class UserGateway extends BasicTableGateway {
    protected $table = 'gw_users';
    protected $fields = ['email', 'password', 'name', 'birthday'];
}

and then - all the magic happens here! - you won't have to write the insert code at all! Instead just create an array with data, then a new instance of your class, and call a suitable method of this class:

$data = [
    'email' => '[email protected]',
    'password' => 123,
    'name' => 'Fooster',
];

$userGateway = new UserGateway($pdo);

$id = $userGateway->create($data);
echo "Create: $id", PHP_EOL;

It should be noted that nowadays one could use either mysqli or PDO, the $db variable could be either. You'd need to have only minor adjustments when porting this example to mysqli