Storing serialized data in MySQL table

[PI]

Hi Guys,

I need some assistance with the following scenario please. I know this
might be more of a MySQL post than a PHP one, but it is likely some
developer has been here before and would be able to help out.

I have some serialized data that I would like to store in a MySQL
database. When viewed, the serialized data looks like this:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart
\0c_cart_owner\";s:5:\"owner\";s:22:\"\0cart\0c_ca rt_date_time\";N;s:
20:\"\0cart\0c_arr_contents\";a:1:{i:0;O:9:\"cart_ item\":5:{s:
25:\"\0cart_item\0c_cart_item_id\";s:3:\"162\";s:2 7:\"\0cart_item
\0c_cart_item_name\";s:10:\"30BWL15481\";s:34:\"\0 cart_item
\0c_cart_item_description\";s:23:\"BOWL,MINIDOT,PR ISM,POLY\";s:
31:\"\0cart_item\0c_cart_item_quantity\";i:1;s:20: \"\0cart_item
\0c_cart_id\";N;}}}

The problem is that I have not been able to successfully persist this
data in the database. The Schema I am currently using for the table
is:
+--------------+--------------+------+-----+---------+----------------
+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+---------+----------------
+
| order_id | int(10) | NO | PRI | NULL | auto_increment
|
| order_number | varchar(255) | YES | | NULL |
|
| user_id | varchar(255) | YES | | NULL |
|
| product_data | longtext | YES | | NULL |
|
| session_data | varchar(255) | YES | | NULL |
|
| date | varchar(255) | YES | | NULL |
|
+--------------+--------------+

When I attempt to insert this data in the table using the following
query statement:

