php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #41135 PDO SQLite BLOB field data truncated after 21 bytes
Submitted: 2007-04-18 16:46 UTC Modified: 2008-03-07 17:16 UTC
Votes:5
Avg. Score:4.8 ± 0.4
Reproduced:4 of 4 (100.0%)
Same Version:1 (25.0%)
Same OS:0 (0.0%)
From: rich at corephp dot co dot uk Assigned: scottmac (profile)
Status: Closed Package: PDO related
PHP Version: 5.2.1 OS: Windows XP SP2
Private report: No CVE-ID: None
 [2007-04-18 16:46 UTC] rich at corephp dot co dot uk
Description:
------------
There appears to be an issue inserting binary data from a file into a SQLite BLOB field via PDO SQLite in that the data is never fully transferred.

The SQLite database consisted of 1 table with 3 columns:

id - integer - primary
filename - varchar(50)
data - blob

The PHP code tried to insert an image file into the BLOB field. The code does not error in any way, nothing appears in the PHP error log, no warnings are produced, but the data is never fully transmitted to the SQLite database.

The ID is created properly, the filename is correct, but the data field contains only the first 21 bytes worth of the image and nothing more.

Various different image files have been tested. The SQLite database itself is valid, and works perfectly with BLOB fields when inserted from the command-line. The web server is Apache 2.2.4 The SQLite database, from phpinfo:

PDO Driver for SQLite 3.x	enabled
PECL Module version 	1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.1 2007/01/01 09:36:05 sebastian Exp $
SQLite Library 	3.3.7undefined



Reproduce code:
---------------
$filename = 'D:/sandbox.dev/public_html/test.gif';

try
{
    $db = new PDO('sqlite:D:/sandbox.dev/public_html/test.db');
}
catch (PDOException $error)
{
    echo "Error! :" . $error->getMessage();
}

$name = 'test.gif';

$stmt = $db->prepare('INSERT INTO DataTest (filename, data) VALUES (?,?)');
$stmt->execute(array($name, file_get_contents($filename)));


Expected result:
----------------
The SQLite database 'test.db' should be updated to contain the full contents of test.gif in the BLOB field.

Actual result:
--------------
The image data is truncated after the first 21 bytes. When viewed in Hexadecimal mode you can see that the GIF89a header was passed across, along with the first 5 bytes of actual picture data, but after that it is cut off. Insert a PNG file instead of a GIF and you get the PNG data header, plus a few bytes worth of image, and again it cuts off. No matter what type of file you insert into the BLOB, only the first 21 bytes make it.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-04-18 16:55 UTC] wez@php.net
Can you clarify if you're using the sqlite that comes with PHP or if you pulled it out of a separate PECL download?
 [2007-04-18 17:11 UTC] rich at corephp dot co dot uk
The one that comes in php-5.2.1-Win32.zip (ext/php_pdo_sqlite.dll - 274,496 bytes). Please note that both the PDO SQLite AND the SQLite (php_sqlite.dll) extensions are loaded.
 [2007-04-20 18:04 UTC] Jared dot Williams1 at ntlworld dot com
I believe it crops the data to the first \0 byte. 

The ugly workaround is not to use bound parameters ... 

$pic_data = 'X'.$db->quote(bin2hex($pic_data));

$stmt = $db->prepare('INSERT INTO Test (name, data) VALUES (?,'.$pic_data.')');
$stmt->bindParam(1, $name, PDO::PARAM_STR, 60);
$stmt->execute();
 [2007-04-21 03:28 UTC] wez@php.net
What does the following script output for you?

$db->exec('CREATE TABLE test (data blob)');

$stmt = $db->prepare("INSERT INTO test VALUES ( ? )");
$stmt->bindParam(1, $name);
$name = "\x00a\x00";
var_dump($stmt->execute(), $stmt->rowCount());

$stmt = $db->prepare("select * from test");
var_dump($stmt->execute());
foreach ($stmt->fetchAll() as $row) {
    echo bin2hex($row[0]), "\n";
}


 [2008-01-22 17:21 UTC] maciej dot pijanka at gmail dot com
on linux, with php 5.2.5 and postgresql 8.2.5, problem is replicable
ie after last example table gets empty '' values not NULLs. I wrote more detailed testcase. 
-- file.php --
<?php
try
{
        $db = new PDO('pgsql:dbname=template1;user=postgres','postgres');
}
catch (PDOException $error)
{
        echo "Error! :" . $error->getMessage();
        exit;
}

$binarydata = "abcdefg\x00a\x00\x01\x02";

$db->exec('CREATE TABLE test (data bytea, comment varchar(64), len integer)');

$stmt = $db->prepare("INSERT INTO test VALUES ( :data, :comment, :len )");

# test 1, just binary data, will fail
$comment = 'Just pass binary data as bindValue';
$stmt->bindValue('data', $binarydata); 
$stmt->bindValue('comment', $comment); $stmt->bindValue('len', strlen($binarydata));
var_dump($stmt->execute(), $stmt->rowCount(), $comment); print "--\n";

# pass as bindParam
$comment = 'Just pass binary data as bindParam';
$stmt->bindParam('data', &$binarydata);
$stmt->bindValue('comment', $comment); $stmt->bindValue('len', strlen($binarydata));
var_dump($stmt->execute(), $stmt->rowCount(), $comment); print "--\n";

