Building SQL statements with optional parameters

Question

I'm working with raw SQL in PHP. What's a good way of building statements with optional parameters? For example, I need to filter user profiles with criteria such as gender, age and origin. In what form would it be best to pass parameters like these?

Here's my current approach:

$params = "";

if( isset($_GET["gender"]) ){

    $gender = "";

    switch( $_GET["gender"] ){
        case "male":
            $gender = " WHERE users.gender = 1 ";
            break;
        case "female":
            $gender = " WHERE users.gender = 0 ";
            break;
    } 

    $params = $params + $gender;
}

score 3 · Answer 1 · answered Jul 13 '11 at 16:42

3

A simple approach that might work for you:

1 - Begin your WHERE clause with "1=1" so that all of your conditions can simply by "AND FIELD = :PARAM", rather than worrying about whether the word "WHERE" has already been added

2 - Use ISNULL() to build a query that basically ignores parameters that are not provided.

Example:

SELECT *
FROM MY_TABLE t
WHERE 1=1
  AND (t.gender = ISNULL(:gender,t.gender))
  AND (t.age >= ISNULL(:age,t.age))

answered Jul 13 '11 at 16:42

JosephStyons

57,317
63
160
234

:gender is just the placeholder coming from your sql, yes - I guess I imagined that your query would be parameterized, but if that's not the case, you can just poke the static value in there (which would be NULL if the parameter should be ignored). – JosephStyons Jul 14 '11 at 02:52

score 2 · Answer 2 · answered Jul 13 '11 at 16:37

2

You could have something like this as a where clause:

where (:gender is null or gender = :gender) ...

And you pass null for when the user doesn't provide you the parameter.

answered Jul 13 '11 at 16:37

Jordão

55,340
13
112
144

1

Of course, if it's search criteria entered by the user then you're in trouble when they enter `1=1 -- DROP TABLE Users` – Tom H Jul 13 '11 at 17:04
Yes it is. Take a look at [this answer](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php/60496#60496). – Jordão Jul 14 '11 at 00:02

score 2 · Accepted Answer · answered Jul 13 '11 at 16:39

If you are determined to use raw SQL you could roll your own very simple query builder.

There are a number of libraries that do this for you but the basic principle would be to create an object that knows about your 'base query' then set a series of parameters on that object .Then write a method for building your SQL statement according to those parameters.

This might be as simple as setting an array of clauses and then imploding them with an 'AND' statement between them...

score 1 · Answer 4 · answered Jul 13 '11 at 17:35

In your given example, I would probably do something like:

$params = $params . " WHERE `users`.`gender` = " 
  . ($_GET["gender"] == "female" ? "0" : "1");

In the case of truly optional parameters, I usually do something like this:

if (isset($_GET["gender"]))
{
  $params = $params . " WHERE `users`.`gender` = " 
    . ($_GET["gender"] == "female" ? "0" : "1");
}

Building SQL statements with optional parameters

4 Answers4