Am looking to use an approach in saving passwords that requires using byte array as in this post
So which data type should i use in sql server to save byte array? and how can i pass and retrieve the byte array using SqlCommand?
Add a comment
|
3 Answers
If it's always going to be the same length, then binary(length) would be suitable. If it's going to vary in length, use varbinary(maxlength).
And, as @p.s.w.g says, you pass it from code by placing it into a suitable parameter.
answered Mar 20, 2013 at 8:02
Damien_The_Unbeliever
241k28 gold badges358 silver badges470 bronze badges
1 Comment
Saul Muñoz Garcia
Thanks for the answer! Really helped me a lot. I got confused at the beginning and think is important to remark that varbinary(maxlength) doesn't refer to varbinary(max). 'maxlength' refers to the maximum posible length for your hashed passwords
Just use a byte[] the same way you would any other parameter, specifying SqlDbType.Binary as the parameter type. Here a sample in C#
// Generate your password hash some way
byte[] passwordHash = new byte[] { 0x0, 0x1, 0x2 ... };
...
command.Parameters.Add("@passwordHash", SqlDbType.Binary);
command.Parameters["@passwordHash"].Value = passwordHash;
Or if you prefer VB.NET
' Generate your password hash some way
Dim passwordHash As Byte() = New Byte() { &H0, &H1, &H2 ... }
...
command.Parameters.Add("@passwordHash", SqlDbType.Binary)
command.Parameters("@passwordHash").Value = passwordHash
3 Comments
Akkad
is it fine to use binary sql data type? as i googled some people telling its use for big binary files like images, do you agree with that?
p.s.w.g
@user2155873 Use
VARBINARY(MAX) for files which are somewhat large or are of varying length. For password hashes use BINARY(32) (or however long your hash is).Damien_The_Unbeliever
@user2155873 - given that you can declare a
binary(5), that allocates storage for 5 bytes, would you say that that was only suitable for storing a big file?I'd recommend using nvarchar(45) and Base64 your 32 bytes into a string. This is the standard way of saving a hash.
Alternatively you could do nvarchar(64) and store it as a hex string.
1 Comment
Tiago Freitas Leal
I've read the Microsoft docs about this subject and never saw the recommendation to convert the hash to Base64. Anyway, why not saving the hash as a byte array?