Escaping optional parameter in WHERE clause

[someone]

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

 

[MRAB]

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

 

[someon]

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?

    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!

 

[Steve Holden]

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)

db.execute(query, tuple(values))

result = db.fetchall()
return result

The .execute() method should take two arguments, the second being a
tuple of data values. Some interfaces don't like an empty tuple when the
query has no parameters.

regards
Steve