SQL query is is: INSERT INTO spares_order (order_number, user_id,
product_data, session_data, date) VALUES ('tlx-18954XM',
'i**@gmail.com', 'O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:
18:\"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\ 0cart
\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_conten ts\";a:1:{i:0;O:
9:\"cart_item\":5:{s:25:\"\0cart_item\0c_cart_item _id\";s:3:\"162\";s:
27:\"\0cart_item\0c_cart_item_name\";s:10:\"30BWL1 5481\";s:
34:\"\0cart_item\0c_cart_item_description\";s:
23:\"BOWL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\ 0c_cart_item_quantity
\";i:1;s:20:\"\0cart_item\0c_cart_id\";N;}}}',
'0cae0dd4494d178a04244be03fff68cd', 'Wednesday 12th of November 2008
09:58:29 AM')

I have no error messages but the table column product_data which
ought to hold the serialized data holds only part of the complete
string as shown:

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+
I understand this might have to do with certain characters within
that serialized data that needs to be escaped. I have attempted a
number of escape options but none seems to work for me. I would
appreciate some assistance with this please. Thanks a lot.

[Sven Reuter]

PI wrote:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart
\0c_cart_owner\";s:5:\"owner\";s:22:\"\0cart\0c_ca rt_date_time\";N;s:
20:\"\0cart\0c_arr_contents\";a:1:{i:0;O:9:\"cart_ item\":5:{s:
25:\"\0cart_item\0c_cart_item_id\";s:3:\"162\";s:2 7:\"\0cart_item
\0c_cart_item_name\";s:10:\"30BWL15481\";s:34:\"\0 cart_item
\0c_cart_item_description\";s:23:\"BOWL,MINIDOT,PR ISM,POLY\";s:
31:\"\0cart_item\0c_cart_item_quantity\";i:1;s:20: \"\0cart_item
\0c_cart_id\";N;}}}

[...]

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+

Did you try add_slashes()?
http://de.php.net/manual/en/function.addslashes.php
Sven Reuter
--
http://www.sReuter.net/
http://www.Auskennbert.de/

[Erwin Moller]

PI schreef:

Hi Guys,

I need some assistance with the following scenario please. I know this
might be more of a MySQL post than a PHP one, but it is likely some
developer has been here before and would be able to help out.

I have some serialized data that I would like to store in a MySQL
database. When viewed, the serialized data looks like this:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart
\0c_cart_owner\";s:5:\"owner\";s:22:\"\0cart\0c_ca rt_date_time\";N;s:
20:\"\0cart\0c_arr_contents\";a:1:{i:0;O:9:\"cart_ item\":5:{s:
25:\"\0cart_item\0c_cart_item_id\";s:3:\"162\";s:2 7:\"\0cart_item
\0c_cart_item_name\";s:10:\"30BWL15481\";s:34:\"\0 cart_item
\0c_cart_item_description\";s:23:\"BOWL,MINIDOT,PR ISM,POLY\";s:
31:\"\0cart_item\0c_cart_item_quantity\";i:1;s:20: \"\0cart_item
\0c_cart_id\";N;}}}

The problem is that I have not been able to successfully persist this
data in the database. The Schema I am currently using for the table
is:
+--------------+--------------+------+-----+---------+----------------
+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+---------+----------------
+
| order_id | int(10) | NO | PRI | NULL | auto_increment
|
| order_number | varchar(255) | YES | | NULL |
|
| user_id | varchar(255) | YES | | NULL |
|
| product_data | longtext | YES | | NULL |
|
| session_data | varchar(255) | YES | | NULL |
|
| date | varchar(255) | YES | | NULL |
|
+--------------+--------------+

When I attempt to insert this data in the table using the following
query statement:

SQL query is is: INSERT INTO spares_order (order_number, user_id,
product_data, session_data, date) VALUES ('tlx-18954XM',
'i**@gmail.com', 'O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:
18:\"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\ 0cart
\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_conten ts\";a:1:{i:0;O:
9:\"cart_item\":5:{s:25:\"\0cart_item\0c_cart_item _id\";s:3:\"162\";s:
27:\"\0cart_item\0c_cart_item_name\";s:10:\"30BWL1 5481\";s:
34:\"\0cart_item\0c_cart_item_description\";s:
23:\"BOWL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\ 0c_cart_item_quantity
\";i:1;s:20:\"\0cart_item\0c_cart_id\";N;}}}',
'0cae0dd4494d178a04244be03fff68cd', 'Wednesday 12th of November 2008
09:58:29 AM')

I have no error messages but the table column product_data which
ought to hold the serialized data holds only part of the complete
string as shown:

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+
I understand this might have to do with certain characters within
that serialized data that needs to be escaped. I have attempted a
number of escape options but none seems to work for me. I would
appreciate some assistance with this please. Thanks a lot.

Hi,

I am no mySQL expert, but did you try mysql_real_escape_string() on the
string before using it in the INSERT statement? (I know this works just
as it should in Postgres.)

Also: Could there be a problem with character encoding?
Are both pieces of data in the characterset? (maybe your db uses latin1
and the data passed is Unicode. This can lead to confusing problems.)

Last: DId you try to ask mysql what it says about the length of the
string in that field? Possibly it is all there but your
browser/shell/whatever refuses to display it all because it found funky
charactercontrol functions. I had that situation with a badly adjusted
shell on Linux that was displaying UTF-8. It can often easily be
adjusted in the shell itself.

Regards,
Erwin Moller
--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

[PI]

Thanks Erwin and Sven,
Sven; I have tried addslashes and other options on the suggested PHP
man pages - didn't work for this. I am familiar with the addslashes()
function.

Erwin: I have tried the mysql_real_escape_string() function and that
did not work either. On character set and encoding, I use the UTF8 for
the database and the data, how can I be sure/check that the data is
UTF-8. The problem occurs whether the insert is made via PHP or using
a MySQL console.
The other suggestion Erwin made is what I could try, but what command
please would I use to ask for the length of the string value- just in
case neither the console nor the other applications are show the full
string in their display. Thanks a lot.

[Erwin Moller]

PI schreef:

Thanks Erwin and Sven,
Sven; I have tried addslashes and other options on the suggested PHP
man pages - didn't work for this. I am familiar with the addslashes()
function.

Erwin: I have tried the mysql_real_escape_string() function and that
did not work either. On character set and encoding, I use the UTF8 for
the database and the data, how can I be sure/check that the data is
UTF-8.

Then you are probably fine, unless MySQL needs extra commands to tell it
to use UTF8 (but I am no good with mysql, so I don't know.)

The problem occurs whether the insert is made via PHP or using

a MySQL console.
The other suggestion Erwin made is what I could try, but what command
please would I use to ask for the length of the string value- just in
case neither the console nor the other applications are show the full
string in their display. Thanks a lot.

A quick google gave me this:
CHAR_LENGTH()
http://dev.mysql.com/doc/refman/5.0/...functions.html

So you'll make a query like:
SELECT CHAR_LENGTH(product_data) as thelength FROM spares_order WHERE
(order_number='tlx-18954XM');

Once again, maybe I make a MySQL mistake, but the above shows the approach.

Good luck.

Regards,
Erwin Moller

--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

[PI]

Thanks again Erwin,
As suggested, I have tried a few commands and tests with the
CHAR_LENGTH command and it appears that the value is indeed stored in
the table but the displays sorts of truncates the values. I hope that
when it is time to retrieve the value and unserialize it, I won't have
the (seemingly)truncated value from the table. However, you've been
brilliant and helpful Erwin, Sven and others. Thanks a huge lot.

[=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?=]

PI escribió:

I have some serialized data that I would like to store in a MySQL
database. When viewed, the serialized data looks like this:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart
\0c_cart_owner\";s:5:\"owner\";s:22:\"\0cart\0c_ca rt_date_time\";N;s:
20:\"\0cart\0c_arr_contents\";a:1:{i:0;O:9:\"cart_ item\":5:{s:
25:\"\0cart_item\0c_cart_item_id\";s:3:\"162\";s:2 7:\"\0cart_item
\0c_cart_item_name\";s:10:\"30BWL15481\";s:34:\"\0 cart_item
\0c_cart_item_description\";s:23:\"BOWL,MINIDOT,PR ISM,POLY\";s:
31:\"\0cart_item\0c_cart_item_quantity\";i:1;s:20: \"\0cart_item
\0c_cart_id\";N;}}}

[...]

When I attempt to insert this data in the table using the following
query statement:

SQL query is is: INSERT INTO spares_order (order_number, user_id,
product_data, session_data, date) VALUES ('tlx-18954XM',
'i**@gmail.com', 'O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:
18:\"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\ 0cart
\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_conten ts\";a:1:{i:0;O:
9:\"cart_item\":5:{s:25:\"\0cart_item\0c_cart_item _id\";s:3:\"162\";s:
27:\"\0cart_item\0c_cart_item_name\";s:10:\"30BWL1 5481\";s:
34:\"\0cart_item\0c_cart_item_description\";s:
23:\"BOWL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\ 0c_cart_item_quantity
\";i:1;s:20:\"\0cart_item\0c_cart_id\";N;}}}',
'0cae0dd4494d178a04244be03fff68cd', 'Wednesday 12th of November 2008
09:58:29 AM')

The backslash is the escape character in MySQL. Your query will not
insert \" but " and it won't insert \0 but a NULL character (i.e., a
byte will all bits set to zero). Once you retrieve the inserted data
your string will containt NULL bytes; some viewers may not display it
properly and stop at the first non-printable char, thus only displaying:

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+

It also happens that \ is the escape character in PHP, which makes it
all even more confusing.

I presume you don't really know what actual data your variables contain
but it's hard to guess the exact problem without a single line of PHP
code. I've serialized some sample session this way:

print_r( serialize($_SESSION) );

It didn't add any backslash to quotes so you're using a different
serializer, your source data is not a PHP variable or your serialized
data is not exactly what you posted.

I understand this might have to do with certain characters within
that serialized data that needs to be escaped. I have attempted a
number of escape options but none seems to work for me. I would
appreciate some assistance with this please. Thanks a lot.

You don't need to try all the escape options available: each database
access library has its own method. Find out in the manual pages for your
library, whatever it is. PHP's built-in extensions are documented here:

http://www.php.net/manual/en/refs.database.php

Good luck and keep us informed.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

[Erwin Moller]

PI schreef:

Thanks again Erwin,
As suggested, I have tried a few commands and tests with the
CHAR_LENGTH command and it appears that the value is indeed stored in
the table but the displays sorts of truncates the values. I hope that
when it is time to retrieve the value and unserialize it, I won't have
the (seemingly)truncated value from the table. However, you've been
brilliant and helpful Erwin, Sven and others. Thanks a huge lot.

Wow, us brilliant?
I don't know about Sven, but I am surely not brilliant. ;-)
But thanks a lot. :-) Glad to be of service.

About the truncation: I had the same problem (and a lot more weirder
problems too for that matter). It took me the better part of the day to
find out what I was doing wrong. :-/
It is all about telling your client which characterset to use.
EG: If you are directly on *nix you'll have an evironment variable named
LANG:
type: 'env' in your shell to see your settings. It will give something
like this:
......
LANG=en_US.UTF-8
.....

If not, overrule it via export command.

Or, if you use a program like putty to connect to *nix from Windows,
like me, you can set it too for every saved connection.
You can find it under Window->Translation:
'Received data assumed to be in which character set'
Select your choice from the dropdownlist.

Good luck.

Regards,
Erwin Moller

--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

[Jerry Stuckle]

PI wrote:

Thanks Erwin and Sven,
Sven; I have tried addslashes and other options on the suggested PHP
man pages - didn't work for this. I am familiar with the addslashes()
function.

Erwin: I have tried the mysql_real_escape_string() function and that
did not work either. On character set and encoding, I use the UTF8 for
the database and the data, how can I be sure/check that the data is
UTF-8. The problem occurs whether the insert is made via PHP or using
a MySQL console.
The other suggestion Erwin made is what I could try, but what command
please would I use to ask for the length of the string value- just in
case neither the console nor the other applications are show the full
string in their display. Thanks a lot.

mysql_real_escape_string() is the correct function to use - you should
not be using addslashes().

How are you looking at your data? If you're using phpMyAdmin, it
truncates long columns by default.

If you're still having problems, you should be asking this in
comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[sskaje]

On 11ÔÂ12ÈÕ, ÏÂÎç6ʱ49·Ö, PI <[e-mail]:

Hi Guys,

I need some assistance with the following scenario please. I know this
might be more of a MySQL post than a PHP one, but it is likely some
developer has been here before and would be able to help out.

I have some serialized data that I would like to store in a MySQL
database. When viewed, the serialized data looks like this:

O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:18: \"\0cart
\0c_cart_owner\";s:5:\"owner\";s:22:\"\0cart\0c_ca rt_date_time\";N;s:
20:\"\0cart\0c_arr_contents\";a:1:{i:0;O:9:\"cart_ item\":5:{s:
25:\"\0cart_item\0c_cart_item_id\";s:3:\"162\";s:2 7:\"\0cart_item
\0c_cart_item_name\";s:10:\"30BWL15481\";s:34:\"\0 cart_item
\0c_cart_item_description\";s:23:\"BOWL,MINIDOT,PR ISM,POLY\";s:
31:\"\0cart_item\0c_cart_item_quantity\";i:1;s:20: \"\0cart_item
\0c_cart_id\";N;}}}

The problem is that I have not been able to successfully persist this
data in the database. The Schema I am currently using for the table
is:
+--------------+--------------+------+-----+---------+----------------
+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+---------+----------------
+
| order_id | int(10) | NO | PRI | NULL | auto_increment
|
| order_number | varchar(255) | YES | | NULL |
|
| user_id | varchar(255) | YES | | NULL |
|
| product_data | longtext | YES | | NULL |
|
| session_data | varchar(255) | YES | | NULL |
|
| date | varchar(255) | YES | | NULL |
|
+--------------+--------------+

When I attempt to insert this data in the table using the following
query statement:

SQL query is is: INSERT INTO spares_order (order_number, user_id,
product_data, session_data, date) VALUES ('tlx-18954XM',
'[e-mail]', 'O:4:\"cart\":4:{s:15:\"\0cart\0c_cart_id\";N;s:
18:\"\0cart\0c_cart_owner\";s:5:\"owner\";s:22:\"\ 0cart
\0c_cart_date_time\";N;s:20:\"\0cart\0c_arr_conten ts\";a:1:{i:0;O:
9:\"cart_item\":5:{s:25:\"\0cart_item\0c_cart_item _id\";s:3:\"162\";s:
27:\"\0cart_item\0c_cart_item_name\";s:10:\"30BWL1 5481\";s:
34:\"\0cart_item\0c_cart_item_description\";s:
23:\"BOWL,MINIDOT,PRISM,POLY\";s:31:\"\0cart_item\ 0c_cart_item_quantity
\";i:1;s:20:\"\0cart_item\0c_cart_id\";N;}}}',
'0cae0dd4494d178a04244be03fff68cd', 'Wednesday 12th of November 2008
09:58:29 AM')

I have no error messages but the table column product_data which
ought to hold the serialized data holds only part of the complete
string as shown:

+----------------------+
| product_data |
+----------------------+
| O:4:"cart":4:{s:15:" |
+----------------------+

I understand this might have to do with certain characters within
that serialized data that needs to be escaped. I have attempted a
number of escape options but none seems to work for me. I would
appreciate some assistance with this please. Thanks a lot.

do
mysql_real_escape_string()
mysqli_real_escape_string()

before doing insert/update/replace to mysql