Question 251729 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Injection – Python – MySQL

Basically,
I’m trying to find ways to explore SQL Injection vulnerabilities in the code presented below. The code has another layer, presentation, where it asks the user for board_name input.
Table_name is an inside variable, user doesn’t have control over it.

    def find_board_by_name(cls, connector, board_name, table_name: str):
        query = "SELECT * FROM {} WHERE name = '{}'".format(table_name,board_name.title())
        print(query)
        try:
            connector.mycursor.execute(query)
            res = connector.mycursor.fetchone()
            if res:
                return res[1]
            return None
        except Exception as e:
            raise e

Tried to :

1- board_name as: ‘ OR ‘1’=’1

It worked, program returned to me me first item on the table (it wasn’t supposed to, since I didn’t provide any name)

2- Tried to put board_name as: ‘;Delete Table BoardGames;
It didn’t work, mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements was presented to me.

So my question is " mysql.connector" is protecting me against the majorities of the SQL Injections? Since I didn’t set the "multi" tag in the code to True? Could you show me other ways to explore vulnerabilities in this code? Besides the 1 and 2.

I guess I know how to fix the SQL Injection problem: "connector.mycursor.execute(query, values)" probably will do the job. But, I’m trying to understand better what kind of SQL Vulnerabilities I would have with this original code without this fix.

Tags:

2

Answers


  1. 0

    SQL injection isn’t always malicious. It could result in a simple error instead of deleting all the student records.

    For example, if some legitimate user of your site is named "O’Reilly" then your code formatting would have an imbalanced quote, which would be a syntax error:

    SELECT * FROM tablename WHERE name = 'O'Reilly'
                                       ^ wtf

    Using parameters avoids this kind of problem. Query parameters aren’t just a quick way of interpolating variables. They either escape special characters in the dynamic content (by default Python does this), or else full-blown parameters keep the values separate from the SQL query until after it has been parsed by the SQL server. So no special characters can ever cause a syntax error.

    But table names and other identifiers cannot be parameters. You can use parameters only in place of SQL values, like your quoted string in your example.

    You said the user is not in control of the table name variable. That’s great, but not sufficient.

    What if another programmer on your project isn’t careful when calling your find_board_by_name() function, and inadvertently passes unsafe content as the table name? Note that unsafe content is more than just user input.

    What if the table name is a reserved keyword in SQL?

    SELECT * FROM order WHERE name = ?
              ^ syntax error

    What if the table name contains whitespace or punctuation? There are other risks.

    You should code your function so it does not trust its caller. For SQL values, do use parameters of course. For other dynamic elements like table names, you do have to interpolate variables into the SQL string, but you can be defensive:

    • Use an allowlist to verify the table name is a legitimate known table name.

    • Delimit the table name in back-ticks in your SQL, just in case it’s a reserved word or not a simple identifier.

      legit_table_names = Set(['table1', 'table2', 'table3'])
if table_name not in legit_table_names:
  return error # pseudocode

query = f"SELECT * FROM {table_name} WHERE name = %s"

    (I prefer f-strings instead of format() in modern Python)

    Login or Signup to reply.
  2. 0

    Even if execution of multiple statement is permitted, SQL injection in SELECT statements allows the attacker to spy data.

    Assuming tablename has 4 columns, injecting ' UNION SELECT name, birthday, identity_nr, credit_card_nr FROM customers WHERE '1'='1 would allow the attacker to steal credentials from another table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search