Pass a python list to an SQL Query

Question

res = db.execute("""
SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
FROM RawData r
INNER JOIN Product p
ON r.ProductId = p.ProductId 
INNER JOIN Calendar c
ON r.DayId = c.DayId
WHERE c.WeekCodeInYear BETWEEN 1 AND 12
AND 
c.YearId = 2014
AND p.Distributor IN (%s)
GROUP BY c.WeekCodeInYear """ % _studios)

_studios is a Python list and is a part of a JSON object. It can be read as:

["WARNER","TF1","GAUMONT","PATHE","STUDIOCANAL","FRANCETV","M6SND"]

However when I try and execute this I get an error:

ProgrammingError: (ProgrammingError) (207, "Invalid column name 'u'WARNER', u'TF1', 
u'GAUMONT', u'PATHE', u'STUDIOCANAL', u'FRANCETV', u'M6SND''.DB-Lib error message 20018, 
severity 16:\nGeneral SQL Server error: Check messages from the SQL

The Database however has all the columns specified. I guess it has to do with the format. Not sure though.

The query when printed in the command prompt can be seen as:

SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
FROM RawData r
INNER JOIN Product p
ON r.ProductId = p.ProductId
INNER JOIN Calendar c
ON r.DayId = c.DayId
WHERE c.WeekCodeInYear BETWEEN 1 AND 12
AND
c.YearId = 2014
AND p.Distributor IN ([u'WARNER', u'TF1', u'GAUMONT', u'PATHE', u'STUDIOCANAL',
u'FRANCETV', u'M6SND'])
GROUP BY c.WeekCodeInYear

I notice the [ ] in the p.distributor line which is the probably issue?

This is trivially vulnerable to injection attacks, even in the "fixed" version where you concatenate the list into a comma-delimited string. Use whatever parameterized query implementation your SQL API provides. — roippi
– roippi, Commented Jul 3, 2015 at 12:37

wonderb0lt · Accepted Answer · 2015-07-03 12:49:29Z

1

This is indeed the issue. You're using the __repr__ version of the list. Instead, use str.join function to make it into a usable string, combined with a list comprehension to quote the strings:

"""SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
FROM RawData r
INNER JOIN Product p
ON r.ProductId = p.ProductId
INNER JOIN Calendar c
ON r.DayId = c.DayId
WHERE c.WeekCodeInYear BETWEEN 1 AND 12
AND
c.YearId = 2014
AND p.Distributor IN ({})
GROUP BY c.WeekCodeInYear """.format(', '.join(["'" + studio + "'" for studio in _studios]))

This, however, becomes very hard to maintain and read if there's more complex queries with more parameters. The above solution's format statement is already hard to understand, this will get only worse.

It is also vulnerable to SQL injection, as roippi has pointed out. They are also right with their suggestion to use parametrized queries.

edited Jul 3, 2015 at 12:49

answered Jul 3, 2015 at 12:42

wonderb0lt

2,0531 gold badge25 silver badges37 bronze badges

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

8 Comments

Tauseef Hussain Over a year ago

This converts the string as ("WARNER", "TF1", "GAUMONT", "PATHE", "STUDIOCANAL", "FRANCTV", "M6SND"). So basically the double quotes dont work no? What I need is ('WARNER', 'TF1', 'GAUMONT', 'PATHE', 'STUDIOCANAL', 'FRANCTV', 'M6SND')

wonderb0lt Over a year ago

Whoops, overlooked your requirement there, but I've fixed it now. :)

Tauseef Hussain Over a year ago

Now this solution doesnot read the string at all :( The previous one read the string but with "

wonderb0lt Over a year ago

Works for me with Python 2.7 and 3.4, what problem do you have?

Tauseef Hussain Over a year ago

Works now! Probably some sytax issue from my end. But yes I have about 5 more parameters to add. In that case this solution would become infeasible?

|

Collectives™ on Stack Overflow

Pass a python list to an SQL Query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related