29

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?

Community
  • 1
  • 1
user2155873
  • 559
  • 1
  • 5
  • 12

3 Answers3

31

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).

binary and varbinary.

And, as @p.s.w.g says, you pass it from code by placing it into a suitable parameter.

Damien_The_Unbeliever
  • 227,877
  • 22
  • 326
  • 423
6

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
p.s.w.g
  • 141,205
  • 29
  • 278
  • 318
  • 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? – user2155873 Mar 20 '13 at 08:02
  • @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). – p.s.w.g Mar 20 '13 at 08:04
  • 1
    @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? – Damien_The_Unbeliever Mar 20 '13 at 08:04
0

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.

double-beep
  • 4,567
  • 13
  • 30
  • 40
Andrew Hoffman
  • 752
  • 6
  • 14
  • 1
    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? – Tiago Freitas Leal Mar 31 '18 at 18:40