Re: [pysqlite] database locked

Eric S. Johansson wrote:
> Dennis Lee Bieber wrote:
>> On Fri, 06 Feb 2009 00:24:01 -0500, "Eric S. Johansson"
>> <[email protected]> declaimed the following in
>> gmane.comp.python.db.pysqlite.user:
>>
>>
>>>            selection_command = selection_command +  " limit ?"
>> 	<snip>
>>> pysqlite2.dbapi2.OperationalError: near "?": syntax error
>>>
>> 	I suspect you need to fill in the limit using regular % string
>> interpolation.
> 
> it doesn't bark at me for limit but does bark at me for ? substitution for the
> table name.  this is in part why I asked is there anything I can use to see the
> end result of the ? substitution process.

There any substitution process happening with the ?. It's more like a 
function that's being compiled by SQLite for its own virtual machine 
that is then called with parameters (see attachments).

According to the docs, there are nice pragmas like vdbe_listing, 
vdbe_trace, etc., but I couldn't get them to work.

> [...] 
>> 	Visualize, just for example, a statement like:
>>
>> select * from ? where ? = ?
>>
> ...
>> 	On the other side of the equation, you are putting quotes around the
>> ?s for real data values... You don't need the quotes! Again, the purpose
>> of parameterized queries is that the database interface, itself, ensures
>> that the contents which take the place of the ? will be properly quoted
>> and escaped to fill the role of "data item".
> 
> I think I understand.  Maybe what I need to use is a limited set of values and
> types to protect against SQL injection attacks. i.e. limit and offset are only
> integers and order by argument would be limited to a finite number of possible
> that I use.  Then I could use string substitution and get the quoting right
> without worrying about injection attacks. [...]

As noted before, the numbers for LIMIT and OFFSET work fine as bound 
parameters:

 >>> import sqlite3
 >>> con = sqlite3.connect(":memory:")
 >>> con.execute("select * from (select 1 union select 2 union select 3) 
limit ?", (1,)).fetchall()
[(1,)]
 >>>

If you think you must use string substitution to dynamically create SQL, 
you could write your own quote() function. But then you must make sure 
that you *always* use it.

If I really needed that, I'd probably use an existing SQL generation 
framework, like the one from SQLAlchemy. It's perfectly possible to use 
SQLAlchemy for just that and not use the rest of it.

-- Gerhard

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite

from pysqlite2 import dbapi2 as sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table foo(a, b)")
cur.execute("explain insert into foo(a, b) values (?, ?)", ("bla", 42))
print ">> With bound parameters"
for row in cur.fetchall():
    print row
print "-" * 50

print ">> Literally"
cur.execute("explain insert into foo(a, b) values ('bla', 42)")
for row in cur.fetchall():
    print row

>> With bound parameters
(0, u'Trace', 0, 0, 0, u'explain insert into foo(a, b) values (?, ?)', u'00', None)
(1, u'Goto', 0, 11, 0, u'', u'00', None)
(2, u'SetNumColumns', 0, 2, 0, u'', u'00', None)
(3, u'OpenWrite', 0, 2, 0, u'', u'00', None)
(4, u'NewRowid', 0, 2, 0, u'', u'00', None)
(5, u'Variable', 1, 3, 0, u'', u'00', None)
(6, u'Variable', 2, 4, 0, u'', u'00', None)
(7, u'MakeRecord', 3, 2, 5, u'bb', u'00', None)
(8, u'Insert', 0, 5, 2, u'foo', u'0b', None)
(9, u'Close', 0, 0, 0, u'', u'00', None)
(10, u'Halt', 0, 0, 0, u'', u'00', None)
(11, u'Transaction', 0, 1, 0, u'', u'00', None)
(12, u'VerifyCookie', 0, 1, 0, u'', u'00', None)
(13, u'TableLock', 0, 2, 1, u'foo', u'00', None)
(14, u'Goto', 0, 2, 0, u'', u'00', None)
--------------------------------------------------
>> Literally
(0, u'Trace', 0, 0, 0, u"explain insert into foo(a, b) values ('bla', 42)", u'00', None)
(1, u'Goto', 0, 11, 0, u'', u'00', None)
(2, u'SetNumColumns', 0, 2, 0, u'', u'00', None)
(3, u'OpenWrite', 0, 2, 0, u'', u'00', None)
(4, u'NewRowid', 0, 2, 0, u'', u'00', None)
(5, u'String8', 0, 3, 0, u'bla', u'00', None)
(6, u'Integer', 42, 4, 0, u'', u'00', None)
(7, u'MakeRecord', 3, 2, 5, u'bb', u'00', None)
(8, u'Insert', 0, 5, 2, u'foo', u'0b', None)
(9, u'Close', 0, 0, 0, u'', u'00', None)
(10, u'Halt', 0, 0, 0, u'', u'00', None)
(11, u'Transaction', 0, 1, 0, u'', u'00', None)
(12, u'VerifyCookie', 0, 1, 0, u'', u'00', None)
(13, u'TableLock', 0, 2, 1, u'foo', u'00', None)
(14, u'Goto', 0, 2, 0, u'', u'00', None)