python passing variables into sql query

Question

I have the following variable -

sql_query = """
    select jr.jobrun_id 'Job ID',
        jm.jobmst_prntname + '\\' + jm.jobmst_name 'Job Name',
        cast(jr.jobrun_proddt as date) 'Production Date' from jobrun jr
        inner join joboutput jo on jo.jobrun_id = jr.jobrun_id
        inner join jobmst jm on jm.jobmst_id = jr.jobmst_id
    where jr.jobrun_proddt BETWEEN ? and ? and jo.jobrun_output like '%not available%' and jr.jobrun_status='107'
        and jr.jobrun_dirty != 'X'
    order by jr.jobrun_proddt desc
"""

Which I'm running through the following def -

def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) \
               for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

By running the following command -

my_query = query_db(sql_query, (date1, date2))

The problem is my args are not passing into the query. What am I doing wrong?

For the record the two variables are like so -

date1 = '2014-12-15'
date2 = '2014'12-17'

I ran the following -

print ("""
        select jr.jobrun_id 'Job ID',
            jm.jobmst_prntname + '\\' + jm.jobmst_name 'Job Name',
            cast(jr.jobrun_proddt as date) 'Production Date' from jobrun jr
            inner join joboutput jo on jo.jobrun_id = jr.jobrun_id
            inner join jobmst jm on jm.jobmst_id = jr.jobmst_id
        where cast(jr.jobrun_proddt as date) BETWEEN ? and ? and (jo.jobrun_output LIKE '%does not exist%' and jr.jobrun_status='66') or
            (jo.jobrun_output LIKE '%duplicate%' and jr.jobrun_status='66') or
            (jo.jobrun_output LIKE '%password missing%' and jr.jobrun_status='66')
            and jr.jobrun_dirty != 'X'
        order by jr.jobrun_proddt desc
    """, (date1, date2))

To confirm, it's not putting the dates where the ?'s are.

edit - I know it's not working because the results I'm getting are including dates outside the two values I've specified for the BETWEEN. This is what I want the query to display -

select jr.jobrun_id 'Job ID',
jm.jobmst_prntname + '\\' + jm.jobmst_name 'Job Name',
cast(jr.jobrun_proddt as date) 'Production Date' from jobrun jr
inner join joboutput jo on jo.jobrun_id = jr.jobrun_id
inner join jobmst jm on jm.jobmst_id = jr.jobmst_id
where cast(jr.jobrun_proddt as date) BETWEEN '2014-12-15' and '2014-12-17' and (jo.jobrun_output LIKE '%does not exist%' and jr.jobrun_status='66') or
(jo.jobrun_output LIKE '%duplicate%' and jr.jobrun_status='66') or
(jo.jobrun_output LIKE '%password missing%' and jr.jobrun_status='66')
and jr.jobrun_dirty != 'X'
order by jr.jobrun_proddt desc

Why would print("foo ? bar ?", (date1, date2)) result in a replacement of ? with the variables? This is not how python string formatting works. — orange
– orange, Commented Dec 17, 2014 at 4:26

orange · Accepted Answer · 2014-12-17 04:29:00Z

1

Try

print ("""
        select jr.jobrun_id 'Job ID',
        jm.jobmst_prntname + '\\' + jm.jobmst_name 'Job Name',
        cast(jr.jobrun_proddt as date) 'Production Date' from jobrun jr
        inner join joboutput jo on jo.jobrun_id = jr.jobrun_id
        inner join jobmst jm on jm.jobmst_id = jr.jobmst_id
    where cast(jr.jobrun_proddt as date) BETWEEN '%s' and '%s' and (jo.jobrun_output LIKE '%%does not exist%%' and jr.jobrun_status='66') or
        (jo.jobrun_output LIKE '%%duplicate%%' and jr.jobrun_status='66') or
        (jo.jobrun_output LIKE '%%password missing%%' and jr.jobrun_status='66')
        and jr.jobrun_dirty != 'X'
    order by jr.jobrun_proddt desc
    """ % (date1, date2))

I replaced the variables to be populated with %s and escaped the % you were using with % to become %%.

Also, this kind of code makes your application vulnerable to sql injections. You should consider using a library such as SQLAlchemy.

answered Dec 17, 2014 at 4:29

orange

8,25215 gold badges89 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

whoisearth Over a year ago

Thnx. I reran my query in SSMS and turns out the order of the query was off. I corrected the order and bracketing properly and it works fine.

Justin O Barber Over a year ago

This is not good practice. As you mention, this solution is vulnerable to sql injection attacks. The docs explicitly warn against using this strategy. You should consider modifying your answer accordingly.

orange Over a year ago

@JustinBarber The question wasn't about good practice or not, but about formatting a string. I've already indicated the flaws of this code in general which is more than was required to answer the question.

Collectives™ on Stack Overflow

python passing variables into sql query

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related