# encoded by pg_escape_bytea, wrong too
$comment = 'encoded by pg_escape_bytea';
$stmt->bindValue('data', pg_escape_bytea($binarydata));
$stmt->bindValue('comment', $comment); $stmt->bindValue('len', strlen($binarydata));
var_dump($stmt->execute(), $stmt->rowCount(), $comment); print "--\n";

# pass something that give correct result
$comment = 'this one produces best result so far';
$stmt->bindValue('data', str_replace("\\\\","\\",pg_escape_bytea($binarydata)),PDO::PARAM_STR);
$stmt->bindValue('comment', $comment); $stmt->bindValue('len', strlen($binarydata));
var_dump($stmt->execute(), $stmt->rowCount(), $comment); print "--\n";

echo "---- NOW FETCH ----\nExecute returned: ";

$stmt = $db->prepare("select * from test");
var_dump($stmt->execute());

# with or without that its still wrong
#$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES,true);

print "\nFetching and comparing data\n";
# with fetchall resources don't work too
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $buffer = '';
        #var_dump($row);
        if(is_resource($row['data'])) {
                $buffer = stream_get_contents($row['data']);
        } else
                $buffer = $row['data'];
        print "Test `".$row['comment']."' StrCmp:";
        print "".(strcmp($binarydata,$buffer) == 0 )?"equal":"fail";
        print " length stored in db: ". $row['len'] . ", returned data len: ".strlen($buffer)."\n";
        echo bin2hex($buffer)."\n";
}
$db->exec('DROP TABLE test');
?>

== end of file ===
and output for me looks like
(only rows with results)

Fetching and comparing data
Test `Just pass binary data as bindValue' StrCmp:fail length stored in db: 12, returned data len: 7
61626364656667
Test `Just pass binary data as bindParam' StrCmp:fail length stored in db: 12, returned data len: 7
61626364656667
Test `encoded by pg_escape_bytea' StrCmp:fail length stored in db: 12, returned data len: 24
616263646566675c303030615c3030305c3030315c303032
Test `this one produces best result so far' StrCmp:equal length stored in db: 12, returned data len: 12
616263646566670061000102
 [2008-02-03 20:43 UTC] chx1975 at gmail dot com
I just ran Wez's test code and got 006100 so it seems I am unable to reproduce this bug. PECL Module version => 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $

SQLite library => 3.4.2
 [2008-02-03 22:33 UTC] chx1975 at gmail dot com
Based on http://netevil.org/blog/2005/oct/lob-support-added-to-pdo-oci-in-php-5-1-cvs-finally I believe maciej dot pijanka at gmail dot com wants this code:

$db = new PDO(...);
$binarydata = "abcdefg\x00a\x00\x01\x02";
$db->exec('CREATE TABLE test (data bytea, comment varchar(64), len integer)');
$db->beginTransaction();
$a = tempnam('.', 'pdo');
file_put_contents($a, $binarydata);
$stmt = $db->prepare("INSERT INTO test (data, comment, len) VALUES (:data, :comment, :len)");
$stmt->bindParam(':data', $blob, PDO::PARAM_LOB);
$comment = 'lob';
$stmt->bindParam(':comment', $comment);
$len = filesize($a);
$stmt->bindParam(':len', $len);
$blob = fopen($a, 'rb');
$stmt->execute();
$db->commit();
unlink($a);
print "\nFetching and comparing data\n";
# with fetchall resources don't work too
$stmt = $db->prepare("select * from test");
$stmt->execute();
$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES,true);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $buffer = '';
        var_dump($row);
        if(is_resource($row['data'])) {
                $buffer = stream_get_contents($row['data']);
        } else
                $buffer = $row['data'];
        print "Test `".$row['comment']."' StrCmp:";
        print "".(strcmp($binarydata,$buffer) == 0 )?"equal":"fail";
        print " length stored in db: ". $row['len'] . ", returned data len: ".strlen($buffer)."\n";
        echo bin2hex($buffer)."\n";
}
$db->exec('DROP TABLE test');
 [2008-02-03 22:40 UTC] chx1975 at gmail dot com
My final note here is that you can avoid a temporary file with the php://memory stream.
$stmt->bindParam(':data', $blob, PDO::PARAM_LOB);
$stmt->bindParam(':len', $len);
$blob = fopen('php://memory', 'a');
$len = fwrite($blob, $binarydata);
rewind($blob);
 [2008-02-06 20:58 UTC] nospam dot list at unclassified dot de
So, here we are. I can confirm that PDO SQLite truncates BLOB data at the first \x00 character. It's not binary-safe. I tried to write a JPEG file and it's only 4 bytes long. Looking at the file in a hex viewer, the byte no. 5 is a NUL.

PHP version: 5.2.3 (from xampp) on Windows XP
Not quite up-to-date, but this bug isn't marked as fixed so the issue is the same. It really does exist, for those in doubt.

This is a rather old bug and it's still in discussion. Is there any progress already? Will it be fixed or is it considered "irrelevant"? (I'm asking this because I see so many severe bugs being forgotten in many public bug trackers.)
 [2008-02-07 09:44 UTC] scottmac@php.net
There is a patch attached to another bug which deals with this. http://bugs.php.net/bug.php?id=42443
 [2008-03-07 17:16 UTC] scottmac@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

In 5.3+ at the moment.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 13:01:29 2024 UTC