Saving binary data to MySQL- bin2hex vs base64_encode

Question

I have an encryption class to encrypt a user's password, and the end result is a binary string.

I want to save this to MySQL as the user's password, but because MySQL doesn't play very well with binary data, I wanted to convert it to a more database-friendly format.

I seem to be able to encrypt/decrypt just fine saving and pulling values from MySQL as long as I either use bin2hex or base64_encode beforehand and hex2bin or base64_decode afterward.

My question is, should there be any reason why I should choose one over the other? Is any 1 more reliable over another? Is any 1 faster than another?

Thank you.

About bin2hex stackoverflow.com/questions/2558453/… . bin2hex may use more memory than base64 operations, however base64 strings are likely going to be longer due to padding. You should run local research and measure differences in memory usage and execution time. — DeDee
– DeDee, Commented Aug 30, 2015 at 13:16
@DeDee Base64 is 6 bits per character while hexadecimal is just 4 bits per character. Unless you encode just up to 2 bytes, Base64 is always shorter than hexadecimal. — Gumbo
– Gumbo, Commented Aug 30, 2015 at 15:16

NovaDenizen · Accepted Answer · 2015-08-30 15:10:34Z

2

You should be using a BINARY or VARBINARY type in your database and a prepared statement to insert. Then you don't have to do any conversion at all. It's the best of all worlds as far as I can tell. The data size on the wire will be compact, and no extra memory will be used in the storage or in your php script.

answered Aug 30, 2015 at 15:10

NovaDenizen

5,38517 silver badges29 bronze badges

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

1 Comment

Andrew Over a year ago

what about max length of VARBINARY vs max length of say MEDIUMTEXT?

Jonathan · Accepted Answer · 2017-02-14 16:50:27Z

Storing a users password is a bad idea. It is better to hash a users password and store the hash.

password_hash($password, PASSWORD_DEFAULT)

that function returns a string that can simply be stored in the database.

To validate it:

if (password_verify($userPassword, $hash)) {
    // Login successful.
}

Also see this paragon site for a more secure password hashing method making use of libsodium (present in php 7.2+):

// Password hashing:
$hash_str = \Sodium\crypto_pwhash_str(
    $password,
    \Sodium\CRYPTO_PWHASH_OPSLIMIT_INTERACTIVE,
    \Sodium\CRYPTO_PWHASH_MEMLIMIT_INTERACTIVE
);
// Password verification:
if (\Sodium\crypto_pwhash_str_verify($hash_str, $password)) {
    // recommended: wipe the plaintext password from memory
    \Sodium\memzero($password);

    // Password was valid.
} else {
    // recommended: wipe the plaintext password from memory
    \Sodium\memzero($password);

    // Password was invalid.
}

Collectives™ on Stack Overflow

Saving binary data to MySQL- bin2hex vs base64_encode

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related