Sudipta Bhattacharyya Sudipta Bhattacharyya - 3 months ago 14
MySQL Question

extract serialized data from msql database and unserialize and use individual fields in php

I am using a wordpress quiz plugin and I want to extract the questions and answers straight from the database table and use it to create a pdf document. The question is stored as a simple string which is easy to extract but the answers(multiple choice answers) are stored in the database column like this:

a:4:{i:0;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option1";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:1;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:1;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option2";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:2;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option3";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:3;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option4";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}}


Here the four options are 'Option1', 'Option2', 'Option3' and 'Option4'. How do I extract them as the four strings, or as a string array?

EDIT------------------------------------

As suggested I used the unserialize function as follows:

$ser ='a:4:{i:0;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option1";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:1;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:1;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option2";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:2;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option3";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:3;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option4";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}}';

$un = unserialize($ser);
if(!is_array($un)){
echo $un;
}else{
foreach($un as $value){
echo $value."\n";
}
}


I got the following error:

PHP Notice: unserialize(): Error at offset 63 of 822 bytes in <path> on line 5


How do I tackle this?

EDIT2---------------------------

Using str_replace() the code worked when I set the variable $ser myself copying the value from the mysql table.
Now when I extract it through php code, the variable $ser is no longer a string. I cannot typecast it to string. So str_replace() doesn't work. So I tried regular expression function preg_replace(), but it cannot replace '*_' taken together. So I replaced just '*'.
Here is the code:

$host="<>"; // Host name
$username="<>"; // Mysql username
$password="<>"; // Mysql password

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("<>")or die("cannot select DB");

$sql="<the query>";

$result = mysql_query($sql);
$row=mysql_fetch_array($result);


$ser = $row['answer_data'];
echo $ser.'<br/><br/>';

$pattern = '/\*/';
$replace = '_00';
$ser = preg_replace($pattern, $replace, $ser);
echo $ser.'<br/><br/>';


$un = unserialize($ser);
$un = json_decode(json_encode($un));
foreach($un as $obj){
echo $obj->_00_answer;
}



mysql_close();


Here's the output:

a:4:{i:0;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option1";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:1;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:1;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option2";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:2;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option3";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}i:3;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"*_answer";s:7:"Option4";s:8:"*_html";b:0;s:10:"*_points";i:1;s:11:"*_correct";b:0;s:14:"*_sortString";s:0:"";s:18:"*_sortStringHtml";b:0;s:10:"*_mapper";N;}}

a:4:{i:0;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"_00_answer";s:7:"Option1";s:8:"_00_html";b:0;s:10:"_00_points";i:1;s:11:"_00_correct";b:1;s:14:"_00_sortString";s:0:"";s:18:"_00_sortStringHtml";b:0;s:10:"_00_mapper";N;}i:1;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"_00_answer";s:7:"Option2";s:8:"_00_html";b:0;s:10:"_00_points";i:1;s:11:"_00_correct";b:0;s:14:"_00_sortString";s:0:"";s:18:"_00_sortStringHtml";b:0;s:10:"_00_mapper";N;}i:2;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"_00_answer";s:7:"Option3";s:8:"_00_html";b:0;s:10:"_00_points";i:1;s:11:"_00_correct";b:0;s:14:"_00_sortString";s:0:"";s:18:"_00_sortStringHtml";b:0;s:10:"_00_mapper";N;}i:3;O:27:"WpProQuiz_Model_AnswerTypes":7:{s:10:"_00_answer";s:7:"Option4";s:8:"_00_html";b:0;s:10:"_00_points";i:1;s:11:"_00_correct";b:0;s:14:"_00_sortString";s:0:"";s:18:"_00_sortStringHtml";b:0;s:10:"_00_mapper";N;}}


And here is the log file record:

[11-Aug-2016 04:01:42 Etc/GMT] PHP Notice: unserialize(): Error at offset 63 of 934 bytes in <path> on line 24
[11-Aug-2016 04:01:42 Etc/GMT] PHP Warning: Invalid argument supplied for foreach() in <path> on line 26


EDIT3-------------------

output of
echo json_encode($ser);
while extracting from database:

