Unserializing PHP array not working

Question

I have the following serialized array stored in a MySQL longblob data field:

a:1:{s:10:"attributes";a:1:{s:13:"Ticket Holder";a:1:{i:0;s:8:"Joe Blow";}}}

In PHP, when I query the field, unserialize it, and print it out, the following empty array is printed:

Array
(
)

This is the table create statement:

CREATE TABLE `order` (
  `state` varchar(255) CHARACTER SET ascii DEFAULT NULL,
  `data` longblob,
  `created` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

What function are you using to perfom the query and unserialize? — Joseph Daudi, Aug 24 '17 at 18:16
It's in Drupal 8. I'm using the select function in \Drupal\Core\Database\Connection for the query. I'm simply using the PHP unserialize function to deserialize it. — user320691, Aug 24 '17 at 18:23
tried it in phpfiddle, and `unserialize()` throws an exception. If I reproduce the array and serialize it, I get `a:1:{s:10:"attributes";a:1:{s:13:"ticket holder";a:1:{i:0;s:8:"Joe Blow";}}}` Bottom Line: I think there's a problem with the data you're getting — William Perron, Aug 24 '17 at 18:23
I changed the ticket holder name for privacy purposes. This is my serialization code serialize(['attributes' => ['Ticket Holder' => ['Joe Blow']]]). — user320691, Aug 24 '17 at 18:27
I just noticed this in the logs 'Notice: unserialize(): Error at offset 0 of 1 bytes'. — user320691, Aug 24 '17 at 18:39
@user320691 out of curiosity, what version of PHP are you using? — William Perron, Aug 24 '17 at 19:21
Serialization works with varchar fields but not longblob. Unfortunately, I have to use the latter. — user320691, Aug 24 '17 at 19:28
Part `s:13:"Joe Blow"` doesn't make any sense in that serialized array or you have some "hidden" characters inside that string. — Wh1T3h4Ck5, Aug 24 '17 at 19:47
s:13:"Joe Blow" was changed to pose this question. The original name had 13 characters. — user320691, Aug 24 '17 at 19:52
Since the string itself is [a valid serialization format](https://3v4l.org/bitY0), we must conclude there is non-printing data in there. Try piping the column value through `cat -vet`. — bishop, Aug 24 '17 at 19:57
Related: https://stackoverflow.com/questions/5555496/what-is-the-difference-between-longblob-and-longtext-in-mysql — mickmackusa, Aug 25 '17 at 00:37
Related: https://stackoverflow.com/questions/5544749/what-column-type-should-be-used-to-store-serialized-data-in-a-mysql-db — mickmackusa, Aug 25 '17 at 00:48

score 1 · Answer 1 · answered Aug 24 '17 at 19:52

1

Your serialized data is invalid. You must never mainpulate serialized data manually.

Strings are serialized as:

s:<i>:"<s>";

where <i> is an integer representing the string length of <s>, and <s> is the string value.

So in this case valid data is :

a:1:{s:10:"attributes";a:1:{s:13:"Ticket Holder";a:1:{i:0;s:8:"Joe Blow";}}}

Joe Blow string length is 8 but in your serialized strings is defined 13.

See : Structure of a Serialized PHP string

answered Aug 24 '17 at 19:52

Alireza Hoseinzadeh

606
3
6

1

From OP: "`s:13:"Joe Blow"` was changed to pose this question. The original name had 13 characters" – bishop Aug 24 '17 at 19:53
Please replace `Joe Blow` with another valid string in your question to prevent mistake. – Alireza Hoseinzadeh Aug 24 '17 at 19:55
Sorry for the confusion Alireza. Thank you. – user320691 Aug 24 '17 at 20:03

William Perron · Answer 2 · 2017-08-24T20:00:43.000

0

Use `base64_encode`

The core problem comes from the binary encoding done by the database and the extra bytes it adds to the value stored. This "corrupts" the data that is being fetched from the database and causes unserialize to fail. To help mitigate this problem, you can use base64_encode and base64_decode which will allow you to insert and extract the data painlessly.

// data is encoded before insert
$dataToInsert = base64_encode(serialize($myArray));

// decode the data before unserializing it
$dataToRead = unserialize(base64_decode($longblob));

edited Aug 24 '17 at 20:00

answered Aug 24 '17 at 19:43

William Perron

1,288
13
18

@bishop the problem comes from the encoding done by the `longblob` type, which makes the corrupts the data, which in turn causes `unserialize` to fail. Using `base64_encode` and `base64_decode` prevents this – William Perron Aug 24 '17 at 19:59
I tried this and it doesn't store anything but a:0:{}. When not using base64 encoding, it stores the serialized array correctly. I just haven't been able to retrieve it from a longblob field. Thanks William. – user320691 Aug 24 '17 at 20:05
1

If the OP is inserting data into his database without properly espcaping it, then base64 encoding the serialized PHP might fix the immediate problem but this is not applicable to all the data stored in a database - and implies that the code is vulnerable to SQL injection. Merely using a longblob does not corrupt data. – symcbean Aug 24 '17 at 20:21

score 0 · Accepted Answer · answered Aug 24 '17 at 21:56

Converting the column to UTF8 in the SELECT statement and then unserializing it.

CONVERT (data USING utf8)

I'm not sure why this is necessary. I'm guessing it has something to do with the utf8mb4_general_ci collation.

Thank you for all of your help guys!

Unserializing PHP array not working

3 Answers3

Use base64_encode

Use `base64_encode`