ID: 41135
Comment by: maciej dot pijanka at gmail dot com
Reported By: rich at corephp dot co dot uk
Status: Assigned
Bug Type: PDO related
Operating System: Windows XP SP2
PHP Version: 5.2.1
Assigned To: wez
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[2007-04-21 03:28:18] [EMAIL PROTECTED]
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";
}
------------------------------------------------------------------------
[2007-04-20 18:04:33] 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-18 17:11:47] 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-18 16:55:34] [EMAIL PROTECTED]
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 16:46:46] 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.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=41135&edit=1
