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.
-
I think that I'll use something like sqlalchemy, and then doing a pickle.dump, which will store the object in a file, so I can use later the same object.jsevilleja– jsevilleja2012-06-27 10:35:41 +00:00Commented Jun 27, 2012 at 10:35
Add a comment
|
4 Answers
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?
Comments
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.
1 Comment
jsevilleja
All I want is to being able to recreate the database in other computers. Since I want to use the less bandwith possible, I need to create partial dumps (which will contain the changes) and transfer them over the net. Is a good idea to use something like sqlalchemy, store the objects with pickle, and use them in the remote computer?
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.
1 Comment
jsevilleja
Yes, but I want to get the query, not the result. For example, "SELECT * FROM stocks WHERE symbol=tralala".
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 ?
answered Jun 27, 2012 at 10:20
bruno desthuilliers
78.3k6 gold badges102 silver badges129 bronze badges
1 Comment
jsevilleja
I tried that too. I ended at a "import _sqlite3", which I think corresponds to a '/usr/lib64/python2.7/lib-dynload/_sqlite3.so' file