rsaxvc rsaxvc - 20 days ago 5
Python Question

Python/SQLITE: How do I escape commands/strings

I would like to construct a sqlite3 database query in python, then write it to a file.

I am a huge fan of python's interfaces for sql databases, which AFAICT wrap all calls you could mess up with a nice little '?' parameters that sanitizes/escapes your strings for you, but that's not what I want. I actually just want to prepare and escape a sql statement - to do this, I need to escape/quote arbitrary strings.

For example:

query = "INSERT INTO example_table VALUES ('%s')",sqlite_escape_string("'")


And so query should contain:
"INSERT INTO example_table VALUES ('''')"

Note that it inserted an additional ' character.

PHP's equivalent is sqlite_escape_string()
perl's equivalent is DBI's quote()
I feel Python has a better overall interface, but I happen to need the query, pre-exec.

Answer

When you use SQLite it doesn't turn parameterized queries back into text. It has an api ("bindings") and stores the values separately. Queries can be reused with different values just by changing the bindings. This is what underlies the statement cache. Consequently you'll get no help from python/sqlite in doing what you describe.

What you didn't describe is why you want to do this. The usual reason is as some form of tracing. My alternate Python/SQLite interface (APSW) provides easy tracing - you don't even have to touch your code to use it:

https://rogerbinns.github.io/apsw/execution.html#apsw-trace

SQLite also has an authorizer API which lets you veto operations performed by a statement. This also has a side effect of telling you what operations a statement would end up performing.

Comments