How do I prevent sqlite from evaluating a string as a math expression?

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.

David Wolever · Accepted Answer · 2012-09-11 21:20:28Z

9

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.

edited Sep 11, 2012 at 21:20

answered Sep 11, 2012 at 21:14

David Wolever

156k94 gold badges365 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven Rumbalski Over a year ago

And why is the first way better? Because of SQL injection.

dan04 Over a year ago

If the values are already strings (or datetime.date objects, which the Python sqlite3 module will automatically convert to strings), then the str call is redundant.

Collectives™ on Stack Overflow

How do I prevent sqlite from evaluating a string as a math expression?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related