SQLite parameter substitution and quotes

Question

I have this line that works OK:

c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)

But I want to use SQLite parameter substitution instead instead of string substitution (because I see here that this is safer).

This is my (failed) try:

t = (name,)
c.execute('select cleanseq from cleanseqs WHERE newID="?"',t)

But this line returns:

'Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.'

So the left part of my statement doesn't work. I am supplying one binding (name, in t) but seems that the question mark (?) is not being parsed. If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them.

So the question is: How do I convert this line:

c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)

• tuinstoel: It is great to use parameters, it is not only safer but also much faster. Read here: stackoverflow.com/questions/904796/…

Answer 1

If sometimes you can't just ignore the quotes (because you're using say a LIKE command), you can fix this by doing something to the effect of:

var = name + "%"
c.execute('SELECT foo FROM bar WHERE name LIKE ?',(var,))

This will allow you to substitute in wildcards in this situation.

Answer 2

I find the named-parameter binding style much more readable – and sqlite3 supports it:

c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=:t', locals())

Note: passing {'t': t} or dict(t=t) instead of locals() would be more punctiliously correct, but in my opinion it would interfere with readability when there are several parameters and/or longer names. In any case, I do find the :t better than the ?.

• Martijn: +1 It also makes it easier to re-use a variable multiple times in the same query, without having to re-add it several times.

Answer 3

If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them.

What you remember from when you were building the whole SQL statement yourself is irrelevant.

The new story is: mark with a ? each place in the SQL statement where you want a value substituted then pass in a tuple containing one value per ? – it's that simple; the wrapper will quote any strings to make sure that they are acceptable SQL constants.

Answer 4

The library will handle quoting and escaping for you. Simply write your query like this:

c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=?', (name,))

Answer 5

Lose the quotes around ?

c.execute('select cleanseq from cleanseqs WHERE newID=?',(t,))

It's treating it as the string "?".

Do you need to use double quotes around the whole expression, instead of singles?

Answer 6

Regular User

just noticed that you'll have to do this manual by using the unsecure method of sql_string = "other sql surger here.. fieldname=\""+value+"\";"

its the only way you'll get it to parse correctly. using SQLite for win ce. and well left me with no other alternative, just escape your values before putting them in else you'll most likely end up with a very sad database from sql injections :'( lol