Building dynamic SQL queries an elegant way

Building dynamic SQL queries — which is very common to handle search forms — is most of the time made in programming languages by concatenating strings this insecure way:

<?php
require 'connect.php';

$firstname = 'Patrick';
$lastname = 'Allaert';

$query = 'SELECT * FROM users WHERE 1';

if (!empty($firstname)) {
    $query .= " AND firstname = '$firstname'";
}

if (!empty($lastname)) {
    $query .= " AND lastname = '$lastname'";
}

foreach ($db->query($query, PDO::FETCH_ASSOC) as $row) {
    print_r($row);
}
?>


Excluding the fact that this code is vulnerable to SQL injection, which is avoided using either proper escaping or prepared statement, we have to admit that appending " WHERE 1" as basic condition and prepending all conditions with " AND " looks more like a hat trick than a proper way of coding although we are used to see this.

A proper but still insecure version of this script would be:

<?php
require 'connect.php';

$firstname = 'Patrick';
$lastname = 'Allaert';

$query = 'SELECT * FROM users';

$cond = array();

if (!empty($firstname)) {
    $cond[] = "firstname = '$firstname'";
}

if (!empty($lastname)) {
    $cond[] = "lastname = '$lastname'";
}

if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}

foreach ($db->query($query, PDO::FETCH_ASSOC) as $row) {
    print_r($row);
}
?>


In this last version, we made use of the implode function to glue all the conditions together in the case at least one condition is defined. To combine this with security, the next step is to use prepared statement:

<?php
require 'connect.php';

$firstname = 'Patrick';
$lastname = 'Allaert';

$query = 'SELECT * FROM users';

$cond = array();
$params = array();

if (!empty($firstname)) {
    $cond[] = "firstname = ?";
    $params[] = $firstname;
}

if (!empty($lastname)) {
    $cond[] = "lastname = ?";
    $params[] = $lastname;
}

if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}

$stmt = $db->prepare($query);
$stmt->execute($params);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    print_r($row);
}
?>