SQL Server 的 varbinary 数据类型可以存储哪些数据? [英] What data can be stored in varbinary data type of SQL Server?

问题描述

我有一个表，其中 userpassword 字段具有 varbinary 数据类型，所以我很困惑我应该以哪种形式将数据保存到 userpassword 字段中，因为当我保存 varchar 数据时，它给了我错误.

I have a table in which the userpassword field have varbinary datatype, So I'm confused that in which form should I save the data into userpassword field because when I save varchar data it gave me error.

推荐答案

varbinary 列可以存储任何内容.要在其中存储字符串，您必须将其强制转换为 varbinary:

A varbinary column can store anything. To store a string in it, you'd have to cast it to varbinary:

declare @t table (id int identity, pwd varbinary(50))
insert into @t (pwd) values (cast('secret' as varbinary(50)))

但是对于密码，varbinary 列通常存储某种哈希值.例如，使用 的 SHA1 哈希HashBytes 函数:

But for a password, a varbinary column usually stores a hash of some kind. For example, a SHA1 hash using the HashBytes function:

insert into @t (pwd) values (HashBytes('sha1', 'secret'));

存储单向哈希而不是真实密码更安全.您可以检查密码是否匹配:

Storing a one-way hash instead of the real password is more secure. You can check if the password matches:

select * from @t where pwd = HashBytes('sha1', 'secret')

但是您无法通过查看表格来检索密码.所以只有最终用户知道他的密码，甚至 DBA 也无法找回.

But there is no way you can retrieve the password by looking at the table. So only the end user knows his password, and not even the DBA can retrieve it.

这篇关于SQL Server 的 varbinary 数据类型可以存储哪些数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！