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
– shmee, Commented Mar 13, 2019 at 11:27

tezzo · Accepted Answer · 2019-03-13 15:46:38Z

2

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)

edited Mar 13, 2019 at 15:46

answered Mar 13, 2019 at 13:33

tezzo

11.2k2 gold badges27 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

How to drop and create database using sql sanitizing

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