Monday 9 July 2012

Ambiguous output from mysql command-line client

You'd think that a command-line tool would show you when something funny is going on. I was scratching my head for an hour, wondering why, when PHP loaded $_SESSION from my database, it seemed to ignore one of the object members. A private member in fact. Here's what I saw:

mysql> select * from sessions where data <> '';
+-----+----------------------------------------------------------------------------------------+-----------+
| id  | data                                                                                   | last_used |
+-----+----------------------------------------------------------------------------------------+-----------+
| foo | user|O:4:"User":2:{s:11:" User email";s:18:"dev@bpj-code.co.za";s:8:"verified";b:1;}   | NULL      |
+-----+----------------------------------------------------------------------------------------+-----------+

See those gaps around 'User'? Those aren't spaces! (And nor are they tabs.) They are, in fact, NUL characters:

Note:
Object's private members have the class name prepended to the member name; protected members have a '*' prepended to the member name. These prepended values have null bytes on either side.
 Why did this matter? Because I had made a change to the web pages that would need the serialized User object to have an id member, and it seemed easier to hand-hack the database's 3 entries in the sessions table than to write a ream of backwards-compatibility code that would conjure up the right id at the right time, when needed. "Easy!" I thought. "Not on your chinny-chin-chin," the computer replied:

mysql> update sessions set data = 'user|O:4:"User":3:{s:11:" User email";s:18:"dev@bpj-code.co.za";s:8:"verified";b:1;s:2:"id";s:2:"16";}' where id = 'foo';
Query OK, 1 row affected (0.00 sec)

Looks encouraging! Refresh page and check results:

mysql> select * from sessions where data <> '';
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| id  | data                                                                                                                                                                      | last_used           |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| foo | user|O:4:"User":5:{s:11:" User email";N;s:8:"verified";b:1;s:11:" User email";s:18:"dev@bpj-code.co.za";s:2:"id";s:2:"16";s:11:"permissions";s:19:"9223372036854775807";} | 2012-07-09 03:22:41 |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+

Blast! What's going on here? Like I wrote above: those weren't NULs. Let's try again:

mysql> update sessions set data = 'user|O:4:"User":3:{s:11:"\0User\0email";s:18:"dev@bpj-code.co.za";s:8:"verified";b:1;s:2:"id";s:2:"16";}' where id = 'foo';
Query OK, 1 row affected (0.00 sec)

That change sticks! I guess it's too much to ask to have both tabular output and escaping of special characters?