How to store hash value from C# in SQL Server using sql query

Question

I have problems getting my data type correct. I have a column in a database table that is Binary(64) to hold the salted hash value of a password.

I don't understand how to present my data to SQL Server. I have read that I am supposed to pass the strSaltedPassword.GetHashCode() as NVARCHAR(MAX) but have not been able to make that work...

So next I tried having a parameter first of Binary and then I have tried VarChar

This is my c# code and SQL insert query

//SqlDbType.VarChar = "Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query."

SqlParameter myField = sqlCmd.Parameters.Add("@myField", SqlDbType.VarChar);
myField.Value = strSaltedPassword.GetHashCode();

string sqlquery = "insert into Users ([UserName],[FirstName],[LastName],[EmailAddress],[EmailVerified],[VerifiedTimeStamp],[IsActive],[PasswordHash],[SecurityStamp],[AccountCreateTimeStamp]) values ('" + UserName.Text + "'" + ", '" + FirstName.Text + "'" + ", '" + LastName.Text + "'" + ", '" + Email.Text + "'" + ", 'false', getdate(), 'true', @myField, '" + strGetNewUserSalt + "', + getdate())";

sqlCmd.CommandText = sqlquery;
sqlConn.Open();

sqlCmd.ExecuteNonQuery();

I get this error:

"Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query"

Do I also need to Declare @myField as NVARCHAR (Max) on the SQL side ??

I am a bit out of my element on this and could really use some help.

First fix the SQL Injection attack, you should use SqlCommand parameters! — Richard Schneider
– Richard Schneider, Commented Sep 30, 2015 at 3:37
Yes, you could really use some help. Security is very tricky, I suggest using some already written library. Your approach is open to many attacks. — Richard Schneider
– Richard Schneider, Commented Sep 30, 2015 at 3:40
You might want to consider the uniqueness of the field too , may be. Also, did you consider reading it back. — Nikita Shrivastava
– Nikita Shrivastava, Commented Sep 30, 2015 at 3:41
Okay, I don't know what I don't know. Richard, I will be looking at this page in the mornining... unixwiz.net/techtips/sql-injection.html I appreciate educating me if I am way out of my league with security and do appreciate the sql injection warning... Do you have any leads on "some already written library" ? It doesn't help me with my data type issue however and that is what I also will need to resolve. — john smith
– john smith, Commented Sep 30, 2015 at 3:57

Steven Mays · Accepted Answer · 2015-09-30 18:26:42Z

1

I'm pretty sure you could get away with setting the SqlDbType to VarBinary, like this:

SqlParameter myField = sqlCmd.Parameters.Add("@myField", SqlDbType.VarBinary);

Although for extra points you should completely parameterize your query.

Pros:

Prevent against SQL injection attacks.
Compiled and then cached, so it's faster each subsequent run.

Cons:

None

Here's how I would do this:

var conn = new SqlConnection("Your connection string");

var userName = UserName.Text.Trim();
var firstName = FirstName.Text.Trim();
var lastName = LastName.Text.Trim();
// etc etc etc
// finish these up
var hashValue = strSaltedPassword.GetHashCode();

var sqlCmd =  new SqlCommand("INSERT INTO Users ([UserName],[FirstName],[LastName],[EmailAddress],[EmailVerified],[VerifiedTimeStamp],[IsActive],[PasswordHash],[SecurityStamp],[AccountCreateTimeStamp]) values(@UserName, @FirstName, @LastName, @EmailAddress, @EmailVerified, @VerifiedTimeStamp, @IsActive, @PasswordHash, @SecurityStamp, @AccountCreateTimeStamp", conn);

//implicit conversion - for the lazy
sqlCmd.Parameters.AddWithValue("@UserName", userName);
sqlCmd.Parameters.AddWithValue("@FirstName", firstName);
sqlCmd.Parameters.AddWithValue("@LastName", lastName);

//explicit conversion
sqlCmd.Parameters.Add("@PasswordHash", SqlDbType.VarBinary, 64).Value = hashValue;
// etc etc etc
// finish these up

edited Sep 30, 2015 at 18:26

answered Sep 30, 2015 at 4:37

Steven Mays

3651 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Steven Mays Over a year ago

Np, glad to help. If one of your answers here worked for you you should "accept" it as an answer. That way when someone Google's the same problem you had they have a quick resource to determine the correct path to take.

john smith Over a year ago

I did accept your answer as the most helpful. I am struggling with one aspect of it. I can't use the line: SqlCommand sqlCmd = "INSERT INTO Users ([UserName],...[AccountCreateTimeStamp]) values(@UserName, ... @AccountCreateTimeStamp", conn); (shortened for comment size limit) I get cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlCommand'. I noticed a closing parenthesis in your example but not an opening one. I have tried several things to get this to work to no avail. Any ideas what this issue is ? (I am using Asp.net 4.5, and SqlServer 2012)

Steven Mays Over a year ago

Try doing it this way: var sql = "INSERT INTO Users ([UserName],[FirstName]..." var sqlCmd = new SqlCommand(sql, conn);

john smith Over a year ago

Hi Steven, I tried that before, and on this line: var sqlCmd2 = new SqlCommand(sql, conn2); I got an error on the right side of the equal sign assignment operator. It complains about: "The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand.(string, System.Data.SqlClient.SqlCommand) has some invalid arguments" it's odd to me why is says System.Data.SqlClient.... with a double SqlCommand.SqlCommand.(string after that Thanks in advance for your help ! - John

Steven Mays Over a year ago

It's not letting me copy and paste the code, but I tried this in my visual studio and it compiled. Click the link: dotnetfiddle.net/5ulG8A

|

Community · Accepted Answer · 2017-05-23 12:06:40Z

0

Convert the hash to a Base64 string and store the string in the database.

It's a pretty straightforward operation, providing you with a string representation of the binary hash, which you can store in a database or a text file. Here's an example: https://stackoverflow.com/a/8066500/618649

To compare the hash, you can just convert the hash of the incoming password to a Base64 string and do a case-sensitive string comparison. Alternatively, you could convert the string from the database back to a byte array and compare that to the binary hash of the incoming password.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Sep 30, 2015 at 4:52

Craig Tullis

10.7k2 gold badges23 silver badges22 bronze badges

1 Comment

john smith Over a year ago

Will look at this also Craig. Thank you.

Collectives™ on Stack Overflow

How to store hash value from C# in SQL Server using sql query

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related