0

In an application I am making, the user is able to type the name of the DB they want to connect to, and the code would connect the user to it.

I am trying to prevent any MySQL injection to this very simple query, and was trying to do so using the following: cursor.execute("USE %s;", (dbName,))

Got this type of statement from the following link: Python best practice and securest to connect to MySQL and execute queries

However, it does not connect to the DB. Instead, if gives me the following error:

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 ''my_db_name'' at line 1

When I do the quick and dirty concatenation, it connects just fine: cursor.execute("USE " + dbName)

Is it not possible to use that type of SQL Injection prevention when the SQL is not a Query? Does this only work when inserting parameters into a SELECT statement? How should I proceed?

I truly apologize if that is a silly question. I am very new at Python and considerably new at SQL in general.

Improve this question
2
  • 2
    You'll want your server to control the query structure, which means you want it to be predictable what will be selected from where and how, and you want to give the user only the minimal choice to insert some specific values, like a value in a WHERE foo = %s clause. The USE statement very much affects the query structure, i.e. you do not have any control of the "from where" anymore. That's one reason you can't use parameterised queries for it. Same as for column or table names. You'll need to have a whitelist of allowed values instead.
    – deceze ♦
    Commented Dec 11, 2019 at 16:18
  • Thank you very much for the quick response. It makes sense. My intention is actually create a "Database configuration tool", where the user will be able to create their own DB and tables for a specific use. I see that the security will be more complex than how I thought it would be.
    – Augusto Peres
    Commented Dec 11, 2019 at 16:44
Related questions
1
MySQL in Python, MATCH and injection attack
18
Protecting against SQL injection in python
0
Fixing SQL Injection With _mysql
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.