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` http://stackoverflow.com/questions/2558453/why-use-bin2hex-when-inserting-binary-data-from-php-into-mysql?rq=1 . `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, Aug 30 '15 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, Aug 30 '15 at 15:16

score 2 · Accepted Answer · answered Aug 30 '15 at 15:10

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 '15 at 15:10

NovaDenizen

5,089
14
28

what about max length of VARBINARY vs max length of say MEDIUMTEXT? – Andrew Dec 29 '15 at 08:46

score 1 · Answer 2 · answered Feb 14 '17 at 16:50

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.
}

Saving binary data to MySQL- bin2hex vs base64_encode

2 Answers2