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()

@Jeremy. Yes...but it's really whether the python library for any particular database supports it (which AFAIK they all do). — Gerrat
– Gerrat, Commented Jun 8, 2012 at 14:14
Yes. So consider my question as asking it what I do is enough to ensure protection against sql injection. — chmike
– chmike, Commented Jun 8, 2012 at 14:15

Philip Ciunkiewicz · Accepted Answer · 2023-09-09 16:42:33Z

13

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,))

edited Sep 9, 2023 at 16:42

Philip Ciunkiewicz

2,8183 gold badges15 silver badges25 bronze badges

answered Jun 8, 2012 at 14:10

Martijn

12.2k12 gold badges56 silver badges101 bronze badges

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

4 Comments

chmike Over a year ago

I updated my question with the code I currently use. I did it as you suggest. Does it mean my code is safe against SQL injection ?

chmike Over a year ago

I forgot that "escaping" means securing against sql injection. I gave you the answer because of the example. Thank you very much for sharing your knowledge and helping me.

OakenDuck Over a year ago

if the bad actor gives input such as joe") what effect would that have?

Martijn Over a year ago

In this answer, it would insert the name joe") into the column firstname

Alex K. · Accepted Answer · 2012-06-08 14:10:08Z

4

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.

answered Jun 8, 2012 at 14:10

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

chmike Over a year ago

Thank you for the reference. I remember now reading this a long time ago and applying since. I wasn't aware it was protecting me against SQL injection. That's great. I did it the right way. Sorry for not giving you the answer. Martijn has less points than you and he gave an example which would be handy for people like me searching for an answer to this question.

Gerrat · Accepted Answer · 2012-06-08 14:11:38Z

1

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).

edited Jun 8, 2012 at 14:11

answered Jun 8, 2012 at 14:06

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

4 Comments

chmike Over a year ago

What are BIND variables ? Any URL to suggest ? Add an answer.

Gerrat Over a year ago

Sure...which RDBMS are you using?

chmike Over a year ago

I'm using sqlite for now. It's not yet the production version of my web app.

JamesHoux Over a year ago

Ironically, I can't even find the information about how to use Variable Binding in python. Do you have a link to info on this? I've always done this in other languages.

Collectives™ on Stack Overflow

Protecting against SQL injection in python

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related