.net - LinqToSql 和 HashBytes

Question

我需要执行此 Sql 命令(在管理工作室中运行良好):

select * from Users where Login = 'test' and PasswordHash = HashBytes('SHA1', 'test')

我写了这个 c# linqtosql:

var user = db.ExecuteQuery<User>("select * from Users where Login = {0} and PasswordHash = HashBytes('SHA1', {1})", loginTextBox.Text.Trim(), passwordPasswordBox.Password).SingleOrDefault();

但它从来没有用过!!

谁能帮帮我？

谢谢!

这是 DataContext 的日志:

select * from Users where Login = @p0 and PasswordHash = HashBytes('SHA1', @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [test]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [test]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

Answer 1

我通过 db.Log = Console.Out 分析了它，结果是:

-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

select * from Users where Login = @p0 and PasswordHash = HashBytes('SHA1', @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Admiral Trask]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Arutha]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

看着这个，我想象问题是参数是 NVarChar 而不是 varchar - 所以取散列(二进制操作) 是不同的。如果您将其散列为varchar，您应该在HASHBYTES 之前将字符串转换为varchar。

例如以下作品:

var user = db.ExecuteQuery<User>(@"select * from Users where Login = {0}
        and PasswordHash = HashBytes('SHA1', CAST({1} as varchar(40)))", cn, pw)
        .SingleOrDefault();

一个更简单的例子是:

SELECT HASHBYTES('SHA1','12345'), HASHBYTES('SHA1',N'12345')