php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #56797 db2_*_errormsg() returns corrupted text on Windows
Submitted: 2006-01-24 18:12 UTC Modified: 2006-02-06 18:33 UTC
From: larry dot menard at rogers dot com Assigned: kfbombar (profile)
Status: Closed Package: ibm_db2 (PECL)
PHP Version: 5.1.1 OS: Windows XP, SP2
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: larry dot menard at rogers dot com
New email:
PHP Version: OS:

 

 [2006-01-24 18:12 UTC] larry dot menard at rogers dot com 
Description:
------------
DB2 is version 8, FP10.

When trying to insert a string containing a '\0' into a VARCHAR column, I get an error that a string has no ending delimiter.

In IBM newsgroup 'ibm.software.db2.udb', Knut Stolze discovered a problem common to both ODBC and 'ibm_db2'.  The problem is that function _php_db2_do_prepare() specifies SQL_NTS for the string length.  Thus, DB2 will search for the first '\0' inside the string to determine the length of the statement.  It finds the one in the string, effectively truncating the statement.

He suggested that if I use a parameter marker and explicitly bind the string it would work, and I have confirmed this is true.  (Simply using db2_prepare() and db2_execute() isn't sufficient... you need to use a parameter marker & db2_bind_param())  The value inserted into the column is truncated at the '\0', but that's fine (at least for me).

Also, note the corruption in the db2_stmt_errormsg() output in the failing scenario.

Reproduce code:
---------------
See this script:

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/testNullInsert.php.txt

Update the dbname, userid and password on lines 9 through 11.

Note: Toggle between prepare()/execute() and exec() by changing $separate_prep_and_exec on line 7.

Run it with arg 'ibm_db2':

   php testNullInsert.php ibm_db2


Expected result:
----------------
C:\>php testNullInsert.php ibm_db2

API in use = 'db2'.

Statement = "insert into mytable values ('The NULL character must be backslashed
   !')".



Actual result:
--------------
C:\>php testNullInsert.php ibm_db2

API in use = 'db2'.

Statement = "insert into mytable values ('The NULL character must be backslashed
   !')".

PHP Warning:  db2_exec(): Statement Execute Failed in d:\Temp\testNullInsert.php
 on line 85

Warning: db2_exec(): Statement Execute Failed in d:\Temp\testNullInsert.php on l
ine 85

[IBM][CLI Driver][DB2/NT] SQL0010N  The string constant beginning with "'
The NULL character must be backslashed" does not have an ending string delimiter
 SQLCODE=-10.2603

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-01-25 02:34 UTC] stolze at de dot ibm dot com 
A proper fix would actually take the length of the  
statement string that is extracted in "odbc_exec" via the  
call "zend_parse_parameters".  That length should be  
passed as additional parameter to "_php_db2_do_prepare" 
and used in that function.  Otherwise, the explicit 
SQL_NTS used in the call to "SQLPrepare" will cause DB2 to 
search for the end of the string, and the wrong end will 
be found, i.e. the one inside the parameter string for the 
VARCHAR column. 
 
p.s: What is the reason for not using SQLExecDirect for 
db2_exec?
 [2006-01-25 08:57 UTC] denials at gmail dot com 
Knut, that's exactly what I was thinking. Assigning to Kellen to investigate how painful it will be to handle this the right way.

BTW, Larry -- just out of interest, is there a particular reason you need to insert NULL characters as part of a string? Normally those would turn up as part of a binary file (like a JPEG), which it is much more efficient to insert using db2_bind_param() and the DB2_PARAM_FILE constant.
 [2006-01-25 10:14 UTC] denials at gmail dot com 
I'm actually trying out a relatively simple patch I whipped together -- also fixing a few other areas where we use SQL_NTS rather than the explicit length of the string. Will commit if it seems to improve the situation. However, PHP might be interfering somewhat, as I'm still seeing the input strings getting truncated at the \0 character on the database side.

Knut, as to why we explicitly call SQLPrepare and SQLExecute for each db2_exec() statement rather than calling SQLExecDirect -- preparing the statement gives the server a chance to reuse the same access plan as the last time the statement was prepared and executed. Given the nature of PHP applications, there aren't a lot of adhoc queries being executed in a typical Web app, so there is a potentially significant performance gain to be realized by preparing each statement.
 [2006-01-25 10:25 UTC] denials at gmail dot com 
Okay, committed the patch for nulls in strings. Please try it out in the next snapshot.

Larry, is the db2_stmt_errormsg() corruption a separate issue (does it occur with every statement), or does it only occur for you with these cases of trying to insert strings that contain nulls?
 [2006-01-25 12:02 UTC] larry dot menard at rogers dot com 
Sorry if this post appears twice... my first attempt seems to have disappeared.

This field in Gallery is used to hold free-form User-entered data... from single characters to (up to 8k) strings like entire articles or maybe even code samples?  One of their Unit Tests tests inserting a string with a null, and it's failing on DB2.  They tell me that truncation is fine... they only diff the results up to the first null.  So I guess it's a matter of whatever DB2 *should* do with nulls... truncate or preserve.

The message corruption is a separate issue; I see it every time I print any error message.

Thanks again, folks.
 [2006-01-25 21:41 UTC] denials at gmail dot com 
Larry confirmed in a separate message that the NULL characters in an SQL statement are handled acceptably with the latest patch. Changing summary to reflect remaining focus of this bug on error message corruption from db2_*_errormsg() on Windows.
 [2006-01-25 21:57 UTC] larry dot menard at rogers dot com 
To be completely accurate, the entire string is inserted, preserving the null '\0'.  Doing a SELECT of that column appears like it is truncated, but it is actually not... DB2's CLP is now stumbling over the null.  Doing a SELECT LENGTH() of the column confirms that the entire string was inserted.
 [2006-01-26 20:48 UTC] denials at gmail dot com 
Checked in a nice little hack to fix the corruption on Windows. We were removing \n from error messages, but of course on Windows linefeeds are \r\n. Not sure why there's a linefeed in the first place, which is why I call this a hack...
 [2006-02-06 18:33 UTC] kfbombar at us dot ibm dot com 
This bug has been fixed in CVS.

In case this was a documentation problem, the fix will show up at the
end of next Sunday (CET) on pecl.php.net.

In case this was a pecl.php.net website problem, the change will show
up on the website in short time.
 
Thank you for the report, and for helping us make PECL better.

Modified hack to fix error message corruption in db2_*_errormsg().
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Wed Jan 15 16:01:31 2025 UTC