SQL queries with optional WHERE parameters

There may be at some point a need to filter data based on some user input, which may or may not be available at application run-time. This is what we'll discuss in this post, in the context of SQL queries.

Let's assume we have the following table structure (I'm using MySQL for examples):

CREATE TABLE Users(
      id INT PRIMARY KEY AUTO_INCREMENT, 
      Name VARCHAR(255), 
      Age INT(3), 
      Gender VARCHAR(1), 
      Address VARCHAR(255)
)

If we want to select all data, based on age and gender we would need to write something like this:

SELECT * FROM Persons WHERE Age=? AND Gender=?

We are using prepared statements for the advantage of avoiding SQL injection.
Now, if age or gender were given by the user at run-time, we would have to build the query from code using something similar to this:

"SELECT * FROM Persons WHERE" + 
(strAge.empty() ? "" : "Age=" + strAge) +
(strGender.empty() ? "" : "AND Gender='" + strAge + "'");

This would do the trick, but it has a few disadvantages:

• we lose the advantage of prepared statements which would lead us to having to do sanity checks on the input parameters to remove the possibility of SQL injections
• the queries are mixed with the code, which is not always a good thing with regards to the readability, maintainability and testability of the code base

A solution to this problem is to write the query in the following way:

SELECT * FROM Persons WHERE IFNULL(?, Age=Age) AND IFNULL(?, Gender=Gender)

The statement above uses the IFNULL control flow directive in MySQL. If the parameters required from the user are not given, we can supply null values, and in this case, the WHERE parameter will become neutral because a condition like Age=Age is always true.
This approach will keep the advantages of prepared statements and basically remove the disadvantages of the first approach.

Control flow directives similar to IFNULL are available for other databases also.