[SQL Server]Statement(s) could not be prepared

Question

here's my code

import pyodbc
username = 'abcdefg'
newPassword = 'xyz1234!'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=abc.database.windows.net;DATABASE=master;UID=yyzzyy;PWD=abcd1234!')
cursor = cnxn.cursor()
cursor.execute("ALTER LOGIN ? WITH PASSWORD = ?", username, newPassword)

I am getting the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

I cannot use %s or fstring as it risks SQL Injection. have to make do with markers(?). Please help me fix this.

Answer 1

LOGIN cannot be parametrised; you'll need to use dynamic SQL for this. I would guess this would work for you, it will from a SQL point of view (I don't know enough about python to suggest if the problem is in that code).

DECLARE @SQL nvarchar(MAX) = N'ALTER LOGIN ' + QUOTENAME(?) + N' WITH PASSWORD = N' + REPLACE(?,'''','''''') + N';';
EXEC sp_executesql @SQL;