php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #35368 PDO query does not work properly with serialize
Submitted: 2005-11-24 16:07 UTC Modified: 2013-12-13 07:45 UTC
Votes:21
Avg. Score:4.7 ± 0.7
Reproduced:19 of 20 (95.0%)
Same Version:4 (21.1%)
Same OS:7 (36.8%)
From: lists at cyberlot dot net Assigned:
Status: Suspended Package: PDO related
PHP Version: 6CVS, 5CVS OS: *
Private report: No CVE-ID: None
View Developer Edit
 [2005-11-24 16:07 UTC] lists at cyberlot dot net 
Description:
------------
If you serialize a string, and run any of the escape functions, mysql_escape, addslashes you can not use pdo->query to insert and it does some sort of bind params translation on the string.

Bug exists in php5.1RC4 as I was using that version when I first found this and upgraded to see if issue was resolved.

Using prepared statements ( and therefore not having to escape the data before hand ) works.

Reproduce code:
---------------
<?

$dsn = 'mysql:dbname=alpha;host=localhost';
$login = 'alpha';
$password = 'alpha';
$db = new PDO($dsn, $login, $password);
$TEST['test']['test2'] = '1234';
$TEST['test']['test3'] = '555353';
$var = serialize($TEST);
echo "$var\n<BR>\n";
$var = mysql_escape_string($var);
$query = "INSERT INTO sessions SET value = '$var'";
$db->query($query);
$query = 'SELECT value FROM sessions';
$result = $db->query($query);
$row = $result->fetch();
echo $row[0]."\n<BR>\n";
?>


Expected result:
----------------
[root@alpha www_admin]# php index.php
a:1:{s:4:"test";a:2:{s:5:"test2";s:4:"1234";s:5:"test3";s:6:"555353";}}
<BR>
a:1:{s:4:"test";a:2:{s:5:"test2";s:4:"1234";s:5:"test3";s:6:"555353";}}
<BR>


Actual result:
--------------
[root@alpha www_admin]# php index.php
a:1:{s:4:"test";a:2:{s:5:"test2";s:4:"1234";s:5:"test3";s:6:"555353";}}
<BR>
a?:{s?:"test";a?:{s?:"test2";s?:"1234";s?:"test3";s?:"555353";}}
<BR>

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-25 01:03 UTC] lists at cyberlot dot net 
Debug output

SQL: [116] INSERT INTO sessions SET value = 'a:1:{s:4:\"test\";a:2:{s:5:\"test2\";s:4:\"1234\";s:5:\"test3\";s:6:\"555353\";}}'
Params:  0

Value in mysql after this

a?:{s?:"test";a?:{s?:"test2";s?:"1234";s?:"test3";s?:"555353";}}

I downloaded the release version of php 5.1 and reran same results
 [2005-11-25 05:07 UTC] wez@php.net 
I can't reproduce this at all, even disabling native prepared statement support in mysql doesn't give me those results.

Which version of mysql are you using?
 [2005-11-25 15:00 UTC] lists at cyberlot dot net 
Mysql 5.0.15

I have a few other systems I can see if I can try to replicate on
 [2005-11-25 15:11 UTC] lists at cyberlot dot net 
Run this on another box, MySQL 4.1.12 and php 5.1.0RC4 same results
 [2005-11-25 15:14 UTC] lists at cyberlot dot net 
What OS are you testing on? All I have are Centos/Redhat based boxes to test on.

Also if this helps I always download directly from MySQL I never use the DIST included rpms.
 [2005-11-25 16:32 UTC] lists at cyberlot dot net 
To try and narrow this down and be able to play with the code more I recompiled PHP 5.1 without pdo support then compiled seperate modules however I could not get pdo_mysql to compile.
I phpized ./configure and make and get the following error

checking for MySQL support for PDO... yes, shared
checking for mysql_config... /usr/bin/mysql_config
checking for mysql_query... no
configure: error: mysql_query missing!?

Might be related? So I forced a install of pdo_mysql RC2

The bug goes away, Same exact script but everything is working...

So its either a diffrence between pdo_mysql RC2 or some wierd issue with shared vs compiled in.

I hope that helps somehow?
 [2005-11-25 16:40 UTC] tony2001@php.net 
This is fixed in CVS, get a fresh snapshot and try again.
 [2005-11-27 22:11 UTC] wez@php.net 
We managed to reproduce the problem; it's a problem with the query rewriter when it maps :name to ?.  If the string is embedded in the SQL using single quotes, but has double quotes backslashed, the string it too tricky for the parser to follow, and it ends up transforming parts of the serialized string that it shouldn't.

There are three possible workarounds for this issue, in order of preference:
- Don't embed serialized data into the query string; use bound parameters (that's what they're there for).  In future versions of PDO, prepared statements may be cacheable in persistent connections, leading to a performance gain.
- Use PDO::quote() to correctly quote the string
- Use PDO::exec() to fire off this UPDATE/INSERT statement; it uses an alternate API that doesn't need to handle parameters.
 [2013-12-13 07:45 UTC] wez@php.net
-Assigned To: wez +Assigned To:
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Wed Oct 29 22:00:02 2025 UTC