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, Jun 08 '12 at 14:14
Yes. So consider my question as asking it what I do is enough to ensure protection against sql injection. — chmike, Jun 08 '12 at 14:15

score 12 · Accepted Answer · edited Oct 27 '15 at 09:07

12

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 Oct 27 '15 at 09:07

sergzach

6,578
7
46
84

answered Jun 08 '12 at 14:10

Martijn

11,964
12
50
96

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 Jun 08 '12 at 14:13
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. – chmike Jun 08 '12 at 14:25
if the bad actor gives input such as `joe")` what effect would that have? – OakenDuck Mar 04 '21 at 21:27
In this answer, it would insert the name `joe")` into the column firstname – Martijn Mar 07 '21 at 16:15

score 4 · Answer 2 · answered Jun 08 '12 at 14:10

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 08 '12 at 14:10

Alex K.

171,639
30
264
288

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. – chmike Jun 08 '12 at 14:22

Gerrat · Answer 3 · 2012-06-08T14:11:38.670

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 08 '12 at 14:11

answered Jun 08 '12 at 14:06

Gerrat

28,863
9
73
101

1

What are BIND variables ? Any URL to suggest ? Add an answer. – chmike Jun 08 '12 at 14:07
Sure...which RDBMS are you using? – Gerrat Jun 08 '12 at 14:08
I'm using sqlite for now. It's not yet the production version of my web app. – chmike Jun 08 '12 at 14:17
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. – JamesHoux Jul 18 '22 at 22:03

Protecting against SQL injection in python

3 Answers3

Linked

Related