无法从web服务在数据库中创建和更新用户

Question

这可能是重复的问题，但我无法找到解决方案。我有一个web服务编码在c#和。net核心通过我试图创建一个用户在数据库(SQL Server管理工作室)。当我尝试通过SSMS创建用户时，查询工作得很好，但它不能通过函数工作。函数如下:

public async Task CreateUserNameQuery(string username, string password)
{ 
object[] sqlParams = {new SqlParameter("@username", username),  new SqlParameter("@password", password)};


string query3 = "IF USER_ID ('{@username}') IS NULL " + "n" +
"CREATE USER {@username} WITH PASSWORD=N'{@password}', DEFAULT_SCHEMA=[dbo] " + "n" +
"ELSE " + "n" +
"ALTER USER {{@username}} WITH PASSWORD=N'{{@password}}', DEFAULT_SCHEMA=[dbo] " + "n" +
"GO " + "n" +
"sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'{{@username}}' " + "n" +
"GO ";
var result = await Database.ExecuteSqlRawAsync(query3, sqlParams);


Database.SetCommandTimeout(600);
}

下面是我得到的例外:

Input string was not in a correct format.

at System.Text.StringBuilder.FormatError()
at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args)
at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args)
at System.String.Format(String format, Object[] args)
at Microsoft.EntityFrameworkCore.Storage.Internal.RawSqlCommandBuilder.Build(String sql, IEnumerable`1 parameters)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)

Answer 1

我们把用户名中的非a-z字符去掉怎么样?结果是很难注射任何东西。这确实引入了一个规则，即用户名必须是ascii a-z，但是真的有人需要'DROP TABLE Students;--的用户名吗?(如果他们真的这样做，见底部)

public async Task CreateUserNameQuery(string username, string password)
{ 
username = Regex.Replace(username, "[^a-z]", "");

string query3 = $@"
IF DATABASE_PRINCIPAL_ID('{username}') IS NULL
CREATE USER {username} WITH PASSWORD={{0}}, DEFAULT_SCHEMA=[dbo] 
ELSE
ALTER USER {username} WITH PASSWORD={{0}}, DEFAULT_SCHEMA=[dbo]";
await Database.ExecuteSqlRawAsync(query3, username, password);
await Database.ExecuteSqlInterpolatedAsync("EXECUTE sys.sp_addrolemember @rolename = N'db_datareader', @membername = {username}");
}

我很确定你可以参数化密码，因为它通常是作为字符串常量呈现的——它只是作为标识符呈现的用户名——但我不能测试它。如果不行，请告诉我

最后注意;你不能发送GO到sql服务器;SSMS使用GO作为分隔符来分解脚本，您也应该这样做(这里我做了两个查询来模拟GO的任何一侧)

---

如果你真的需要你的用户名包含各种古怪的东西，也许最简单的方法是让数据库为你引用它，然后在字符串中使用引号的值。在您的领域模型中选择任何具有字符串属性的合适对象，例如Order，并执行一个原始查询，将用户名引用到字符串属性中(让我们想象orderreference)

var x = (await context.Order.AsNoTracking().FromSqlInterpolated($"SELECT -1 as OrderId, QUOTENAME({username}) as OrderReference").FirstAsync()).OrderReference;

x现在包含，对于abc[]def的输入，[abc[]]def]可以安全地连接到不能参数化的位置的sql