2

This salt messing up my SELECT command:

p‚ÙÕ†¤éÿ5xÃø¤ü¥–ä™m›|§Éá\0yå–e

decoded output:

p‚ÙÕ†¤éÿ5xÃø¤ü¥–ä™m›|§Éá\0yå–e

Without salt:

mysql> SELECT userid, username FROM user;
+--------+--------------+
| userid | username     |
+--------+--------------+
|      1 | user1        |
|      2 | user2        |
|      3 | user3        |
|      4 | user4        |
+--------+--------------+
4 rows in set (0.00 sec)

With salt:

mysql> SELECT userid, username, salt FROM user;
+--------+--------------+----------------------------------+
| userid | username     | salt                             |
+--------+--------------+----------------------------------+
|      1 | user1        | ]ææ=°ù¡£YÒp£'Rm§Éá yåe | |
|      3 | user2        | ¸ÀçqµgsN\ôü¥ä
ɪÓñ1r¦ôyr$ÅK |
|      4 | user4        | \7øþ ÇãÙr|ú&eå¡%»
                                           yYRìÚ¬E |
+--------+--------------+----------------------------------+
4 rows in set (0.00 sec)

Function I'm using for getting my salt generated:

$salt = mcrypt_create_iv(32, MCRYPT_DEV_RANDOM);

Escape it with:

$salt = mysql_real_escape_string($salt);

Problem: User2's row is never shown when SELECTing the 'salt'. Anyone why? Related problem: User3 also has newline, it's kinda messing up my table, probably same problem as above.

What I have tried with no luck: I have googled each word with "mysql" end of it. I didn't find anything..

Thanks.

Alice
  • 701
  • 1
  • 5
  • 17
  • 1
    The salt is binary data and not intended to be output as is to a terminal. Most likely you've got some control character in there deleting the line with userid 2. Why are you trying to print the salt? – lc. Jul 05 '13 at 17:26
  • @lc. I just wanted to know if my sign up was working properly, and then I realize there is no user2 when doing `SELECT *`. Thanks for your answer I'll check it right away and try replace control characters with something else. – Alice Jul 05 '13 at 17:32
  • Why would you have binary "salt" values in the first place? These are almost always [base64 encoded](http://php.net/manual/en/function.base64-encode.php) to avoid character set issues. – tadman Jul 05 '13 at 18:04
  • @tadman I'd disagree - why go to the trouble of encoding and decoding your data when it's really supposed to be bytes everywhere you actually use it. The only reason I can see to store as base-64 is for convenience when you're manually querying, but the app will likely always use the raw bytes. As long as your app can work with raw byte arrays, I'd leave it that way in the database. – Joe Enos Jul 05 '13 at 18:54
  • @JoeEnos If this is some kind of cryptographic salt, it doesn't need to be 8-bit. With a long base64 string you will have more than enough randomness that it's not an issue. Binary data of this sort just gets in the way and is extremely annoying to deal with. Where the data is intrinsically binary, like a `BLOB`, it's unavoidable. In this case having it binary is probably pointless. – tadman Jul 05 '13 at 19:32

2 Answers2

1

Looks like you can query binary columns with the hex function and it will give you a hex representation of the bytes, which won't funk up your output:

select userid, username, hex(salt) from user;

link to MySQL binary documentation

Joe Enos
  • 39,478
  • 11
  • 80
  • 136
  • Anyway to do a `SELECT *` with this? So I get every column and the salt in HEX. – Alice Jul 05 '13 at 19:21
  • `UPDATE user SET salt=HEX(salt)` will switch it to hex-encoded values, or if you prefer `TO_BASE64(salt)` will use the more efficient base64 encoding method. Then you can `SELECT *` but will have to decode the `salt` column values in your application. – tadman Jul 05 '13 at 19:34
0

Actually it should be no problem to store and retrieve binary data in your MySql database. Just make sure you escape it properly for the needed target system. This means:

To store the binary string in the MySql database, you need an INSERT query. Escape the binary string with mysqli_real_escape_string() before adding it to the query, or use parametrized queries. BTW mysql_* functions are deprecated, you should switch to mysqli_* functions or to PDO.

When reading the string from the database, you will get back the original unescaped binary string. To display this string you need to escape it for HTML output, the function htmlspecialchars() is a good choice then.

You seem to give out the result in some type of console, depending on whether this is your own code running, you can escape it correctly for this type of output, or you simply have to keep in mind that what you see is not what you got. The tool phpMyAdmin is circumventing this problem by giving out the text [BLOB - 32Bytes].

A quick note: If you need the salt for hashing passwords, you can make your life easier if you use an algorithm like BCrypt. PHP's own implementation password_hash() will then include the password specific salt in the hash-value itself, you could do without a user specific salt then.

martinstoeckli
  • 23,430
  • 6
  • 56
  • 87