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.
Add a comment
|
4 Answers
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)))
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')
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.
2 Comments
GorvGoyl
can I store raw files like an audio file?
John Smith
@JerryGoyal yes. Another thing that is commonly done, is to store larger files on disk, and just store the file paths in the DB though
You will need to explicitly cast the VARCHAR.
SELECT CAST(N'Test' as VARBINARY)
SQL Server error message says.
Implicit conversion from data type varchar to varbinary is not allowed.
Comments
SQL Server requires an explicit conversion from varchar to varbinary, as per the big table on CAST and CONVERT in MSDN
The table will have a varbinary column to store hashed values as per sys.sql_logins
Comments
SELECT CAST(N'Test' as VARBINARY)
------you have to give it a size ---------->
SELECT CAST(N'Test' as VARBINARY(30))
