Escaping optional parameter in WHERE clause

[someon]

On Mar 23, 1:48 pm, MRAB <goo... at mrabarnett.plus.com> wrote:
> 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

Like it. Thanks, man!