php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #46249 pdo_pgsql always fill in NULL for empty BLOB
Submitted: 2008-10-07 18:00 UTC Modified: 2008-10-14 01:53 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: hswong3i at gmail dot com Assigned: felipe (profile)
Status: Closed Package: PDO related
PHP Version: 5.2.6 OS: Debian
Private report: No CVE-ID: None
 [2008-10-07 18:00 UTC] hswong3i at gmail dot com
Description:
------------
When pdo_pgsql working with:
  1. BLOB field,
  2. running INSERT/UPDATE queries, and
  3. BLOB value with empty string ''

It will always fill NULL into database and so result as buggy. INSERT/UPDATE BLOB value with NULL will function correctly.

P.S. pdo_mysql is NOT buggy when running with similar programming logic, empty string pass into database correctly. Tested with PHP5.2.6 and PHP5.3-dev, both are buggy.

Reproduce code:
---------------
Please refer to http://drupal.org/node/316095#comment-1047830 for more information.

Expected result:
----------------
Empty string should fill into database correctly.

Actual result:
--------------
Empty string is now stored as NULL.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-10-11 00:26 UTC] felipe@php.net
I can't reproduce using 5.3CVS:

$db = new PDO('pgsql:host=localhost dbname=test', 'foo', 'bar', array(PDO::ATTR_STRINGIFY_FETCHES => TRUE));

$stmt = $db->prepare("INSERT INTO test_one_blob (blob1) VALUES (?)");
$stmt->execute(array(''));
var_dump($db->errorinfo());

$stmt = $db->prepare("INSERT INTO test_one_blob (blob1) VALUES (:foo)");
$x = '';
$stmt->bindParam(':foo', $x);
$stmt->execute();
var_dump($db->errorinfo());

$stmt = $db->query("INSERT INTO test_one_blob (blob1) VALUES ('')");
$stmt->execute();


teste=> select * from test_one_blob where blob1 is null;
 id | blob1 
----+-------
(0 registros)


Do you have tested using PDO directly, without Drupal's db_insert()?
 [2008-10-11 06:47 UTC] hswong3i at gmail dot com
According to http://www.php.net/manual/en/pdo.lobs.php, using PDO for BLOB INSERT/UPDATE should come with:
1. bindParam/bindColumn
2. PDO::PARAM_LOB
3. stream API, e.g. fopen(), fwrite(), rewind(), etc.

You may try to insert other value though you code snippet. The value should also NOT able to insert into PostgreSQL...

The bug reproduction code in http://drupal.org/node/316095#comment-1047830 is using pdo_pgsql directly. The db_insert() used is just a fake clone of Drupal CVS HEAD function with simplified programming logic.
 [2008-10-11 11:47 UTC] felipe@php.net
Oh sorry, you're right. I can reproduce it.
 [2008-10-11 19:05 UTC] felipe@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.


 [2008-10-12 16:39 UTC] hswong3i at gmail dot com
I try for both PHP5.2 and 5.3 from http://snaps.php.net/ (they both coming with the fix). They are now able to handle NULL and empty string BLOB INSERT/UPDATE. Thanks for the work.

BTW, it is now generate another issue that should belongs to http://bugs.php.net/bug.php?id=46274. Whenever fetching NULL or empty BLOB content from DB apache will crash with Segmentation fault. Maybe we still need some love for that :S
 [2008-10-12 16:51 UTC] felipe@php.net
Do you have tried after 13:03:31 2008 UTC? I've commited a complete fix to these issue in this time.

Thanks.
 [2008-10-12 17:44 UTC] hswong3i at gmail dot com
@felipe: Yes I did. I give a compare with cvs.php.net log message, and check if my PHP package coming with the fix. Fix is already included, #46249 is fixed but than buggy for #46274 as mentioned :S

I try both PHP in CLI and Apache2.2. BLOB INSERT/UPDATE are successful in both cases, but fetching NULL and empty string are both buggy...
 [2008-10-13 02:31 UTC] hswong3i at gmail dot com
Bug still exists, but preform in another way... Please try the following code snippet:

<?php

function _var_dump($msg) {
  print("<code><pre>");
  var_dump($msg);
  print("</pre></code>");
}

function db_insert($sql, $data) {
  global $active_db;
  $stmt = $active_db->prepare($sql);
  $blob = fopen('php://memory', 'a');
  fwrite($blob, $data);
  rewind($blob);
  $stmt->bindParam(':blob1', $blob, PDO::PARAM_LOB);
  $stmt->execute();
  $id = $active_db->lastInsertId('test_one_blob_id_seq');
  return $id;
}

function db_select($sql, $id) {
  global $active_db;
  $sql = 'SELECT * FROM "test_one_blob" WHERE "id" = :id';
  $stmt = $active_db->prepare($sql);
  $stmt->bindParam(':id', $id);
  $stmt->execute();
  return $stmt->fetch(PDO::FETCH_ASSOC);
}

$active_db = new PDO('pgsql:host=localhost dbname=DRUPAL_7', 'root', 'CHANGE', array(PDO::ATTR_STRINGIFY_FETCHES => TRUE));

// Test normal BLOB insert
$data = "This is\000a test.";
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', $data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
_var_dump("Test normal BLOB insert");
_var_dump($data);
_var_dump($return['blob1']);
_var_dump($data === $return['blob1']);

// Test NULL BLOB insert
$data = NULL;
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', $data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
_var_dump("Test NULL BLOB insert");
_var_dump($data);
_var_dump($return['blob1']);
_var_dump($data === $return['blob1']);

// Test empty BLOB insert
$data = "";
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', $data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
_var_dump("Test empty BLOB insert");
_var_dump($data);
_var_dump($return['blob1']);
_var_dump($data === $return['blob1']);

?>
 [2008-10-13 02:32 UTC] hswong3i at gmail dot com
It is now result as:

string(23) "Test normal BLOB insert"

string(15) "This is&#65533;a test."

string(15) "This is&#65533;a test."

bool(true)

string(21) "Test NULL BLOB insert"

NULL

string(0) ""

bool(false)

string(22) "Test empty BLOB insert"

string(0) ""

string(0) ""

bool(true)
 [2008-10-13 16:36 UTC] felipe@php.net
You aren't working with real NULL, but with a "empty resource". It's exaclty what happens to fwrite($fp, '') too.

Only using the code below you will get the NULL as expect:
$blob = null;
$stmt->bindParam(':blob1', $blob, PDO::PARAM_LOB);


Thanks.
 [2008-10-14 01:53 UTC] hswong3i at gmail dot com
Thanks felipe!! After update the code snippet of db_insert() as below, everything works fine:

<?php

function db_insert($sql, $data) {
  global $active_db;
  $stmt = $active_db->prepare($sql);
  if (is_null($data)) {
    $stmt->bindParam(':blob1', $data, PDO::PARAM_LOB);
  }
  else {
    $blob = fopen('php://memory', 'a');
    fwrite($blob, $data);
    rewind($blob);
    $stmt->bindParam(':blob1', $blob, PDO::PARAM_LOB);
  }
  $stmt->execute();
  $id = $active_db->lastInsertId('test_one_blob_id_seq');
  return $id;
}

?>
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 08:01:29 2024 UTC