如何使用变量更改sql登录密码 [英] How to change a sql login password with variables
问题描述
我正在尝试使用 Alter LOGIN 更新现有 SQL 登录的密码
I am trying to update the password for an existing SQL login using Alter LOGIN
我知道以下作品
ALTER LOGIN [username1] WITH PASSWORD = 'somenewpassword123';
但是当我尝试使用局部变量时
However when I try to use a local variable
DECLARE @newpass nvarchar(max);
SET @newpass = 'P@ssw0rd12345';
ALTER LOGIN [username1] WITH PASSWORD = @newpass;
这失败了.将 [] 大括号添加到变量似乎可以在 SSMS 查询编辑器中解决此问题，但是通过在 C# 中写出查询以编程方式使用它，它与上述语句一样失败，并出现相同的错误(密码处的语法错误)
This fails. Adding [] braces to the variable seems to resolve this within the SSMS query editor however using this programmaticlly by writing out the query in C# it fails as the above statement with the same error ( syntax error at PASSWORD)
c# 应用程序中的代码
Code within c# app
public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
{
try
{
string updatePassword =
@" SET NOCOUNT ON
DECLARE @loginName AS nvarchar(max) = {0}
DECLARE @password AS nvarchar(max) = {1}
EXEC('
USE master
ALTER LOGIN ['+ @loginName + '] WITH PASSWORD = ['+ @password + ']
')";
return context.Database.ExecuteSqlCommand(updatePassword, loginName, password);
}
catch (Exception)
{
return -2;
}
}
我也尝试过对密码进行哈希处理(认为这是变量的问题)但这里的语法不被接受
I have also tried to hash the password (thinking that was the issue with the variable) but the syntax here is not being accepted
DECLARE @newpass nvarchar(max);
SET @newpass = 'P@ssw0rd12345';
DECLARE @hashedpass varbinary(max);
SET @hashedpass = HASHBYTES('SHA1', CONVERT(nvarchar(max),@newpass));
ALTER LOGIN [newuser10] WITH PASSWORD = @hashedpass HASHED;
SELECT @hashedpass;
谁能帮助我了解如何使用变量而不是固定值来更新 sql 中的登录密码?
Can anyone help me understand how to update a login's password in sql using a variable instead of a fixed value?
提前致谢
更新
根据查理的建议，我还尝试了以下方法
Based upon a suggestion from Charlie I also tried the following
public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
{
try
{
string updatePassword =
@"ALTER LOGIN [' + @loginName +'] WITH PASSWORD = @password ";
return context.Database.ExecuteSqlCommand(updatePassword, new SqlParameter("loginName", loginName), new SqlParameter("password", password));
}
catch (Exception)
{
return -2;
}
}
这仍然会生成一个 sqlException Incorrect Syntax new '@password'.如果我括号参数
This still generates a sqlException Incorrect Syntax new '@password'. If I brace the parameter
public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
{
try
{
string updatePassword =
@"ALTER LOGIN [' + @loginName +'] WITH PASSWORD = [' + @password +']";
return context.Database.ExecuteSqlCommand(updatePassword, new SqlParameter("loginName", loginName), new SqlParameter("password", password));
}
catch (Exception)
{
return -2;
}
}
然后我在 PASSWORD 附近生成一个 sqlException Incorrect syntax.
I then generate a sqlException Incorrect syntax near PASSWORD.
更新 2
根据查理的更新建议，我尝试使用 QuoteName 函数
Using the updated suggestions from Charlie I attempted to use the QuoteName function
string sql = @"DECLARE @sql NVARCHAR(500)
SET @sql = 'ALTER LOGIN ' + QuoteName(@loginName) +
' WITH PASSWORD = ' + QuoteName(@password, '''')
EXEC @sql";
return context.Database.ExecuteSqlCommand(sql, new SqlParameter("loginName", loginName), new SqlParameter("password", password));
虽然查询字符串的格式似乎正确，但抛出了以下 SQLException*名称 'ALTER LOGIN [newuser10] WITH PASSWORD = 't#P@ssw0rd'' 不是有效标识符.
While it appears that the query string is properly formed the following SQLException is thrown *The name 'ALTER LOGIN [newuser10] WITH PASSWORD = 't#P@ssw0rd'' is not a valid identifier.
编辑
在更多阅读之后，错误是由包装@sql 的语法错误产生的，允许查询在没有错误的情况下执行
After some more reading the error was generated by a syntax error wrapping the @sql allows the query to execute with no errors
string sql = @"DECLARE @sql NVARCHAR(500)
SET @sql = 'ALTER LOGIN ' + QuoteName(@loginName) +
' WITH PASSWORD = ' + QuoteName(@password, '''')
EXEC(@sql)";
附带说明:通过简单地构建字符串并将其作为
On a side note: by simply building the string and running it as
string updatePassword = "USE MASTER ALTER LOGIN [" + loginName + "] WITH PASSWORD = '" + password + "'";
return context.Database.ExecuteSqlCommand(updatePassword);
以上也是一种解决方法，更新sql登录.虽然此代码的实现最大限度地减少了 sql 注入的可能性，但这并不是最理想的方法.
the above is also a workaround and updates the sql login. While the implementation of this code minimizes the potential for sql injections this is not the most desirable approach.
-谢谢
推荐答案
您需要在 DbContext 级别使用参数.有关更多详细信息，请参阅此答案，但，这是一个代码示例(改编自同一页面):
You need to use parameters at the DbContext level. See this answer for more details, but, here's a code example (adapted from that same page):
string sql = "ALTER LOGIN @loginName WITH PASSWORD = @password";
ctx.Database.ExecuteSqlCommand(
sql,
new SqlParameter("loginName", loginName),
new SqlParameter("password", password));
在这里(以及任何地方)使用参数的目的是防止 SQL 注入攻击.鉴于您正在编写更改密码的代码，这一点尤其重要.
The purpose of using the parameters here (and everywhere) is to prevent a SQL injection attack. This is especially important given that you are writing code that changes a password.
更新
ALTER LOGIN 语句不适用于变量；它必须通过动态 SQL 来完成.以下是更新后的代码示例:
The ALTER LOGIN statement won't work with variables; it must be done through dynamic SQL. Here's an example of the updated code:
string sql = @"DECLARE @sql NVARCHAR(500)
SET @sql = 'ALTER LOGIN ' + QuoteName(@loginName) +
' WITH PASSWORD= ' + QuoteName(@password, '''')
EXEC @sql ";
ctx.Database.ExecuteSqlCommand(
sql,
new SqlParameter("loginName", loginName),
new SqlParameter("password", password));
请注意，我们仍在使用 SqlParameters 来防止 SQL 注入攻击.我们也在使用 T-SQL 方法 QuoteName 在我们生成的 SQL 中正确引用；但是这个方法只是将任何 [ 字符(在第一次调用中)或 ' 字符(在第二次调用中)加倍.SQL 注入攻击还有许多其他向量，因此仅依靠 QuoteName 是不够的.
Note we're still using the SqlParameters to prevent SQL injection attacks. We are also using the T-SQL method QuoteName to do proper quoting in the SQL we are generating; but this method simply doubles any [ characters (in the first call) or ' characters (in the second). There are many other vectors for a SQL injection attack, so merely relying on QuoteName wouldn't be enough.
这篇关于如何使用变量更改sql登录密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
