0

I have a table which looks like

mysql> select * from statements;
+----+----------------+--------+---------+
| id | account_number | amount | type    |
+----+----------------+--------+---------+
|  1 |              1 |   1000 | Deposit |
|  2 |              1 |    500 | Fees    |
+----+----------------+--------+---------+
2 rows in set (0.00 sec)

I have a PyMySQL connector through which I want to execute a query select * from statements where type in ('Deposit', 'Fees')

My question is different from possible duplicates as it asks particularly for "IN" type of queries, where list sizes can be dynamic and are slightly difficult to write than the usual %s hardcoded select * from statements where type in (%s, %s) type of queries.

I am wondering how to exactly write the query in a way that it is parameterized and relatively safe from SQL injection. My current code snippet is as follows:

import pymysql

connection = pymysql.connect('''SQL DB credentials''')
cur = connection.cursor()
l = ['Deposit', 'Fees']
st = f'select * from statements where type in (' + ','.join(['%s'] * len(l)) + ')'
cur.execute(st, l)
cur.fetchall()

Result:

((1, 1, 1000, 'Deposit'), (2, 1, 500, 'Fees'))

My question is, is this SQL statement parameterized well as safe from basic SQL injection?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Speeeddy
  • 154
  • 8
  • 2
    You're using pymysql correctly. That's the best it can do. There's no MySQL connector for Python that implements real query parameters. They only copy values into the SQL query string before preparing it, and they do apply best-effort quoting and escaping to protect against SQL injection. – Bill Karwin Sep 20 '19 at 12:31
  • you could you mysql coonector https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/ for prepare3d stqaements – nbk Sep 20 '19 at 12:39
  • Possible duplicate of [How do PyMySQL prevent user from sql injection attack?](https://stackoverflow.com/questions/51647301/how-do-pymysql-prevent-user-from-sql-injection-attack) – Darren Christopher Sep 20 '19 at 12:41

0 Answers0