Hello, I’m trying to write a prepared statement for a search query for my database. The problem I’m running into is that a user may choose to leave any number of fields blank on the client form used to perform the search. For example, if a user is searching for a location, they can enter an address, city, state, and zip code on the form. BUT, they can also just enter a single value in the form such as submitting a search with ONLY the city.
I’m having trouble determining how to structure this prepared statement when parameters passed in may be null or missing. I’m very new to databases in general and would appreciate guidance or being pointed in the right direction if this is a common problem which people have implemented in their N1QL queries already.
Some things I’ve tried which don’t feel right at all and haven’t worked (and have resulted in long query times) are things like NULLIF and REGEX_CONTAINS in the query, but I believe this is going down the wrong path for this problem.
NULL or MISSING values can’t be passed as dynamic parameters.
Please note only way predicate will be true f1 IS [NOT] [NULL |MISSING]. i.e f1 = NULL always false because NULL/MISSING are undefined and can’t be compared with relational operators.
This type of situations you generate query dynamically by adding the only predicates that user supplied.
What’s your suggestion about the best way to prevent N1QL (SQL) injection when generating the queries dynamically then since its not possible to use dynamic parameters for prepared statements?
If parameter is present in prepared statement if user doesn’t supplied during evaluating that part of expression returns error.
If those are in predicate If user passes NULL or MISSING that part of predicate evaluates false so there should not be an issue.
You can’t do this in N1QL. So no way user can pass NULL or MISSING parameters for predicates.
WHERE … f1 IS [NOT] $v1 …
Would a reasonable solution be to have separate prepared statements based on what values the user has supplied for the search? We have about 5-6 searchable fields.
So for example if user provides city and zip we have a prepared statement with only those 2 parameters. If a user provides only city, then we use a different prepared statement with only the city parameter.
This seems like it would be a common problem that people would have in their applications if they have some sort of search capabilities but I can’t find many possible solutions or discussions around this.
Yes, the solution we went with involved a base query string and appending additional AND WHERE variableName=userValue query string to our base query. We have logic in our code which loops and checks all possible user provided search variables and if one is not null, append the corresponding query string to our base one.