1

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.

Improve this question
2
  • Its pretty straightforward. If you want to execute multiple statements, use multi=True. Does this protect you against some specific forms of sql injection? Sure - as you have discovered. Does that mean its safe to not use parameters in your sql and just set multi=False - No! Commented Sep 24, 2023 at 14:48
  • 1
    This is not a duplicate question (I reopened it). The OP knows how to use parameters to avoid SQL injection. They are asking what problems could result from the code shown. Commented Sep 24, 2023 at 14:51

2 Answers 2

Reset to default
1

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.