L.fcg L.fcg - 1 year ago 51
SQL Question

How to filter out the where clause in a query builder?

I'm building an dynamic application with a query builder that consists of four select lists where the user can enter the table, column, operator and attribute names. Now I also want the user to be able to do select ALL. So the user should have the option to only select the table, and column name(=ALL). But I don't know how to filter out the where clause then. This is my php script at the moment:


include "connect.php";

$table = $_GET['tableSelected'];
$field = $_GET['fieldSelected'];
$attribute = $_GET['attributeSelected'];
$operator = $_GET['operatorSelected'];
$tableList = $_GET['tableList'];
$fieldList = $_GET['fieldList'];
$attributeList = $_GET['attributeList'];

$fieldstr = $fieldList . ",ST_AsGeoJSON(ST_Transform(l.geom,4326))";

$sql = "SELECT $fieldstr
FROM $table l
WHERE $field $operator '{$attribute}'";

if (!$response = pg_query($conn, $sql)) {
echo "A query error occured.\n";

while ($row = pg_fetch_row($response)) {
foreach ($row as $i => $attr){
echo $attr.", ";
echo ";";


Answer Source

You should build your string dynamically and only add a WHERE condition if the required fields for that section are not empty.

For example:

$sql = "SELECT {$fieldstr}
        FROM {$table}";

if (!empty($field) && !empty($operator) && !empty($attribute)) {
    $sql .= " WHERE {$field} {$operator} '{$attribute}'";

By the way, you should replace the value with a placeholder and use white-lists for the database-, table and column names and the operators to avoid sql injection / breaking your query.