php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72429 Inserting empty string into VARCHAR results in a VARCHAR full of whitespace
Submitted: 2016-06-16 12:40 UTC Modified: 2017-01-16 15:27 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: james at asgrim dot com Assigned: rangercairns (profile)
Status: Closed Package: ibm_db2 (PECL)
PHP Version: 5.6.22 OS: IBM i
Private report: No CVE-ID: None
 [2016-06-16 12:40 UTC] james at asgrim dot com
Description:
------------
When executing exactly the same query, one non-prepared and one prepared, the prepared statement results in a VARCHAR filled with spaces the same length as the VARCHAR.

For example, if we create a table with a single VARCHAR(32), and insert an empty string using a straight db2_exec, the value is correct. However, if we insert an empty string using db2_prepare and db2_bind_param, the resulting string is actually 32 whitespace characters.

Test script:
---------------
<?php

$conn = db2_connect('<replace>', '<replace>', '<replace>', [
  'i5_naming' => DB2_I5_NAMING_OFF,
  'i5_lib' => '<replace>',
]);

db2_exec($conn, 'CREATE OR REPLACE TABLE FOO (BAR VARCHAR(32));');
db2_exec($conn, 'DELETE FROM FOO');

// Works fine
db2_exec($conn, "INSERT INTO FOO VALUES ('')");

// Preparing and executing results in 32 space characters
$stmt = db2_prepare($conn, 'INSERT INTO FOO VALUES (?)');

$value = '';
db2_bind_param($stmt, 1, "value", DB2_PARAM_IN);

var_dump(db2_execute($stmt));

// Dump the results
$r = db2_exec($conn, "SELECT * FROM FOO");
while ($row = db2_fetch_assoc($r)) {
  echo json_encode($row, JSON_PRETTY_PRINT) . "\n";
}


Expected result:
----------------
bool(true)
{
    "BAR": ""
}
{
    "BAR": ""
}

Actual result:
--------------
bool(true)
{
    "BAR": ""
}
{
    "BAR": "                                "
}

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-06-16 12:48 UTC] james at asgrim dot com
Note - this behaviour is only observed when running on IBM i (we are running V7R1, with PHP 5.6). I cannot reproduce this using DB2 on Linux.
 [2016-06-16 16:25 UTC] james at asgrim dot com
If anyone is looking for a fix in the mean time, it looks like there is one available on: http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHP

In the section "PHP ibm_db2 test fix (binary php 5.6 or 5.5 for ZS 8.x — install correct version)". May be a suitable workaround for some until this can be patched upstream here.
 [2017-01-16 15:27 UTC] rangercairns@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: rangercairns
 [2017-01-16 15:27 UTC] rangercairns@php.net
Will be updated next version of ibm_db2 (likely 2.0.0).
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Apr 09 17:01:23 2020 UTC