mysql-connector-python adds single quotes around table name when table is passed as argument. Table name comes from Flask session variable

Question

I am new to MySQL and I am building a Flask project and using mysql.connector to query a MySQL Database. I know this question has been answered many times before but this is more specific to using MySQL with Flask.

I need to pass a query where I want to plug in the table name into the query, dynamically, depending on the value stored in the session variable in Flask. But the problem is, if I try to do:

Method 1:

cur.execute('SELECT * FROM %s;',(session['table_name'],))

the database throws an error stating that such a table is not found. However, the problem is mysql.connector keeps enclosing the table name with single quotes, hence the error.

Sample Error Statement:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''52_data'' at line 1

Here the table name should be 52_data and not '52_data'.

Only other workaround, I figured, is using:

Method 2:

cur.execute('SELECT * FROM '+session['table_name']+';')

which is working but it does not escape SQL Injection, I am guessing, since it's direct concatenation, unlike Method 1, where the cur.execute() function handles the escaping, as per this question.

The value being passed is stored in a sessions variable in Flask, which is not so secure, as per Miguel's Video. Hence, I want to escape that string, without triggering off an error.

Is it possible to implement Method 1 in a way that it does not add the quotes, or maybe escape the string using some function? Or maybe any other Python/Flask package that can handle this problem better?

Or if nothing works, is checking for SQL Injection manually using regex is a wiser option?

Thanks in advance.

Note: The package name for this mysql.connector is mysql-connector-python and not any other same sounding package.

Nuno Mariz · Accepted Answer · 2020-05-19 18:07:29Z

0

For identifiers, you can use something like:

table_name = conn.converter.escape(session['table_name'])
cur.execute('SELECT * FROM `{}`'.format(table_name))

For values placeholders, you can use your Method 1, by using the parameters in the cur.execute() method. They will be escaped and quoted.

More details in https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

NOTE: You don't need to end the SQL statements with ;

edited May 19, 2020 at 18:07

answered May 19, 2020 at 18:00

Nuno Mariz

5993 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

mysql-connector-python adds single quotes around table name when table is passed as argument. Table name comes from Flask session variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related