SQL Queries - Templates and a Preprocessor

• Download source code - 0.7 KB

Introduction

Let's suppose that you have to create a PHP web page which will display the records from a database table, and also allows the user to apply optional filters on the record list. For this task, you have to create a SQL command which will have a specific condition in the WHERE clause for each filter filled by the user. The classic algorithm to do this is to build the SQL command by appending the conditions one by one at the end, if the corresponding filter is filled by the user. My article describes an improved way to do this task.

Using the code

I will take, as an example, a table named Cargos, with the following fields:

id - INT
userId - INT
startCountry - VARCHAR
endCountry - VARCHAR
startRegion - VARCHAR
endRegion - VARCHAR
startLocation - VARCHAR
endLocation - VARCHAR
status - VARCHAR
cargoType - VARCHAR
weight - NUMBER 
volume - NUMBER
description VARCHAR

To use the preprocessor, we need two things:

1. the query template, containing the preprocessor constructs
2. a dictionary (an associative array in PHP) which contains a key/value pair for each of the filter fields filled by the user; usually, this array is $_REQUEST

Bellow is a PHP code example (using a manual constructed dictionary - in your code, you will want to replace $req with $_REQUEST):

<?php

include ('MySqlQueryPreProcessor.php');

$req["userId"] = 1;
$req["weight"] = 20;
$req["cargoType"] = 'box';
$req["startCountry"] = 'Romania';
$req["sortField"] = 'weight';


echo mysql_preprocess_query($req, "
SELECT  
    c.id,
    c.userId,
    c.startCountry,
    c.endCountry,
    c.startRegion,
    c.endRegion,
    c.startLocation,
    c.endLocation,
    c.status,
    c.cargoType,
    c.weight,
    c.volume,
    c.description,
FROM Cargos c
WHERE userId = {userId}
[AND c.weight <= {weight}]
[AND c.volume <= {volume}]
[AND c.cargoType = '{cargoType}']
[AND c.startCountry = '{startCountry}']
[AND c.startRegion = '{startRegion}']
[AND c.startLocation = '{startLocation}']
[AND c.endCountry = '{endCountry}']
[AND c.endRegion = '{endRegion}']
[AND c.endLocation = '{endLocation}']
[ORDER BY c.`{sortField}` ##SDIR]");
?>

The output of this PHP page will be:

SELECT  
    c.id,
    c.userId,
    c.startCountry,
    c.endCountry,
    c.startRegion,
    c.endRegion,
    c.startLocation,
    c.endLocation,
    c.status,
    c.cargoType,
    c.weight,
    c.volume,
    c.description,
FROM Cargos c
WHERE userId = 1
AND c.weight <= 20
AND c.cargoType = 'box'
AND c.startCountry = 'Romania'
ORDER BY c.`weight`

Points of Interest

As you can see, the preprocessor replaces some parts of the query template with the values from the input dictionary (associative array), and also removes some of the conditions surrounded by square brackets - [].

Below is a detailed description of the constructs handled by the preprocessor:

1. Words surrounded by curly braces, e.g., {userId}

The preprocessor will replace this string (including the curly braces) with _REQUEST["userId"]. The variable obtained from $_REQUEST["userId"] will automatically be converted to a number.

2. Words surrounded by apostrophes and curly braces, e.g., '{cargoType}'

The preprocessor will replace the string inside the curly braces (including the curly braces) with _REQUEST["cargoType"]. The variable obtained from $_REQUEST["cargoType"] is treated as a string, and escaped using mysql_real_escape_string. The apostrophes will appear in the final query.

3. A simple SQL condition surrounded by square brackets - [] and containing a preprocessor construct as the ones above, e.g., [AND c.weight <= {weight}]

This is an optional condition. If $_REQUEST["weight"] is not defined, then the condition surrounded by [] will be removed by the preprocessor from the final query. If the $_REQUEST["weight"] is defined, then the square brackets are removed, and the expression is treated like in the situations from 1 or 2.

4. Words surrounded by back apostrophes and curly braces e.g., '{sortField}'

The preprocessor will replace the string inside the currly braces (including the curly braces) with _REQUEST["sortField"]. The variable obtained from $_REQUEST["sortField"] is treated as a database object name and is not escaped.

5. Words starting with ##, e.g., ##SDIR

The preprocessor will replace this (including ##) with _REQUEST["SDIR"]. Warning: In this case, the string from the request should be checked by the developer to avoid SQL injection. E.g., in this case, $_REQUEST["SDIR"] should be checked to be equal to ASC or DESC.

Code samples

Here is the SQL query template for UPDATE command:

UPDATE Cargos SET
    startCountry = '{startCountry}',
    endCountry = '{endCountry}',
    startRegion = '{startRegion}',
    endRegion = '{endRegion}',
    startLocation = '{startLocation}',
    endLocation = '{endLocation}',
    status = '{status}',
    cargoType = '{cargoType}',
    weight = {weight},
    volume = {volume},
    description = '{description}'
WHERE userId = {userid}
AND id = {id}

Here is the SQL query template for the DELETE command:

DELETE FROM bt_Cargos
WHERE userId = {userid}
AND id = {id}

Conclusion

The SQL query templates will allow you to write clean and safe code when constructing complex SQL commands.