Optional where parts

hello,
what is your best practice if you have many optional filters?

as example i have a input form with a filter code = “” and code = “abc” (combobox or textfield)
but the meaning of “” means no filter. if me put empty in where part i would get no results.

if me would create the where part with merged strings i have the problem with sql injection or special chars if the user input this in the textfield.

Var ps As PostgreSQLPreparedStatement
ps = db.Prepare("SELECT * FROM Country WHERE code = $1")

as example in past i used
if Len(input2)>0 then where$=where$+ " and input2 like ‘" + input2 + "’"
if input3 > 0 then where$=where$+ " and input3 = " + input3.ToString

and to make it save we wrapped a method around the string inputs.

what i am missing in xojo is this syntax
"SELECT * FROM Country WHERE code = " + Prepare(input)
with the same behave.

I use my SQLBuilder_MTC which includes a way to optionally add Where clauses. For example:

var builder as new SQLBuilder_MTC.Statement
call builder _
    .SQLSelect( "*" ) _
    .From( "Country" ) _
    .CondWhere( input2.Length <> 0, "input2", input2 ) _
    .CondWhere ...
3 Likes

i think a helper class is a good idea. thanks.