Protecting against SQL injection in python

Question

I have code in Python that sets char (80) to sqlite DB.

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

On the server side, I am currently passing a string to a method that invokes the SQL UPDATE operation.

It works, but I know that it is not safe at all.

I expect that the client side is still unsafe, so any protection should be placed on the server. What can I do to perform an UPDATE operation with SQL injection again?

A function that “quotes” text so that it cannot confuse the SQL parser is what I'm looking for. I expect such a function to exist, but cannot 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 eludes "Joe" , so you want to

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

Answer 2

DB-API .execute() supports parameter substitution, which will take care of the evacuation for you mentioned at the top of the documentation; http://docs.python.org/library/sqlite3.html above Never do this - it’s not safe.

Answer 3

Noooo ... USE VARIABLES VARIABLES! This is what they are for. See this

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