How do I prevent sqlite from evaluating a string a

Question

Simple question that probably has a very simple answer. I am writing date strings of the format "2012-06-10" to a TEXT column from a Python script.

e.g.

cur.execute("CREATE TABLE tbl(date TEXT, ...)")

cur.execute('INSERT INTO tbl VALUES(%s)' % (str(date[i])), ...)

The script is actually evaluating the date string, so "2012-06-10" gets written into the table as "1996". From reading the docs, I'm guessing this has something to do with type affinity, but I can't figure how to override or even why the string would be evaluated.

Answer 1

Two ways:

• By using a parameterized query: cur.execute("INSERT INTO tbl VALUES (?), [str(date[i])]). This is the best way. Do it this way. The second method is only being included for posterity.
• By quoting the value (note: this is almost certainly in almost every case the wrong way): cur.execute("INSERT INTO tbl VALUES ('%s')" %(str(date[i]), ). This method is suboptimal because, unless you're careful, you'll be vulnerable to SQL injection.

To understand why this is going on, imagine the query that your code is sending to SQLite:

 INSERT INTO tbl VALUES (2012-06-10);

Which the SQL engine correctly evaluates to:

 INSERT INTO tbl VALUES (1996);

Quoting the value will solve this issue:

 INSERT INTO tbl VALUES ('2012-06-10');

But can lead to issues if the value has certain characters in it (characters like ' or the null byte).

However, with a parameterized query, the values are sent separately from the query, so there is no chance they will be misinterpreted.