Thursday, September 13, 2007

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($queryPDO::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($queryPDO::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);
}
?>

8 comments:

Anonymous said...

Since this is php you may be using MySQL. I believe the later/newer versions of MySQL can handle SQL code nativley so this could be handled better through a parameterized query. Although that also assumes MySQL could handle Dynamic SQL...I guess I've conjectured enough here eh? I guess my point is though you've addressed the basics there are opportunities to use alternate archetectural design that might be more flexible and productive. Kudos on the blog though. Keep plugging at it!

Patrick Allaert said...

Indeed MySQL is used in my example, but I could use PostreSQL, Oracle or any other DB server handled by PDO (being a consistent and lightweight interface to most of DB server). In the last part of my examples, I use PDO->prepare() and PDOStatement->execute() that are parts of the API for "prepared statements" (another name for "parameterized queries", being almost exclusively used in the .Net/Microsoft world), by the way this is what solves the SQL injection problem of the 2 first examples.

Sam said...

Patrick, your example really helped my out...exactly what I was looking for!

Thanks

Geert said...

It's a nice example, but keep in mind that using dynamic queries in this fashion means that your prepared statements won't be cached as well when using a static query (as the query is constantly changing).

Anonymous said...

still useful well done ^_^

Anonymous said...

Would you really hack the first example or second!! Maybe not that easy.
Good tut though

Anonymous said...

Excellent solution! Thank you very much!

Anonymous said...

This was seriously helpful and the fact that it works with PDO prepared statements is killer. Thanks!