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.

Augusto Peres
  • 249
  • 4
  • 9
  • 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 Dec 11 '19 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 Dec 11 '19 at 16:44

0 Answers0