Protecting against SQL injection in python

Question

I have some code in Python that sets a char(80) value in an sqlite DB.

The string is obtained directly from the user through a text input field and sent back to the server with a POST method in a JSON structure.

On the server side I currently pass the string to a method calling the SQL UPDATE operation.

It works, but I'm aware it is not safe at all.

I expect that the client side is unsafe anyway, so any protection is to be put on the server side. What can I do to secure the UPDATE operation agains SQL injection ?

A function that would "quote" the text so that it can't confuse the SQL parser is what I'm looking for. I expect such function exist but couldn't find it.

Edit: Here is my current code setting the char field name label:

def setLabel( self, userId, refId, label ):
    self._db.cursor().execute( """
        UPDATE items SET label = ? WHERE userId IS ? AND refId IS ?""", ( label, userId, refId) )
    self._db.commit()

Answer 1

From the documentation:

con.execute("insert into person(firstname) values (?)", ("Joe",))

This escapes "Joe", so what you want is

con.execute("insert into person(firstname) values (?)", (firstname_from_client,))

Answer 2

The DB-API's .execute() supports parameter substitution which will take care of escaping for you, its mentioned near the top of the docs; http://docs.python.org/library/sqlite3.html above Never do this -- insecure.

Answer 3

Noooo... USE BIND VARIABLES! That's what they're there for. See this

Another name for the technique is parameterized sql (I think "bind variables" may be the name used with Oracle specifically).