Using ExecuteSqlCommand makes my password not work when inserting to DB

Question

I'm using MySql DB and trying to create a user there. The password is a hash. When I use the Context.Useraccount.add(User) and Context.SaveChanges() adding to the database works just fine, but using ExecuteSqlCommmand makes the password not work.

var sql = @"INSERT INTO useraccount
           (UserId,UserName,Password,CustomerId,PasswordSalt,CreatedDate)
                VALUES
                (@UserId, @UserName,@Password,@CustomerId, @PasswordSalt, @CreatedDate)";

        int rows = _context.Database.ExecuteSqlCommand(
                sql,
                new MySqlParameter("@UserId", user.UserId),
                new MySqlParameter("@UserName", user.UserName),
                new MySqlParameter("@Password", user.Password),
                new MySqlParameter("@CustomerId", user.CustomerId),
                new MySqlParameter("@PasswordSalt", user.PasswordSalt),
                new MySqlParameter("@CreatedDate", MySQLFormatDate));

It gives this exception: {MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect string value: '\x90]\x0E\x80\xB1\xFF...' for column 'Password' at row 1 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect string value: '\x90]\x0E\x80\xB1\xFF...' for column 'Password' at row 1

I tried changing the Column value in the DB to varbinary (from varchar) and then I can insert it, but it becomes a blob in the DB and it doesn't work when I try to read it again.

How can I send the hash correctly to the DB?

Edit--- code for creating the hash

 private static void CreatePasswordHash(string password, out byte[] passwordHash, out byte[] passwordSalt)
    {
        if (password == null) throw new ArgumentNullException("password");
        if (string.IsNullOrWhiteSpace(password)) throw new ArgumentException("Value cannot be empty or whitespace only string.", "password");

        using (var hmac = new System.Security.Cryptography.HMACSHA512())
        {
            passwordSalt = hmac.Key;
            passwordHash = hmac.ComputeHash(System.Text.Encoding.UTF8.GetBytes(password));
        }
    }

edit2--- password types are byte[]

This is if I change the Password and SaltedPassword types in the database to VarBinary. When I use VarChar it gives the error which I pasted before and nothing gets sent to the DB.

Text Image

UPDATE -- SOLVED

So I had the wrong value in the modelbuilder.entity for the password I had VarChar there when it should've been VarBinary. Somehow it worked with Context.Useraccount.add(User) and Context.SaveChanges() though.

Thanks to everyone for the help!

There's obviously an issue with how you are hashing the password. Please post the hashing code. — Dan Wilson, Oct 11 '18 at 15:12
What is the datatype of `user.Password`? What is an example value? And what do you see when you check an example value in the database? — Joel Coehoorn, Oct 11 '18 at 16:31

Studwell · Answer 1 · 2018-10-11T15:26:27.663

Paraphrasing the answer from this answer since it applies to your situation as well

"Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?

MySql's utf8 encoding only supports characters that can be encoded in 3 Bytes. Whatever character '\x90]\x0E\x80\xB1\xFF' is probably takes more than 3 bytes to encode and that is why MySql is yelling at you.

Verify that whatever method you are using to encode these passwords is limited to a utf8 format and that should prevent this error from re-occuring.

I can't see your method for hashing these, but this will ensure you are encoding using utf8

   byte[] salt = // Salt
   byte[] encodedValue = Encoding.UTF8.GetBytes(value);
   byte[] saltedValue = value.Concat(salt).ToArray();
   byte[] hashedValue = SHA256Managed().ComputeHash(saltedValue);

score 0 · Accepted Answer · answered Oct 11 '18 at 17:38

0

Password hashes are byte arrays, and cannot be stored in a C# string. They also have to be stored in a BINARY (or VARBINARY) column in the database, not a VARCHAR column.

but it becomes a blob in the DB and it doesn't work when I try to read it again

To verify the user's password, you should read back the password salt (as a byte[]), hash the (plain text) password with the same salt, then compare the newly-generated hash to the password hash retrieved from the database (as a byte[]). Do not try to convert the password hash back to a C# string.

answered Oct 11 '18 at 17:38

Bradley Grainger

27,458
4
91
108

I'm only doing this: var user = _context.Useraccount.SingleOrDefault(x => x.UserName == username) When I'm trying to authenticate And I get this error -----------------{System.InvalidOperationException: An exception occurred while reading a database value for property 'Useraccount.Password'. The expected type was 'System.Byte[]' but the actual value was of type 'System.Byte[]'. ---> System.InvalidCastException: Unable to cast object of type 'System.Byte[]' to type 'System.String'. – Tapdance Oct 11 '18 at 17:55
But as mentioned before if I use Context.Useraccount.add(User) and Context.SaveChanges() everything works fine and there is no values with black backgrounds in the text in DB... – Tapdance Oct 11 '18 at 18:00
You shouldn't be trying to deserialize `Password` back to a `string`; you may need to change your `User` model to change the type of the property to a `byte[]`. (I would also rename the property to `PasswordHash` to make it clear you're not storing _actual_ passwords.) – Bradley Grainger Oct 11 '18 at 18:01
Passwords are of type byte[]. I'm not trying to deserialize it to a string. Just trying to authenticate. – Tapdance Oct 11 '18 at 18:28
Please edit your question to include the code that's causing the error, and the exception details with the full call stack. – Bradley Grainger Oct 11 '18 at 18:32

Using ExecuteSqlCommand makes my password not work when inserting to DB

2 Answers2