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.

Can you clarify if you're using the sqlite that comes with PHP or if you
pulled it out of a separate PECL download?

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.

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();

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";
}

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

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

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');

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);

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.)

There is a patch attached to another bug which deals with this.
http://bugs.php.net/bug.php?id=42443

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.