rlcrews rlcrews - 4 months ago 14
C# Question

How to change a sql login password with variables

I am trying to update the password for an existing SQL login using Alter LOGIN

I know the following works

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;


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)

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;


Can anyone help me understand how to update a login's password in sql using a variable instead of a fixed value?

thanks in advance

Update

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;
}
}


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;
}
}


I then generate a sqlException Incorrect syntax near PASSWORD.

Update2

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));


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.

EDIT

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);


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.

-Thanks

Answer

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));

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.

UPDATE

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));

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.

Comments