Escaping optional parameter in WHERE clause

[MRAB]

someone wrote:
> Hi,
> 
> as you can see below I have some optional parameter for my query (mf,
> age). They are in WHERE clause only if not empty.
> In this function they are not escaped as, for example, 'search'
> parameter, cause I can't pass them to execute function, which does
> escaping automatically.
> 
> I could write another if's block like that
> 
>     if mf and not age:
>         db.execute(query, search, mf, limit)
>     if age and not mf:
>         db.execute(query, search, age, limit)
>     if age and mf:
>         db.execute(query, search, mf, age, limit)
> 
> Is there a better way to deal with optional WHERE clause?
> 
> Pet
> 
>     def getData(self, db, params):
>         search = params.get('search','')
>         age = params.get('age','')
>         mf = params.get('mf','')
>         limit = params.get('limit',1)
> 
>         if mf:
>             mf = " AND mf = %s " % mf
>         if age:
>             age = " AND age = %s " % age
> 
>         query = """
>             SELECT * FROM mytable
>             WHERE class = 'P'
>             AND name = %s
>             """ +  mf +  """
>             """ +  age +  """
>             ORDER BY id DESC
>             LIMIT %s;
>         """
> 
>         db.execute(query, search, limit)
>         result = db.fetchall()
>         return result
> 
How about:

     def getData(self, db, params):
         search = params.get('search', '')
         age = params.get('age', '')
         mf = params.get('mf', '')
         limit = params.get('limit', 1)

         query = """
             SELECT * FROM mytable
             WHERE class = 'P'
             AND name = %s
         """
         values = [search]

         if mf:
             query += " AND mf = %s"
             values.append(mf)

         if age:
             query += " AND age = %s"
             values.append(age)

         query += """
             ORDER BY id DESC
             LIMIT %s;
         """
         values.append(limit)

         db.execute(query, *values)
         result = db.fetchall()
         return result