1

I am creating a Python Flask app that interfaces with an SQL database. One of the things it does is take user input and stores it in a database. My current way of doing it looks something like this

mycursor.execute(f"SELECT * FROM privileges_groups WHERE id = {PrivID}")

This is not a good or correct way of doing this. Not only can certain characters such as ' cause errors, it also leaves me susceptible to SQL injection. Could anyone inform me of a good way of doing this?

RealistikDash
  • 55
  • 6
  • what's wrong with the current way? as long as your incoming `id` is secure you should be good. the other approach maybe create the query outside the `.execute()` call and then call in the query once it's setup. – de_classified Mar 21 '20 at 20:35
  • It is dealing with user text input. If someone puts ' in the input, likely the whole query will break – RealistikDash Mar 21 '20 at 23:43

2 Answers2

1

So, if you want to avoid a sql injection...you have to have a secure query i.e. you don't want your query to doing something it shouldn't be.

  queryRun = "SELECT * FROM privileges_groups WHERE id = %s" % (PrivID)

When you use "%s" this variable as a placeholder, you avoid ambiguity as to what the injection can or cannot cause to the overall system.

then..run the .execute() call:

  mycursor.execute(queryRun)

Note: this also can be done in one step having all the changes within the .execute() call but you maybe better off splitting into piece-wise approach.

This isn't 100 % but should help a lot.

de_classified
  • 1,927
  • 1
  • 15
  • 19
1

To protect against injection attacks you should use placeholders for values.

So change

mycursor.execute(f"SELECT * FROM privileges_groups WHERE id = {PrivID}")

to

mycursor.execute("SELECT * FROM privileges_groups WHERE id = ?", (PrivID,))

Placeholders can only store a value of the given type and not an arbitrary SQL fragment. This will help to guard against strange (and probably invalid) parameter values.

However, you can't use placeholders for table names and column names.

Note: trailing comma is required for one-element tuples only but not necessary for multiple-element tuples. The comma disambiguates a tuple from an expression surrounded by parentheses.

Related: How do parameterized queries help against SQL injection?

jignatius
  • 6,304
  • 2
  • 15
  • 30
  • Applying this to my code, i got something like this `mycursor.execute("SELECT username, FROM users WHERE username_safe = ?", (username.lower()))`. This does not seem to work as it's trying to lookup ?. Any help? – RealistikDash Mar 22 '20 at 00:30
  • 1
    @RealistikDash I added a note above. You need a trailing comma to make it clear you are providing a tuple. So your query should be: `mycursor.execute("SELECT username FROM users WHERE username_safe = ?", (username.lower(),))`. You don't need a comma after `username`. – jignatius Mar 22 '20 at 05:39
  • thank you very much, I forgot tuples require trailing commas – RealistikDash Mar 22 '20 at 15:12