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.
我有一个表，其中userpassword字段有varbinary数据类型，所以我很困惑，我应该以哪种形式将数据保存到userpassword字段，因为当我保存varchar数据时它给了我错误。
3 个解决方案
#1
21
A varbinary column can store anything. To store a string in it, you'd have to cast it to varbinary:
varbinary列可以存储任何内容。要在其中存储字符串，您必须将其强制转换为varbinary：
declare @t table (id int identity, pwd varbinary(50))
insert into @t (pwd) values (cast('secret' as varbinary(50)))
But for a password, a varbinary column usually stores a hash of some kind. For example, a SHA1 hash using the HashBytes function:
但是对于密码，varbinary列通常存储某种类型的哈希。例如，使用HashBytes函数的SHA1哈希：
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')
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.
但是你无法通过查看表来检索密码。因此，只有最终用户知道他的密码，甚至DBA都无法检索密码。
#2
1
You will need to explicitly cast the VARCHAR.
您需要显式转换VARCHAR。
SELECT CAST(N'Test' as VARBINARY)
SQL Server error message says.
SQL Server错误消息说。
Implicit conversion from data type varchar to varbinary is not allowed.
不允许从数据类型varchar到varbinary的隐式转换。
#3
1
SQL Server requires an explicit conversion from varchar to varbinary, as per the big table on CAST and CONVERT in MSDN
根据CAST上的大表和MSDN中的CONVERT，SQL Server需要从varchar到varbinary的显式转换
The table will have a varbinary column to store hashed values as per sys.sql_logins
该表将有一个varbinary列，用于存储根据sys.sql_logins的散列值
#1
21
A varbinary column can store anything. To store a string in it, you'd have to cast it to varbinary:
varbinary列可以存储任何内容。要在其中存储字符串，您必须将其强制转换为varbinary：
declare @t table (id int identity, pwd varbinary(50))
insert into @t (pwd) values (cast('secret' as varbinary(50)))
But for a password, a varbinary column usually stores a hash of some kind. For example, a SHA1 hash using the HashBytes function:
但是对于密码，varbinary列通常存储某种类型的哈希。例如，使用HashBytes函数的SHA1哈希：
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')
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.
但是你无法通过查看表来检索密码。因此，只有最终用户知道他的密码，甚至DBA都无法检索密码。
#2
1
You will need to explicitly cast the VARCHAR.
您需要显式转换VARCHAR。
SELECT CAST(N'Test' as VARBINARY)
SQL Server error message says.
SQL Server错误消息说。
Implicit conversion from data type varchar to varbinary is not allowed.
不允许从数据类型varchar到varbinary的隐式转换。
#3
1
SQL Server requires an explicit conversion from varchar to varbinary, as per the big table on CAST and CONVERT in MSDN
根据CAST上的大表和MSDN中的CONVERT，SQL Server需要从varchar到varbinary的显式转换
The table will have a varbinary column to store hashed values as per sys.sql_logins
该表将有一个varbinary列，用于存储根据sys.sql_logins的散列值