2

I am newbie in MySQL and I am quite confused en how to insert and after how to retrieve row using this hash in where clause in a varbinary type column.

/*sql*/
CREATE TABLE my_table(hash_column VARBINARY(32));

//php
$hash = "3cd63ba0e52e7c16316bd3b1d33a1bdbc6128fa55f4747c80ac4eab104a9f459";

//this seem to work ok because I can see new item in my_tabl
$sql = "INSERT INTO my_table (hash_column) VALUES ('$hash');


//there is my problem y have tried all this but got 0 result
$sql = "SELECT * FROM my_table WHERE hash_column  = '$hash'";
$sql = "SELECT * FROM my_table WHERE hash_column  = HEX('$hash')";
$sql = "SELECT * FROM my_table WHERE hash_column  = BINARY HEX('$hash')";
$sql = "SELECT * FROM my_table WHERE hash_column  = BINARY '$hash'";

SOLVED: With some ideas from @JuveLeo1906 and @Psi and this post What data type to use for hashed password field and what length? that help me find the right solution.

Right solution here:

$sql = "INSERT INTO my_table (hash_column) VALUES (UNHEX('$hash'));

$sql = "SELECT * FROM my_table WHERE hash_column  = UNHEX('$hash')";
Community
  • 1
  • 1
MTK
  • 3,300
  • 2
  • 33
  • 49
  • why don't you store the hash in a `varchar` instead? – Psi Apr 06 '17 at 19:33
  • `select * from my_table where hash_column = binary('$hash')` should work. – JuveLeo1906 Apr 06 '17 at 19:47
  • @Psi 1.To save space as I read several tips. 2.According to it says here is also faster http://blog.3234.net/20100609/hashbytes-varchar-vs-varbinary-performance/ – MTK Apr 06 '17 at 19:49
  • @JuveLeo1906 I have tried now BINARY('$hash') and not work :( – MTK Apr 06 '17 at 19:52
  • 1
    You don't save space that way, because your hash value is still using 2 bytes for each binary byte because the character codes are stored, not the binary value: `set varbin = '0d0a'` results in a four byte binary containing 0x30443041 (the char codes for "0", "d", "0" and "a"). You should set it this way then: `set varbin = 0x0d0a` without any quotation marks. Try comparing `WHERE hash_column = cast( '$hash' as varbinary )` – Psi Apr 06 '17 at 19:53
  • Thank's for you time @Psi but cast( '$hash' as varbinary ) got to me an error Syntax error or access violation: 1064 You have an error in your SQL syntax; (I using PDO) – MTK Apr 06 '17 at 20:03
  • I just came to another suspicion... did you check for lowercase/uppercase mismatch? (Again: You don't save space at the moment) – Psi Apr 06 '17 at 20:07
  • 1
    @MTK. I believe your issue might be with your column definition, try increasing the length. – JuveLeo1906 Apr 06 '17 at 20:09
  • Not. All hashes are lowercase. – MTK Apr 06 '17 at 20:12
  • **SOLVED:** ... With your ideas I have reached the next solution: using UNHEX('$hash') when insert and UNHEX('$hash') in where clause. That solve also the space earning – MTK Apr 06 '17 at 20:18

0 Answers0