Create Login Via Sqlcommand - Parameters Not Substituted

I am trying to create a new SQL login on via c#. I have tried the following to set the query params: using (SqlConnection connection = new SqlConnection(sqlServerConnString)) {

Solution 1:

I'm not a C# developer, so I'm trusting that the code you have is correct.

As I state in my comment "You can't use a variable to replace a string literal. The above would try to create a login with the name @databaseUserIdnot the value of @databaseUserId." It would also set the password of that login to be the string '@databasePassword' (again, not the value of @databasePassword).

You'll need to use dynamic SQL to achieve this within your statement. I believe this will work, but I have no way of testing.

using (SqlConnection connection = new SqlConnection(sqlServerConnString))
{
    connection.Open();

    //string addLogin = "CREATE LOGIN [@databaseUserId] WITH PASSWORD = '@databasePassword';";
    string addLogin = "DECLARE @SQL nvarchar(MAX) = N'CREATE LOGIN ' + QUOTENAME(@databaseUserId) + N' WITH PASSWORD = N' + QUOTENAME(@databasePassword,'''') + N';'; EXEC sp_executesql @SQL;";
    using (SqlCommand command = new SqlCommand(addLogin, connection))
    {

        command.Parameters.Add("databaseUserId", System.Data.SqlDbType.NVarChar,128).Value = databaseUserId;
        command.Parameters.Add("databasePassword", System.Data.SqlDbType.NVarChar,128).Value = databasePassword;

        command.ExecuteNonQuery();
    }
}

Note the important use of QUOTENAME here, and that the SQL you call is still parametrised on the application side of things. As you need to use literal strings for the values, you have to inject them (which is normally frowned upon). QUOTENAMEproperly quotes your strings meaning that the exposure to injection is significantly reduced (some as simple of this now will be injection "immune").

So, if someone did try to inject with the login name then the characters would be escaped. For example the value N'L] WITH PASSWORD = 'abc123!"£'; ALTER SERVER ROLE sysadmin ADD MEMBER L;--' the value would be be quoted to the value [L]] WITH PASSWORD = 'abc123!"£'; ALTER SERVER ROLE sysadmin ADD MEMBER L;--], and a login with that (stupid) name would actually be created (provided a value password was supplied). For the password, as it needs to be a string literal, rather than a literal, I use the 2nd parameter, to tell SQL Server what character to quote the string with.