why is django/sqlite barfing on my parameterized raw() query?

Question

i'm not sure whether my issue is django, sqlite3, some odd interaction with regexp syntax, or just me: i've got a BAD version of a SQL query to be handled correctly by django.model:

qryStrBAD = "SELECT idx, cdate, beat FROM app_database where cc  regexp '^%s'" % (cc)
for c in MyModel.objects.raw(qryStrBAD):

this works. but of course i don't want to allow injection attacks, so i've swapped it to the recommended params list version of raw():

qryStr = "SELECT idx, cdate, beat FROM app_database where cc regexp '^%s'"
for c in MyModel.objects.raw(qryStr,[cc]):

but using this throws

DatabaseError at <URL>
Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied

even odder is that poking thru the traceback log seems to show that the substition is being done correctly:

** .../app/views.py in plotResults

        for c in MyModel.objects.raw(qryStr,[cc]):

    ...

Local vars

qryStr:     "SELECT idx, cdate, beat FROM app_database where cc regexp '^%s'"
cc:             u'LARCENY_THEFT'

** /Library/Python/2.7/site-packages/django/db/models/query.py in __iter__
<RawQuerySet: u"SELECT idx, cdate, beat FROM app_database where cc regexp '^LARCENY_THEFT'">


** /Library/Python/2.7/site-packages/django/db/models/sql/query.py in _execute_query
<RawQuery: u"SELECT idx, cdate, beat FROM app_database where cc regexp '^LARCENY_THEFT'">

** /Library/Python/2.7/site-packages/django/db/backends/util.py in execute
Local vars

params:     [u'LARCENY_THEFT']
sql:            u"SELECT idx, cdate, beat FROM app_database where cc regexp '^LARCENY_THEFT'"


** /Library/Python/2.7/site-packages/django/db/backends/sqlite3/base.py in execute

                six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])

    ...


ProgrammingError('Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.',)
Local vars


params:     [u'LARCENY_THEFT']
query:          u"SELECT idx, cdate, beat FROM app_database where cc regexp '^?'"

** /Library/Python/2.7/site-packages/django/db/backends/sqlite3/base.py in execute
Local vars

params:     [u'LARCENY_THEFT']
query:          u"SELECT idx, cdate, beat FROM app_database where cc regexp '^?'"

any guesses what's up?

Alasdair · Accepted Answer · 2013-05-16T12:09:48.213

0

Don't try to combine ^ with your parameter inside the querystring.

Instead, create the regular expression separately, then use it as a parameter.

startswith_cc = '^%s' % startswith_cc
qryStr = "SELECT idx, cdate, beat FROM app_database where cc regexp %s"
for c in MyModel.objects.raw(qryStr, [startswith_cc]):
    ...

edited May 16 '13 at 12:09

answered May 16 '13 at 11:40

Alasdair

298,606
55
578
516

i think you meant `startswith_cc = '"^%s"' % cc` ? (we need extra quotes for the regexp, and the original variable was `cc`.) then i thought i liked your basic idea, but it doesn't work: this matches zero records (: – rikb May 16 '13 at 11:54
wait: WITHOUT the extra quotes this does work!?! any chance you can help me rationalize why the `^` is a special issue, just how python/sqlite/sql parsers are dividing this substitution process up? in any case, you solved my bug, thanks Alasdair! – rikb May 16 '13 at 11:59
I believe the python sqlite3 module takes care of escaping the stings. See e.g. http://stackoverflow.com/questions/1005552/sqlite-parameter-substitution-and-quotes or http://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like. – Alasdair May 16 '13 at 12:08

why is django/sqlite barfing on my parameterized raw() query?

1 Answers1