2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
john smith
  • 27
  • 6
  • Hope [Converting a VARCHAR to VARBINARY](http://dba.stackexchange.com/a/63745) helps – Mohit S Sep 30 '15 at 03:36
  • 4
    First fix the SQL Injection attack, you should use SqlCommand parameters! – Richard Schneider Sep 30 '15 at 03: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 Sep 30 '15 at 03:40
  • You might want to consider the uniqueness of the field too , may be. Also, did you consider reading it back. – Nikita Shrivastava Sep 30 '15 at 03:41
  • Okay, I don't know what I don't know. Richard, I will be looking at this page in the mornining... http://www.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 Sep 30 '15 at 03:57
  • @johnsmith Just a bit of clarification for you--and you're obviously going to read up on this--but all the talk about the SQL injection vulnerability simply refers to the way you are concatenating direct user into into your SQL query. A crafty attacker could substitute SQL that deletes or otherwise manipulates data, runs discovery queries against your database schema, selects out sensitive data, and more. If you use parameterized queries you avoid those vulnerabilities. – Craig Tullis Sep 30 '15 at 05:06
  • Thanks for the clarification Craig. (and Richard for bringing it up) I honestly did not have much sql work during the descovery of sql injection back in the day. I do remember (and actually saw a small photo of this) the guy in Silicon Valley that printed a large sql query across his car bumper so he could inject into an electronic freeway roadsign and display custom text. –  john smith Sep 30 '15 at 14:07

2 Answers2

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:

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

Cons:

  1. 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
Steven Mays
  • 365
  • 1
  • 12
  • 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. – Steven Mays Sep 30 '15 at 14:34
  • 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) –  john smith Sep 30 '15 at 16:11
  • Try doing it this way: var sql = "INSERT INTO Users ([UserName],[FirstName]..." var sqlCmd = new SqlCommand(sql, conn); – Steven Mays Sep 30 '15 at 16:47
  • 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 –  john smith Sep 30 '15 at 17:29
  • It's not letting me copy and paste the code, but I tried this in my visual studio and it compiled. Click the link: https://dotnetfiddle.net/5ulG8A – Steven Mays Sep 30 '15 at 18:02
  • Thank you Steven !!! It works for me now also. I am not 100% sure of what I was seeing (or doing !!! :( but your code now works. I will study it closely. btw, I really did enjoy reading this about sql injection (http://www.unixwiz.net/techtips/sql-injection.html), they take a step by step approach and I learned a lot. (Thanks Richard.) –  john smith Sep 30 '15 at 18:20
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.

Community
  • 1
  • 1
Craig Tullis
  • 9,939
  • 2
  • 21
  • 21