Jump to content

Archived

This topic is now archived and is closed to further replies.

anson5

Inserting binary string to MySQL BINARY column

Recommended Posts

I'm planning on storing RIPEMD-160 passphrase hash's raw binary data in MySQL as BINARY(20).  Right now, I'm not sure if one of the following INSERT approach is more preferrable and more efficient:

 

Approach (1) - Using mysqli::real_escape_string()

=================================================

 

$binhash = $mysqli->real_escape_string(hash('ripemd160', $passphrase, true));
$mysqli->query("INSERT INTO testtbl (Passphrase) VALUES ('" . $binhash . "')");

 

This approach saves hash() from converting raw binary data to hexadecimal format, but need to process and escape the binary string.

 

Approach (2) - Using UNHEX() on MySQL

=====================================

 

$hexhash = hash('ripemd160', $passphrase);
$mysqli->query("INSERT INTO testtbl (Passphrase) values (UNHEX('" . $hexhash . "'))");

 

This approach needs hash() to convert binary data to hexadecimal but no need for escaping the string and it put extra load on MySQL to convert the hexadecimal back to binary.

 

Share this post


Link to post
Share on other sites

This is a 40 charecter string. I would sugest you store this information in this format.

 

Desmond.

 

Share this post


Link to post
Share on other sites

There are two reasons for storing hash in binary format:

[*]It saves half the storage size, for a 160-bit hash, it's BINARY(20) vs CHAR(40)

[*]Operations on BINARY is faster according to benchmarks

 

So my question will be which one will be faster:

[*]mysqli->real_escape_string() on a 20 byte binary string (note that I used hash($algo, $passwd, true) to output raw binary data), or

[*]telling hash() to output hexadecimal character string (conversion from binary needed) plus UNHEX on MySQL?

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.