Create MariaDB database with Python-CodePudding

I would like to write a python script to create new MariaDB databases.
The database name is a user input. I tried to use arguments for creating the database:

#!/usr/bin/python3
import mysql.connector

mariadb_host = '127.0.0.1'
mariadb_port = 3306
mariadb_user = 'root'
mariadb_password = 'password'

mariadb_connection = mysql.connector.connect(
    host=mariadb_host,
    port=mariadb_port,
    user=mariadb_user,
    passwd=mariadb_password,
    use_pure=True
)

query = 'CREATE DATABASE %(db_name)s;'
args = {'db_name': 'test-db'}

result = None
cursor = mariadb_connection.cursor()
cursor.execute(query, args)
print(cursor.statement)
result = cursor.fetchall()
cursor.close()

The following error appears: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''test-db'' at line 1
It seems, that the command cursor.execute appends ' around the database name, which results in an invalid sql query.

How could I get around this problem and create safely new database from user input?

CodePudding user response：

Parameter substitution notation - %(name)s or just %s is for interpolating values into an SQL statement.

RDBMSs have different quoting rules for values and identifiers like database, table or column names. For example, a string value will be surrounded by single quotes to tell the RDBMS that is is a character value, but single-quoting an identifier is a syntax error; the RDBMS will require that identifiers are quoted using some other character (for example backticks, double-quotes, square brackets, depending on the RDBMS).

If you want to interpolate identifiers using Python you have to use string formatting techniques. For example, using an f-string

db_name = 'test-db'
query = f'CREATE DATABASE `{db_name}`;'

Note that it is best to quote dynamic identifier names with backticks to handle names which contain special characters.

As always with dynamic SQL generation, you should be aware of the risk of SQL injection when handling data from an untrusted source.