0

I have a Sqlite3 table paste{ paste_id int, paste_content text }

i have to do an update statement, where text can possibly contain single ' quotes as well as "" double quotes. In python i wrote

UPDATE_Statement = "Update paste set paste_content = '%s' where paste_id=id" %(content)

But since the content can contain ' or "" , my execute query is not working properly. How can i escape this properly ?

user1159517
  • 5,390
  • 8
  • 30
  • 47

1 Answers1

2

Do not use string interpolation. Use SQL parameters instead:

UPDATE_Statement = "Update paste set paste_content = %s where paste_id=%s"

cursor.execute(UPDATE_Statement, content)

and leave escaping (and proper quoting) up to the database adapter instead. This:

  • Simplifies your code
  • Quotes different data types correctly
  • Lets the database reuse query plans for varying data
  • Prevents SQL injection attacks

See the Passing parameters into raw() in the Django SQL documentation.

If you are using a different database connector (not the connection provided by Django) verify the specific style of parameter placeholders in the documentation. The sqlite3 database adapter for example, uses ? as the placeholder syntax.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • how can i write when i have more parameters like content1,content 2 ? – user1159517 Aug 04 '13 at 22:00
  • All parameters are positional; use one `%s` per parameter, and pass in the values as a sequence in the second argument (list, tuple, etc.). – Martijn Pieters Aug 04 '13 at 22:02
  • `UPDATE_Statement = "Update paste set paste_content = %s content2= %s where paste_id=%d" cursor.execute(UPDATE_Statement, (content1,content2,id))` The above statement gave me error again. – user1159517 Aug 04 '13 at 22:06
  • Django only supports `%s`, not `%d`, for parameters. However, a `SyntaxError` exception usually indicates you did something *else* wrong somewhere, but without a full traceback that is hard to tell exactly what that might be. – Martijn Pieters Aug 04 '13 at 22:12
  • Also, a SQL `UPDATE` statement uses commas between the columns. `UPDATE_Statement = "Update paste set paste_content = %s, content2= %s where paste_id=%d"` – Martijn Pieters Aug 04 '13 at 22:14
  • @user1159517: Right, what database connector? `sqlite3` uses `?` placeholders, not `%s`. So the query would be `UPDATE_Statement = "Update paste set paste_content = ? where paste_id=?"` in that case. – Martijn Pieters Aug 04 '13 at 22:15
  • Database: sqlite3 I tried putting question marks st = Update tableName set Column1= ?,Colum2=? where colum3=? cursor.execute(st,(c1,c2,c3)) i am getting this error now. **You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.** – user1159517 Aug 04 '13 at 22:15
  • See [sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text\_factory that can interpret 8-bit bytestrings](http://stackoverflow.com/q/3425320) – Martijn Pieters Aug 04 '13 at 22:20
  • I have added this con.text_factory = str Problem Solved ! . Thanks ! – user1159517 Aug 04 '13 at 22:21