Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Escaping chars in Python and sqlite

Tags:

python

sqlite

I have a python script that reads raw movie text files into an sqlite database.

I use re.escape(title) to add escape chars into the strings to make them db safe before executing the inserts.

Why does this not work:

In [16]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='\'Allo\ \'Allo\!\"\ \(1982\)'") --------------------------------------------------------------------------- OperationalError                       Traceback (most recent call last)  /home/rajat/Dropbox/amdb/<ipython console> in <module>()  OperationalError: near "Allo": syntax error 

Yet this works (removed \' in two places) :

In [17]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='Allo\ Allo\!\"\ \(1982\)'") Out[17]: <sqlite3.Cursor object at 0x9666e90> 

I can't figure it out. I also can't ditch those leading quotes because they're actually part of the movie title. Thank you.

like image 924
rajat banerjee Avatar asked Jul 10 '10 16:07

rajat banerjee


People also ask

How do I escape in SQLite?

Double-quotes in SQLite identifiers are escaped as two double quotes. SQLite identifiers preserve case, but they are case-insensitive towards ASCII letters. It is possible to enable unicode-aware case-insensitivity.

How does Python store data in SQLite?

Inserting data using pythonImport sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3.

Is SQLite can be used with Python only?

Introduction. SQLite is a self-contained, file-based SQL database. SQLite comes bundled with Python and can be used in any of your Python applications without having to install any additional software.


1 Answers

You're doing it wrong. Literally. You should be using parameters, like this:

c.execute("UPDATE movies SET rating = ? WHERE name = ?", (8.7, "'Allo 'Allo! (1982)")) 

Like that, you won't need to do any quoting at all and (if those values are coming from anyone untrusted) you'll be 100% safe (here) from SQL injection attacks too.

like image 53
Donal Fellows Avatar answered Oct 02 '22 17:10

Donal Fellows