Dynamic SQL WHERE clause generation

Question

For the record, I'm using Python and SQLlite. I have a working function that generates the SQL I need, but it does not seem right.

def daily(self, host=None, day=None):
    sql = "SELECT * FROM daily WHERE 1"
    if host:
        sql += " AND host = '%s'" % (host,)
    if day:
        sql += " AND day = '%s'" % (day,)
    return sql

I will probably need to add multiple columns and criteria later on.

Any better ideas?

Edit: What does not look right is that I am constructing the SQL dynamically from Strings. This is generally not the best approach. SQL injections attacs, need to properly escape strings. I cannot use placeholders because some of the values are None and do not need to be in the WHERE clause condition.

Solution 1

You really do not want to use string formatting to include values. Leave that to the database API via SQL parameters.

Using parameters you:

• give the database a chance to prepare the statement and reuse the query plan for better performance.
• save yourself the headache of escaping the value properly (including avoiding allowing SQL escapes and with those SQL injection attacks).

Since SQLLite supports named SQL parameters, I'd return both a statement and a dictionary with parameters:

def daily(self, host=None, day=None):
    sql = "SELECT * FROM daily"
    where = []
    params = {}
    if host is not None:
        where.append("host = :host")
        params['host'] = host
    if day is not None:
        where.append("day = :day")
        params['day'] = day
    if where:
        sql = '{} WHERE {}'.format(sql, ' AND '.join(where))
    return sql, params

then pass both to cursor.execute():

cursor.execute(*daily(host, day))

SQL generation becomes complex fast, you may want to look at SQLAlchemy core to do the generation instead.

For your example, you can generate:

from sqlalchemy import Table, Column, Integer, String, Date, MetaData

metadata = MetaData()
daily = Table('daily', metadata, 
    Column('id', Integer, primary_key=True),
    Column('host', String),
    Column('day', Date),
)
from sqlalchemy.sql import select

def daily(self, host=None, day=None):
    query = select([daily])
    if host is not None:
        query = query.where(daily.c.host == host)
    if day is not None:
        query = query.where(daily.c.day == day)
    return query

The query object can have additional filters applied to it, ordered, grouped, used as a subselect to other queries, joined and finally sent to be executed at which point SQLAlchemy will turn this into SQL fit for the specific database you are connecting to.

Solution 2

Just for completeness. I found the pypika library quite handy (if libraries are allowed):

https://pypika.readthedocs.io/en/latest/index.html

It allows to construct sql queries like this:

from pypika import Query

q = Query._from('daily').select('*')
if host:
     q = q.where('host' == host)
if day:
     q = q.where('day' == day)
sql = str(q)

Comments

[Ayman]

Thanks for the SQLAlchemy link. But it is out of scope. Standard / Built-in modules only restriction.

[Martijn Pieters]

@Ayman: Then at the very least use SQL parameters. Why is there such an arbitrary restriction?

[Ayman]

Corporate policy thing with third party libraries. I hate that too but can't do anything about it.

[Martijn Pieters]

It is covered by the MIT license, hardly going to be a viral liability. My sympathies for having to work in such a restrictive environment!