best practice for implementing a Search-Function via prepared statements.

Question

I'm trying to implement a Search-Function using c++ and libpqxx. But I've got the following problem: The user is able to specify 4 different search patterns (each of them optional):

1. from date
2. till date
3. document type
4. document id

Each of them is optional. So if I want to use prepared statements I would need 2^4 = 16 different prepared statements. Well, it's possible, but I want to avoid this.

Here as an example what a prepared statement in libpqxx looks like:

_connection->prepare("ExampleStmnt", "SELECT * FROM foo WHERE title=$1 AND id=$2 AND date=$3")
    ("text", pqxx::prepare::treat_string)
    ("smallint", pqxx::prepare::treat_direct)
    ("timestamp", pqxx::prepare::treat_direct);

Therefore I also have no idea how I would piece such a prepared statement together.

Is there any other 'nice' way that I didn't think of?

Answer 1

The best you can do is to have four different ->prepare clauses, depending on how many search criteria are actually used, concatenate the criteria into your String, and then branch to one of the four prepare code blocks. (That will probably spook your style checker into thinking you are creating an injection vulnerability, but of course you aren't, as long as you insert only elements of the closed set os column names.)

Note that this isn't a very nice solution, but even Stephane Faroult (in The Art of SQL) says it's the best one possible, so who am I to argue?