"a:4:{i:0;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"\u0000*\u0000_answer\";s:7:\"Option1\";s:8:\"\u0000*\u0000_html\";b:0;s:10:\"\u0000*\u0000_points\";i:1;s:11:\"\u0000*\u0000_correct\";b:1;s:14:\"\u0000*\u0000_sortString\";s:0:\"\";s:18:\"\u0000*\u0000_sortStringHtml\";b:0;s:10:\"\u0000*\u0000_mapper\";N;}i:1;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"\u0000*\u0000_answer\";s:7:\"Option2\";s:8:\"\u0000*\u0000_html\";b:0;s:10:\"\u0000*\u0000_points\";i:1;s:11:\"\u0000*\u0000_correct\";b:0;s:14:\"\u0000*\u0000_sortString\";s:0:\"\";s:18:\"\u0000*\u0000_sortStringHtml\";b:0;s:10:\"\u0000*\u0000_mapper\";N;}i:2;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"\u0000*\u0000_answer\";s:7:\"Option3\";s:8:\"\u0000*\u0000_html\";b:0;s:10:\"\u0000*\u0000_points\";i:1;s:11:\"\u0000*\u0000_correct\";b:0;s:14:\"\u0000*\u0000_sortString\";s:0:\"\";s:18:\"\u0000*\u0000_sortStringHtml\";b:0;s:10:\"\u0000*\u0000_mapper\";N;}i:3;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"\u0000*\u0000_answer\";s:7:\"Option4\";s:8:\"\u0000*\u0000_html\";b:0;s:10:\"\u0000*\u0000_points\";i:1;s:11:\"\u0000*\u0000_correct\";b:0;s:14:\"\u0000*\u0000_sortString\";s:0:\"\";s:18:\"\u0000*\u0000_sortStringHtml\";b:0;s:10:\"\u0000*\u0000_mapper\";N;}}"


output of
echo json_encode($ser);
previously, i.e. copy-pasting the value from phpmyadmin:

"a:4:{i:0;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"*_answer\";s:7:\"Option1\";s:8:\"*_html\";b:0;s:10:\"*_points\";i:1;s:11:\"*_correct\";b:1;s:14:\"*_sortString\";s:0:\"\";s:18:\"*_sortStringHtml\";b:0;s:10:\"*_mapper\";N;}i:1;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"*_answer\";s:7:\"Option2\";s:8:\"*_html\";b:0;s:10:\"*_points\";i:1;s:11:\"*_correct\";b:0;s:14:\"*_sortString\";s:0:\"\";s:18:\"*_sortStringHtml\";b:0;s:10:\"*_mapper\";N;}i:2;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"*_answer\";s:7:\"Option3\";s:8:\"*_html\";b:0;s:10:\"*_points\";i:1;s:11:\"*_correct\";b:0;s:14:\"*_sortString\";s:0:\"\";s:18:\"*_sortStringHtml\";b:0;s:10:\"*_mapper\";N;}i:3;O:27:\"WpProQuiz_Model_AnswerTypes\":7:{s:10:\"*_answer\";s:7:\"Option4\";s:8:\"*_html\";b:0;s:10:\"*_points\";i:1;s:11:\"*_correct\";b:0;s:14:\"*_sortString\";s:0:\"\";s:18:\"*_sortStringHtml\";b:0;s:10:\"*_mapper\";N;}}"


So there are indeed extra characters now. The collation in database is 'utf8_general_c'

Answer

The serialised string has null bytes in many of the property names. These don't display when you echo them. You'll see for instance this:

*_answer

...while in fact the original string (not echo-ed) has this (where <NUL> represents a null byte):

<NUL>*<NUL>_answer

These null bytes indicate that these properties are protected, as stated in the PHP documentation on serialize:

[...] protected members have a '*' prepended to the member name. These prepended values have null bytes on either side.

If you just take the echo-ed string, and then try to unserialize that, you'll get the errors you mention, since the indicated string length (like s:10:) will no longer correspond to the length of the null-byte-stripped property name that follows.

If on the other hand you unserialize the original string, it will produce the array, but then the elements will have protected properties, which makes it hard to access them.

Here is code that will replace the null-asterisk-null sequences with three other, readable characters (I chose: "key"), keeping the length unchanged. That way, the unserialize function will not produce protected properties, and you can easily access them with names like "key_answer":

// Replace two NUL bytes separated by an asterisk with three other characters: "key"
// Note that you need to use double quotes for the first argument.
// This will make it easier to access those properties:
$ser = str_replace("\0*\0_", 'key_', $ser);
// Now unserialize this:
$un = unserialize($ser);
// Avoid warnings about incomplete objects: 
// turn specific class objects to standard associative arrays:
$un = json_decode(json_encode($un), true);
// Extract the answers:
$answers = array_column($un, 'key_answer');
// Output the answers (but you could also use a foreach loop):
echo implode(",", $answers);

If your version of PHP is older than 5.5, then it does not support array_column. Instead just write:

foreach($un as $row) {
    $answers[] = $row['key_answer'];
}