如何使用pyodbc更改sql登录数据库的密码? [英] How can I change password for sql login to database using pyodbc?

问题描述

我尝试在python中使用pyodbc更改sql登录到数据库的密码

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

但我收到错误 - 对象@P1"附近的语法错误(102)(SQLExecDirectW).....无法准备指令(8180)

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

推荐答案

SQL Server ODBC 显然不支持 ALTER LOGIN 语句的参数化.而不是使用这个...

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)

重要事项 - 与所有动态 SQL 一样，这可能容易受到 SQL 注入问题的影响.请务必清理 login_id 和密码值！

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

(SQL Server 的老用户可能还记得有一个名为 sp_password 的系统存储过程，但是 文档 表明它已被弃用.)

(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.)

这篇关于如何使用pyodbc更改sql登录数据库的密码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！