0

I have a stored procedure along these lines:

CREATE DEFINER=`username`@`localhost` PROCEDURE `UserUpdate`(IN inUserId INT, IN inUserFname VARCHAR(40), IN inUserLname VARCHAR(40), IN inUserEmail VARCHAR(40), IN inUserPhone VARCHAR(40), IN inUserPassword BINARY(60))
BEGIN
    IF inUserPassword='' THEN
        UPDATE `users`
        SET fname=inUserFname, lname=inUserLname, email=inUserEmail, phone=inUserPhone, datemodified=NOW()
        WHERE id=inUserId;
    ELSE
        UPDATE `users`
        SET fname=inUserFname, lname=inUserLname, email=inUserEmail, phone=inUserPhone, password=inUserPassword, datemodified=NOW()
        WHERE id=inUserId;
    END IF;
END

The information is coming from a HTML Form/PHP script. If the user has not updated their password, then the inUserPassword remains blank and IF portion of the procedure should run. If the user has updated their password in the form, then the ELSE portion should run.

The password is stored as a bcrypt hash and the column in the database is set as BINARY(60).

The problem that I'm running into is that I can't trigger the IF if the BINARY is used for the password. If I change the password column to VARCHAR, the above query works just fine. I understand that the binary value is not the same as ASCII or Unicode value, but for nothing, as in the above code, shouldn't it be the same?

How should I go about fixing it? Thanks for any help.

equinoxe5
  • 314
  • 1
  • 17
  • can you just convert both to binary? IF binary(inUserPassword)=binary('') THEN – MBeale Sep 23 '20 at 17:35
  • Specify the collation for empty string explicitly - like `IF inUserPassword = '' COLLATE 'utf8mb4_bin' THEN` (specify correct collation). – Akina Sep 23 '20 at 17:43
  • Thank you. Neither of these changes unfortunately change the behaviour. – equinoxe5 Sep 23 '20 at 17:53
  • For what it's worth, [password hashes](https://www.php.net/manual/en/function.password-hash.php) from php are usually stored in tables as `VARCHAR(255)`, not `BINARY`, data. [Password verification](https://www.php.net/manual/en/function.password-verify.php) accepts those string, not binary. Consider changing your data type. – O. Jones Sep 23 '20 at 19:40
  • @O.Jones Thanks. I was going based on this answer: https://stackoverflow.com/a/5882472/2912859. I might as well switch back to VARCHAR, but I'm also still curious what's causing the problem above. – equinoxe5 Sep 23 '20 at 19:45
  • 1
    That stuff is really ancient. As time goes by and cybercreeps get smarter, password hashes get longer, You want your password hash column declared as `password VARCHAR(255) CHARACTER SET ascii` to work in the present and to be future proof. Really. – O. Jones Sep 23 '20 at 19:49
  • @O.Jones roger that! – equinoxe5 Sep 23 '20 at 19:52

0 Answers0