我可以在视图中连接虚拟列吗？

发布于 2024-08-23 07:42:42 字数 689 浏览 13 评论 0原文

不幸的是，我在数据库中有明文密码。我想尽可能少地传递这些明文值，例如比较和更新。为此，我尝试创建用户表的视图，该视图不包括明文密码，而是提供该密码的哈希值。

这是我当前的 SQL Server 视图，它不起作用：

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt
       HashBytes('SHA1', PasswordSalt + u.Password) AS PasswordHash
FROM dbo.Users AS u

我很高兴听到此方法的替代方案，但否则问题似乎是将虚拟列 PasswordSalt 与......任何内容连接起来。例如，以下简单视图可以工作：

SELECT u.Login AS Column1, u.Login + 'b' AS Column2

但这个视图不行：

SELECT u.Login AS Column1, Column1 + 'b' AS Column2

我从 Management Studio 收到的错误是

列名“Column1”无效。

预先感谢您对我做错了什么的任何想法！

原文

Unfortunately, I have plaintext passwords in a database. I want to pass these plaintext values around as little as possible for, say, comparisons and updates. To this end, I'm trying to create a view of my Users table that excludes the plaintext passwords and instead provides a hashed value of that password.

Here's my current SQL Server view, which doesn't work:

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt
       HashBytes('SHA1', PasswordSalt + u.Password) AS PasswordHash
FROM dbo.Users AS u

I'd be happy to hear about alternatives to this approach, but otherwise the problem seems to be concatenating the virtual column PasswordSalt with.. anything. For instance, the following simple view works:

SELECT u.Login AS Column1, u.Login + 'b' AS Column2

but this one does not:

SELECT u.Login AS Column1, Column1 + 'b' AS Column2

The error I'm receiving from Management Studio is

Invalid column name 'Column1'.

Thanks in advance for any ideas about what I'm doing wrong!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

断爱 2024-08-30 07:42:42

出现此问题的原因是语句的 FROM 子句指示要选择的数据来自 Users 表，但 SELECT 部分引用了名为 PasswordSalt 的列。 SQL Server 在 Users 表中找不到具有此名称的列，因此出现错误。

替代方法可能是在子查询中生成 Salt。例如

SELECT x.PasswordSalt, HashBytes('SHA1', x.PasswordSalt + x.Password) AS PasswordHash FROM ( SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt, 密码来自 dbo.Users) x

回复收藏 0 原文

梦与时光遇 2024-08-30 07:42:42

怎么样

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt 
       HashBytes('SHA1', CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) + u.Password) AS PasswordHash 
FROM dbo.Users AS u

What about

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt 
       HashBytes('SHA1', CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) + u.Password) AS PasswordHash 
FROM dbo.Users AS u

回复收藏 0 原文

~没有更多了~