php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40394 mssql_bind() converts empty strings to null
Submitted: 2007-02-07 22:36 UTC Modified: 2016-10-15 23:10 UTC
Votes:14
Avg. Score:4.9 ± 0.3
Reproduced:13 of 13 (100.0%)
Same Version:3 (23.1%)
Same OS:4 (30.8%)
From: rnerovich at gmail dot com Assigned:
Status: Wont fix Package: MSSQL related
PHP Version: 5.2.0 OS: XP/2003 server
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
49 - 32 = ?
Subscribe to this entry?

 
 [2007-02-07 22:36 UTC] rnerovich at gmail dot com
Description:
------------
When using mssql_bind() to pass an empty string to a stored procedure, the empty string is converted to NULL.  This problem has been reported by others, but either closed or marked as BOGUS, which is not the case, as I've reproduced this on both development and deployed systems.

Reproduce code:
---------------
CREATE PROCEDURE REC_PROC_INSERT_MS_INTF
	@UCODE UNIQUEIDENTIFIER, @PHONE VARCHAR(10), @ACCOUNT VARCHAR(25), @EALOC VARCHAR(38), @TIME DATETIME,
	@RECID UNIQUEIDENTIFIER, @PRBSTR VARCHAR(30)
AS
SELECT @UCODE as ucode, @PHONE as phone, 
	@ACCOUNT as account, @EALOC as ealoc, 
	@TIME as time,
	@RECID as recid, @PRBSTR as prbstr
RETURN 1
GO

$sp = mssql_init('REC_PROC_INSERT_MS_INTF',$dblink->dbresource);
        mssql_bind($sp,"RETVAL",&$rc,SQLINT4,TRUE,TRUE,4);
        mssql_bind($sp,"@UCODE",$ucode,SQLVARCHAR,FALSE,FALSE,strlen($ucode));
        mssql_bind($sp,"@PHONE",$phone,SQLVARCHAR,FALSE,FALSE,10);
        mssql_bind($sp,"@ACCOUNT",$account,SQLVARCHAR,FALSE,FALSE,25);
        mssql_bind($sp,"@EALOC",$ealoc,SQLVARCHAR,FALSE,FALSE,38);
        mssql_bind($sp,"@TIME",$currtime,SQLVARCHAR,FALSE,FALSE,strlen($currtime));
        mssql_bind($sp,"@RECID",$recId,SQLVARCHAR,FALSE,FALSE,strlen($recId));
        mssql_bind($sp,"@PRBSTR",$problem,SQLVARCHAR,FALSE,FALSE,30);
        $result = mssql_execute($sp);
        if($result){
            $row = mssql_fetch_array($result);
            global $log;
            $log->Writelog(var_export($row,true));
        }



Expected result:
----------------
I would expect the empty strings to stay empty.  This causes major problems with NON-NULLABLE SQL fields!!!

array (
  0 => '?k?#???M?T??S???',
  'ucode' => '?k?#???M?T??S???',
  1 => '',
  'phone' => '',
  2 => '',
  'account' => '',
  3 => '423998059-130628001',
  'ealoc' => '423998059-130628001',
  4 => '2007-02-07 16:20:54',
  'time' => '2007-02-07 16:20:54',
  5 => 'g?r}Q\'N??@I?*?',
  'recid' => 'g?r}Q\'N??@I?*?',
  6 => '',
  'prbstr' => '',
)

Actual result:
--------------
when any of the strings (not uniqueidentifiers) are set to an empty string ($phone = '') the following is received from the var_export()

array (
  0 => '?k?#???M?T??S???',
  'ucode' => '?k?#???M?T??S???',
  1 => NULL,
  'phone' => NULL,
  2 => NULL,
  'account' => NULL,
  3 => '423998059-130628001',
  'ealoc' => '423998059-130628001',
  4 => '2007-02-07 16:20:04',
  'time' => '2007-02-07 16:20:04',
  5 => 'g?r}Q\'N??@I?*?',
  'recid' => 'g?r}Q\'N??@I?*?',
  6 => NULL,
  'prbstr' => NULL,
)
the GUIDS look like garbage, but are correct.  The strings however are all NULL if they were sent in as ''

This is what var_export() reports if all strings are set to ' ' (one space)

array (
  0 => '?k?#???M?T??S???',
  'ucode' => '?k?#???M?T??S???',
  1 => ' ',
  'phone' => ' ',
  2 => ' ',
  'account' => ' ',
  3 => '423998059-130628001',
  'ealoc' => '423998059-130628001',
  4 => '2007-02-07 16:20:54',
  'time' => '2007-02-07 16:20:54',
  5 => 'g?r}Q\'N??@I?*?',
  'recid' => 'g?r}Q\'N??@I?*?',
  6 => ' ',
  'prbstr' => ' ',
)




Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-02-07 22:48 UTC] tony2001@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.2-win32-latest.zip


 [2007-02-07 23:19 UTC] rnerovich at gmail dot com
Just tried the new snapshot with the same result.

885: [02/07] [17:17:30] *** array (
  0 => '?k?#???M?T??S???',
  'ucode' => '?k?#???M?T??S???',
  1 => NULL,
  'phone' => NULL,
  2 => NULL,
  'account' => NULL,
  3 => '423998059-130628001',
  'ealoc' => '423998059-130628001',
  4 => '2007-02-07 17:17:21',
  'time' => '2007-02-07 17:17:21',
  5 => 'g?r}Q\'N??@I?*?',
  'recid' => 'g?r}Q\'N??@I?*?',
  6 => NULL,
  'prbstr' => NULL,
)
 [2007-02-07 23:27 UTC] tony2001@php.net
Assigned to the maintainer.
 [2007-06-21 10:12 UTC] smargroth at hotmail dot com
I have the same problem and it makes me a headache all the time.

This bug is very annoying, as empty string is normal and valid value,
and is different from NULL value. I have non-NULL-able columns, and I
cannot insert empty strings because of this stupid bug. 

The only workaround is to make NULL-able columns, but then I could not distinguish between omitted (NULL) or valid empty string.
 [2008-03-04 16:22 UTC] alexr at oplot dot com
Guys, I was fix (temporarily) this bug, please see the
http://bugs.php.net/bug.php?id=44325
 [2014-12-29 01:05 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: fmk +Assigned To:
 [2016-10-15 23:10 UTC] kalle@php.net
-Status: Open +Status: Wont fix
 [2016-10-15 23:10 UTC] kalle@php.net
With MSSQL being removed from PHP as of PHP7.0, and ext/mssql not having a maintainer, I'm gonna close this report as a Won't fix, until maybe one day it will find a new maintainer.

Alternatively you can use sqlsrv from Microsoft if you are on Windows, or pdo_dblib if you are on Unix.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Apr 26 01:01:30 2024 UTC