Is it safe to search through serialized array as a string? (MySql query)

Question

I have a serialized array like this

a:6:{i:0;i:6;i:1;i:65;i:2;i:56;i:3;i:87;i:4;i:48;i:5;i:528;}

For example i want to make a mysql query like this

$id_serialize = 6;
"SELECT id FROM table WHERE col LIKE '% i:" . $id_serialize . "; %'"

Is it possible to get a conflict (for example the numbers are repeated etc.) as a consequence of this query ?

Is there another more effective and correct way to find number in array without unserializing the array and without looping ?

i wouldn't use like on a serialized string. it's not reliable. — Volkan Ulukut, Feb 05 '14 at 15:22

score 2 · Answer 1 · answered Feb 05 '14 at 15:42

It depends on data you are going to store. For integers it is highly possible.

a:6:{i:0;i:6;i:1;i:65;i:2;i:56;i:3;i:87;i:4;i:48;i:5;i:528;}

This actually menas:

a:6:{...} - array of 6 elements
i:0;i:6; - first element, id 0, value 6
i:1;i:65; - second element, id 1, value 65
and so on

If you will get to array of 7 elements, 7th element definition would be: i:6;i:34 And this would collide with i:0;i:6;. Your query would return results with id 6 along with results with value 6.

A bit more about arrays anatomy http://www.php.net/manual/pl/function.serialize.php#66147

score 1 · Answer 2 · edited May 23 '17 at 12:21

1

a:1:{i:0;s:5:"i:42;";}

Oops.

It's extremely hard to search within data formats which allow arbitrary content. It's the same reason why regexen are simply unsuited for (X|HT)ML. You should really be normalising the data and store each value in its own column/row.

edited May 23 '17 at 12:21

Community

1
1

answered Feb 05 '14 at 15:22

deceze

510,633
85
743
889

which is the best way to get the desired result ? – David Feb 05 '14 at 15:34
I don't know what the desired result is. Can you normalise your table structure to store those values individually? – deceze Feb 05 '14 at 15:36
Not in this case as the table will be really too huge and it will be more difficult to select. – David Feb 05 '14 at 15:39
I doubt it will be larger in storage size than a serialised string or harder to select than *impossible*. Maybe you have the wrong idea of what I'm suggesting. I'm saying you should make a new table, with two columns `table_id` (the primary id of your current `table`) and `value` (the values you're currently serialising). Then each value is inserted as a separate row, and you select it with a `JOIN` query. Simple, proper use of an RDBMS. – deceze Feb 05 '14 at 15:52

score 0 · Answer 3 · answered Feb 05 '14 at 15:42

If you are certain of the contents of the array - that is, if you know that all the items in the array are numbers - you should be able to use your method without too much trouble. If anything else makes it into the array you may start getting false results.

Is it safe to search through serialized array as a string? (MySql query)

3 Answers3