How can I change password for sql login to database using pyodbc?

user_11663982 0
Viewed 870

I try change password for sql login to database using pyodbc in python

But I get error - Wrong syntax near object "@P1" (102) (SQLExecDirectW).....Cannot prepare the instruction (8180)

config.login = 'user'
config.haslo = '12345'
haslo = 'abcde'
con = pyodbc.connect("DRIVER={ODBC Driver 11 for SQL Server};"
                  "SERVER=Serwer;"
                  "DATABASE=Baza;"
                  "UID="+config.login+";"
                  "PWD="+config.haslo+";"
                  "autocommit=true")
kursor = con.cursor()
zapytanie = """ALTER LOGIN ? with password = ? old_password = ?"""
val = (config.login, haslo, config.haslo)
kursor.execute(zapytanie, val)
kursor.commit()
kursor.close()
del kursor
pyodbc sql-server python
1 Answers

SQL Server ODBC apparently does not support parameterization of an ALTER LOGIN statement. Instead of using this ...

uid = 'bubba'
old_pwd = 'NASCAR'
new_pwd = 'GRITS'
sql = "ALTER LOGIN ? WITH password ? old_password ?"
crsr.execute(sql, uid, new_pwd, old_pwd)

... you will need to do something like this:

uid = 'bubba'
old_pwd = 'NASCAR'
new_pwd = 'GRITS'
sql = f"ALTER LOGIN {uid} WITH PASSWORD = '{new_pwd}' OLD_PASSWORD = '{old_pwd}'"
crsr.execute(sql)

IMPORTANT - As with all dynamic SQL, this is potentially vulnerable to SQL injection issues. Be sure to sanitize the login_id and password values!

(Long-time users of SQL Server may recall that there is a system stored procedure named sp_password but the documentation indicates that it is deprecated.)

user_21443900
Related