How to drop and create database using sql sanitizing

Question

cursor.execute("DROP DATABASE ?", (databasename,))

I am using python3 with pyodbc driver. Only facing issue while create and delete database. other operations like select are working fine.

Getting below error: pyodbc.ProgrammingError: ('42000', u"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")

You can't. Parametrized queries are using prepared statements and these only allow for binding of "values", not identifiers or literals. You'll have to sanitize table names, columns etc. yourself. See also this Q&A; although it is tagged php, it still applies. — shmee

tezzo tezzo · Accepted Answer · 2019-03-13T13:33:26

In order to sanitize your data you can use SQL Server QUOTENAME to returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

You also need to set autocommit=True in your pyodbc connection to allow dropping of databases.

conn = pyodbc.connect("DRIVER={SQL Server};"
    "SERVER="+server+";"
    "UID="+username+";"
    "PWD="+password,
    autocommit=True)

cursor = conn.cursor()

your_database_name = "YOUR_DB_NAME"
sql_drop = (
    "DECLARE @sql AS NVARCHAR(MAX);"
    "SET @sql = 'DROP DATABASE ' + QUOTENAME(?);"
    "EXEC sp_executesql @sql"
)

cursor.execute(sql_drop, your_database_name)

How to drop and create database using sql sanitizing

1 Answers