How to prevent sql injection for a IN clause using python mysql connector?

Question

Using Python 3.8, mysql-connector-python 8.0.28, I have a query that looks like this

query = """
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number = "%s"
    LIMIT 1;
""" % (
    quotation_number
)
result_in_list_of_dict = query_mysql(query)

where quotation_number (note the singular) is a python string variable

How do i keep the """ format and using % to prevent SQL injection if my query is now using IN clause?

Assume the query is now written as in MySQL format

SELECT id, quotation_number FROM quotations WHERE quotation_number IN (...,...);

And the variable is now quotation_numbers (note the plural s) which is a python list of strings.

Assume the python variable quotation_numbers which is a list of strings can be of arbitrary length, with a minimum of 1 string.

Infinitely duplicated question https://stackoverflow.com/questions/49193680/improve-sql-insert-query-to-avoid-sql-injections — N1ngu, Apr 12 '22 at 10:43
@N1ngu my issue has more to do with the IN clause than the prevention of sql injection. I was wrong to use string formatting but i was already using mysql-connector i am a mere 1 step away from using mysql-connector to sanitize the paramters. My bigger issue should have been about how to create the %s given that the list of parameters can be of any length with at least 1. — Kim Stacks, Apr 12 '22 at 11:55
Ok. Then, isn't it a duplicate for https://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause ? — N1ngu, Apr 12 '22 at 14:49
You’re right . The question is duplicate. Tho I must say the accepted answer I got is better and more explicit than the answers over at the question you cite. Not justifying the duplicate tho. Should I delete? I feel Joel shd be rewarded anyway — Kim Stacks, Apr 12 '22 at 23:17

Joel · Accepted Answer · 2022-04-12T23:41:43.043

You should not use string formatting to create your request, leave argument parsing to the lib with the second parameter of cursor.execute.

Aka:

query = """
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number = %s
    LIMIT 1;
"""
result_in_list_of_dict = cursor.execute(query, (quotation_number,))

Now, to use this with a list is pretty simple

quotation_numbers = [1, 2, 3]
query = f"""
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number IN ({', '.join(['%s'] * len(quotation_numbers))})
;
"""
result_in_list_of_dict = cursor.execute(query, quotation_numbers)

You only have to create a placeholder for every item in your list and pass it to cursor.execute

To show the problem string formatting.

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s" % fake_args

print(query) # => 'SELECT "hello"; SELECT 123; -- '

By passing args to cursor.execute

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s"

cursor.execute(query, (fake_args, ))

# In MySQL log
# SELECT '\"hello\"; SELECT 123; -- '

With string formatting, you have so security against SQL injection (that's not the point of it). mysql-connector-python can do it and should do it.

But what if the "injected" stuff is in the middle of a "number"? Eg, `"1,2);DROP DATABASE wp" — Rick James, Apr 10 '22 at 16:55
That's why I said to not use string formatting. In the question, there is no security against injection, to securely pass variables, it should be passed to `cursor.execute`. I tried with a simple request `cursor.execute("SELECT %s", ('"hello"; SELECT 123; -- ', ))`, this is what have in my logs `SELECT '\"hello\"; SELECT 123; -- '`. With python formatting => `"SELECT %s" % ('"hello"; SELECT 123; -- ')` => `'SELECT "hello"; SELECT 123; -- '` — Joel, Apr 10 '22 at 21:23

score -2 · Answer 2 · answered Apr 10 '22 at 16:57

-2

Use a regexp to validate that the string contains only ^[0-9,]+$. (That would allow an invalid "1,2,,,3", but at least won't allow anything "dangerous".)

answered Apr 10 '22 at 16:57

Rick James

135,179
13
127
222

1

If you rely on your own validations, you will end up missing something. This promotes writing architecturally dangerous code. Simply use the parametrization from whatever DB connector is at your hands. Never do string formatting of variables into statements. Period. – N1ngu Apr 12 '22 at 11:42

How to prevent sql injection for a IN clause using python mysql connector?

2 Answers2