-2

I'm unfamiliar with how injection attacks work. Will using f-strings in .execute() make me vulnerable to injection, if I only request data (not update/insert)?

Similarly, let's say I'm trying to edit a column. How can I put in my own variables through .execute() without an f-string?

i.e.

new_date = '30/10/2022'

my_database.execute(f'UPDATE people SET birthday={new_date} WHERE name="Odin"')

What would be the best way to update the data?

Also, how do I tell if a database has been corrupted?

Edit: I should add my own understanding of 'vulnerability.' The user could input their own SQL commands into new_date so that the execute will input a different command.

Titan31 Hunter29
  • 17
  • 6
  • 2
    THAT example will not, of course, but if `new_date` came from user input, then of course it could. Consider if the user entered "1; drop table people;" as the new date. You must let the database connector do it: `my_database.execute('UPDATE people SET birthday=? WHERE name="Odin"', (new_date,))`. – Tim Roberts Oct 31 '22 at 01:13
  • [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) – deceze Oct 31 '22 at 01:16
  • you could put some sort of validation around the date. For example if you know it's meant to be in some sort of a range (ie this year) – bn_ln Oct 31 '22 at 01:16
  • @TimRoberts Thank you. I'm guessing that code make the connector not consider new_date as a command? So even if I put in an SQL command it would just insert "1; drop table people;" as my birthday and not execute it? – Titan31 Hunter29 Oct 31 '22 at 01:19
  • Obligatory XKCD [Little Bobby Tables](https://xkcd.com/327/) reference. – tdelaney Oct 31 '22 at 01:45
  • The database connector will add the escapes that are necessary to make that whole thing a simple string. – Tim Roberts Oct 31 '22 at 02:49

1 Answers1

1

Yes, it will leave you vulnerable, only if new_date can be changed by users. For example, let's say new_date is set to 30/10/2022; DROP DATABASE users -- by a user. The f-string will then resolve to

UPDATE people SET birthday=30/10/2022; DROP DATABASE users -- WHERE name="Odin"

If this query is sent to your database, then it will delete a database named users.

To make user input safe to be sent to a database, you must sanitize it. To sanitize your database input, see this web page. For example, code is safe:

my_database.execute('UPDATE people SET birthday=%s WHERE name="Odin"', (new_date,))

If new_date is constant and cannot be changed in any way by the user, it is theoretically safe. However, always err on the side of caution. Personally, I choose to sanitize all input and not risk it.

Michael M.
  • 10,486
  • 9
  • 18
  • 34