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

go to bug id or search bugs for

Bug #47982 PDO_mysql: Storing image binary data
Submitted: 2009-04-16 08:46 UTC Modified: 2017-10-24 08:33 UTC
Votes:9
Avg. Score:3.8 ± 1.3
Reproduced:6 of 6 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: markac at home dot pl Assigned:
Status: Verified Package: PDO MySQL
PHP Version: 5.2CVS-2009-04-16 (snap) OS: WinXP
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: markac at home dot pl
New email:
PHP Version: OS:

 

 [2009-04-16 08:46 UTC] markac at home dot pl 
Description:
------------
PDO adding extra bytes (F3 F3 F3 F3) to begining binary data.

I'm use SOAP extension to retrieving data from client. When I save data to file or using MySQL directly, all is ok. When I use PDO to store the same data to column (MySQL), PDO adding extra data and image is broken.

Reproduce code:
---------------
$server = new SoapServer('file.wsdl', array('cache_wsdl' => WSDL_CACHE_NONE)); 

/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=db;host=localhost;charset=UTF-8';
$user = 'root';
$password = '';
	

$pdo = new PDO($dsn, $user, $password);
$pdo->exec('SET CHARACTER SET utf8');

	
    //NOT WORK! Adding Image is broken
    $sth = $pdo->prepare('UPDATE users SET 
      avatar = :avatar
      WHERE id = :id
    '); 
    
    //$avatar is server function argument. See $server->addFunction('UpdateUser);
    $sth->bindParam(':avatar', $avatar, PDO::PARAM_LOB);
    $sth->bindParam(':id', $user->id, PDO::PARAM_INT);

    return $sth->execute();

    //THIS WORK!
    file_put_contents('image.jpg', $user->avatar);
    
    /* AND THIS WORK!
    mysql_query('update users set avatar = "' . addslashes($user->avatar) . '" where id = ' . $user->id);
    */    




  $server->addFunction('UpdateUser);
  $server->handle();
  
Sorry for my english.

Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-04-16 08:50 UTC] markac at home dot pl 
Sorry, replace $avatar to $user->avatar from all. Function argument is an object $user, 'avatar' and 'id' is properties.
 [2009-04-16 08:56 UTC] pajoye@php.net 
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.

and mysql != PDO
 [2009-04-16 09:31 UTC] markac at home dot pl 
$image = fopen('image.jpg','rb'); 
$sth->bindValue(':avatar', $image, PDO::PARAM_LOB);

Also not working, but when I remove this code:
$pdo->exec('SET CHARACTER SET utf8');

then is working in this example.
Any suggestion?
 [2009-04-16 09:37 UTC] markac at home dot pl 
OK. previous example also working witchout this code:
$pdo->exec('SET CHARACTER SET utf8');

How use UTF-8 and blob columns?
 [2009-04-16 12:21 UTC] johannes@php.net 
I wasn't able to reproduce the issue. Could you please give me the mysql version (the server version as well as the version of the mysql client library used (see phpinfo() output) and the table defintion of your table. Thanks.
 [2009-04-16 12:43 UTC] markac at home dot pl 
Apache 2.2.11
PHP 5.2.9-1
MySQL 5.1.31-ommunity

MySQL Client API version 5.0.51a
PDO Driver for MySQL, client library version 5.0.51a

Table structure:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(45) CHARACTER SET latin2 NOT NULL,
  `password` varchar(45) CHARACTER SET latin2 COLLATE latin2_bin NOT NULL,
  `email` varchar(45) DEFAULT '',
  `memo` text,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `removed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `avatar` blob,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `Index_2` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8;
 [2009-04-16 13:28 UTC] markac at home dot pl 
Strange, but when I using another remote database server 5.0.66a everything is good. I thing problem is on my site (bad MySQL or PHP configuration? ;/).
 [2009-04-16 13:33 UTC] markac at home dot pl 
Sorry once again. Works when
$pdo->exec('SET CHARACTER SET utf8');
is commented.
 [2009-04-21 15:10 UTC] johannes@php.net 
I'm not sure I correctly understand your both last messages. Is the problem only dependant on the SET NAMES call or also on the server version?

Thanks for clarification.
 [2009-04-21 18:48 UTC] markac at home dot pl 
Only dependant on the SET NAMES.
 [2009-04-22 10:51 UTC] johannes@php.net 
Thanks. Got it now reproduced using 5.2 as well as 5.3 (with both libmysql and mysqlnd)
 [2009-08-25 14:04 UTC] uw@php.net 
I don't call this a bug.

PDO::PARAM_LOB "can be either textual or binary in nature":

"At some point in your application, you might find that you need to store "large" data in your database. Large typically means "around 4kb or more", although some databases can happily handle up to 32kb before data becomes "large". Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB  type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.", http://www.php.net/manual/en/pdo.lobs.php

PDO_MySQL threats PDO::PARAM_LOB like textual data. Textual data needs to be escaped. This is what also happens if you use the PDO Prepared Statmeent emulation, which has been a default for a long time. When using the emulation, the column will be seen as textual data and be escaped. 

This, however, is not a MySQL specific problem. MySQL is affected because it supports charsets and stuff. But every other PDO driver supporting charsets is affected as well. 

A proper fix would be to introduce PDO::PARAM_BLOB for use with binary data. PDO::PARAM_BLOB should go into the PDO core. As changes to the core can impact all drivers, a volunteer is needed to check and/or update all drivers to get this PDO flaw fixed.

Ulf
 [2010-05-11 13:04 UTC] uw@php.net
-Status: Assigned +Status: Open -Assigned To: mysql +Assigned To:
 [2010-05-11 13:04 UTC] uw@php.net 
Not a MySQL specific issue. PDO general/PDO specification issue.
 [2010-11-05 06:02 UTC] kalle@php.net
-Package: MySQL related +Package: PDO related
 [2014-01-01 12:50 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 [2016-12-31 17:14 UTC] cmb@php.net
-Status: Open +Status: Verified -Package: PDO MySQL +Package: PDO Core
 [2016-12-31 17:15 UTC] cmb@php.net 
> A proper fix would be to introduce PDO::PARAM_BLOB for use with
> binary data. PDO::PARAM_BLOB should go into the PDO core.

So this is apparently a PDO Core issue.
 [2017-10-24 08:30 UTC] kalle@php.net
-Package: PDO Core +Package: PDO related
 [2017-10-24 08:33 UTC] kalle@php.net
-Package: PDO related +Package: PDO MySQL
 [2019-02-11 10:12 UTC] 
The following pull request has been associated:

Patch Name: Subdomain now uses php logo font.
On GitHub:  https://github.com/php/web-shared/pull/1
Patch:      https://github.com/php/web-shared/pull/1.patch
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 21 13:01:29 2024 UTC