CODEWITHSUNDEEP
pythonsqlite
jsevilleja
jsevilleja

Reputation: 83

Python + Sqlite 3. How to construct queries?

I'm trying to create a python script that constructs valid sqlite queries. I want to avoid SQL Injection, so I cannot use '%s'. I've found how to execute queries, cursor.execute('sql ?', (param)), but I want how to get the parsed sql param. It's not a problem if I have to execute the query first in order to obtain the last query executed.

Upvotes: 0

Views: 1305

Answers (4)

Ned Batchelder
Ned Batchelder

Reputation: 376052

If you're trying to transmit changes to the database to another computer, why do they have to be expressed as SQL strings? Why not pickle the query string and the parameters as a tuple, and have the other machine also use SQLite parameterization to query its database?

Upvotes: 1

Donal Fellows
Donal Fellows

Reputation: 137787

If you're not after just parameter substitution, but full construction of the SQL, you have to do that using string operations on your end. The ? replacement always just stands for a value. Internally, the SQL string is compiled to SQLite's own bytecode (you can find out what it generates with EXPLAIN thesql) and ? replacements are done by just storing the value at the correct place in the value stack; varying the query structurally would require different bytecode, so just replacing a value wouldn't be enough.

Yes, this does mean you have to be ultra-careful. If you don't want to allow updates, try opening the DB connection in read-only mode.

Upvotes: 1

bruno desthuilliers
bruno desthuilliers

Reputation: 77942

I want how to get the parsed 'sql param'.

It's all open source so you have full access to the code doing the parsing / sanitization. Why not just reading this code and find out how it works and if there's some (possibly undocumented) implementation that you can reuse ?

Upvotes: 0

Mariusz Jamro
Mariusz Jamro

Reputation: 31683

Use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. 

# Never do this -- insecure!
symbol = 'hello'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

More reference is in the manual.

Upvotes: 0

Related Questions