php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33963 [PATCH] Binding some stored procedure parameters fails
Submitted: 2005-08-02 13:39 UTC Modified: 2005-11-18 21:49 UTC
Votes:3
Avg. Score:5.0 ± 0.0
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:0 (0.0%)
From: paul dot robinson at groupbc dot com Assigned: fmk
Status: Closed Package: MSSQL related
PHP Version: 5CVS-2005-08-06 OS: Linux (RHEL 4)
Private report: No CVE-ID:
 [2005-08-02 13:39 UTC] paul dot robinson at groupbc dot com
Description:
------------
Using FreeTDS 0.63 (latest stable release), calling mssql_bind can fail due to the restrictions placed on parameters by the dbrpcparam function from FreeTDS.
One specific example is an input parameter must have maxlen==-1. With the code as is in PHP 5.0.4 a NULL valued variable length type input parameter will always have maxlen==0 thereby always failing.

Diff below shows changes to ext/mssql/php_mssql.c that address this parameter mismatch.

2025,2026c2025,2029
<                       maxlen=0;
<                       datalen=0;
---
>                       datalen = 0;
>                       if (is_output)
>                               maxlen = -1;
>                       else
>                               maxlen = -1;
2030d2032
<                       datalen=Z_STRLEN_PP(var);
2031a2034,2046
> 
>                       if (is_output) {
>                         if ((maxlen > 0 ) && (maxlen < 256))
>                               datalen = maxlen;
>                         else {
>                               maxlen = 255;
>                               datalen = 255;
>                         }
>                       }
>                       else {
>                         maxlen = -1;
>                         datalen=Z_STRLEN_PP(var);
>                       }

Reproduce code:
---------------
mssql_bind($query, "@varchar1", $varchar1, SQLVARCHAR);
or
mssql_bind($query, "@varchar1", $varchar1, SQLVARCHAR, false, true, 57);

Expected result:
----------------
Sucessfully bind input variable.

Actual result:
--------------
"Unable to set parameter"
i.e. FAIL is returned by the call to dbrpcparam in PHP_FUNCTION(mssql_bind).

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-08-02 18:34 UTC] sniper@php.net
Please try using this CVS snapshot:

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


 [2005-08-02 18:51 UTC] paul dot robinson at groupbc dot com
Patches suggested are against Snapshot: php5-STABLE-200508021038.tar.gz

Although I haven't actually built an example using that snapshot today I have looked at the code which since it is unchanged would seem to exhibit the same problem.
 [2005-08-02 19:13 UTC] sniper@php.net
Provide patches in unified diff format (diff -u) and against PHP CVS HEAD (we're not likely to release any 5.0.x versions anymore). Put the diffs somewhere were people can download it and put the URLs here.

 [2005-08-05 16:52 UTC] paul dot robinson at groupbc dot com
Patch against php_mssql.c CVS version 1.149 can be found here:
http://cobweb.businesscollaborator.com/pdr/33963.patch

The values of maxlen and datalen set here have been verified to work with FreeTDS 0.63 and the latest CVS code as of 5/8/2005.
 [2005-08-06 01:31 UTC] sniper@php.net
Frank, check this out too..
 [2005-08-06 08:03 UTC] fmk@php.net
As far as I can read, the current code is following Microsofts definitions here:

maxlen 
For variable-length return parameters (when type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE), maxlen is the maximum desired byte length for the value parameter returned from a stored procedure. 
Set maxlen to -1 in any of these cases: 

For fixed-length return parameters (such as when type is SQLINT4). 
To pass a NULL fixed-length parameter value (such as when type is SQLINT4) to the stored procedure. 
For parameters that are not designated as return parameters. 
Set maxlen to 0 to pass a NULL variable-length parameter value (when type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE) to the stored procedure. 

datalen 
For variable-length return parameters (where type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE), datalen is the actual byte length of the value parameter sent to the stored procedure. The byte length should not count any null terminator. 
Set datalen to - 1 for non-NULL fixed-length parameters (such as when type is SQLINT4). 

Set datalen to 0 to pass a NULL parameter value (fixed or variable length) to the stored procedure

I think this should be fixed in FreeTDS
 [2005-08-09 09:01 UTC] freddyz77 at tin dot it
Fixed in CVS. Note however that if you call mssql_bind passing 7 parameters with maxlen = XXX and is_output = false you call dbrpcparam with status == 0 and maxlen = XXX, this is not correct, you should pass maxlen == -1 or maxlen == 0 (for NULL variable types... bad specifications but is what MS specify).

Change these lines

case 7: {
    zval **yyis_output, **yyis_null, **yymaxlen;

    if (zend_get_parameters_ex(7, &stmt, &param_name, &var, &yytype,
&yyis_output, &yyis_null, &yymaxlen)==FAILURE){
      RETURN_FALSE;
    }
    convert_to_long_ex(yytype);
    convert_to_long_ex(yyis_output);
    convert_to_long_ex(yyis_null);
    convert_to_long_ex(yymaxlen);
    type=Z_LVAL_PP(yytype);
    is_output=Z_LVAL_PP(yyis_output);
    is_null=Z_LVAL_PP(yyis_null);
    maxlen=Z_LVAL_PP(yymaxlen);
  }
  break;

with

case 7: {
    zval **yyis_output, **yyis_null, **yymaxlen;

    if (zend_get_parameters_ex(7, &stmt, &param_name, &var, &yytype,
&yyis_output, &yyis_null, &yymaxlen)==FAILURE) {
      RETURN_FALSE;
    }
    convert_to_long_ex(yytype);
    convert_to_long_ex(yyis_output);
    convert_to_long_ex(yyis_null);
    convert_to_long_ex(yymaxlen);
    type=Z_LVAL_PP(yytype);
    is_output=Z_LVAL_PP(yyis_output);
    is_null=Z_LVAL_PP(yyis_null);
    maxlen=Z_LVAL_PP(yymaxlen);
    if (!is_output)
      maxlen = -1;
  }
  break;

Or 

  if (is_output) {
    status=DBRPCRETURN;
  }

to

  if (is_output) {
    status=DBRPCRETURN;
  } else {
    maxlen = -1;
  }

(mssql_bind function)

freddy77
 [2005-08-09 18:56 UTC] akim at freedom dot com
Part of the problem is that ADODB passes maxlen=4000 by default when binding parameters (see adodb-mssql.inc.php line 580). This conflicts with the published specs, which state that input parameters should have maxlen = -1.

You can get away with that using FreeTDS < 0.63, but it looks like the 0.63 release of FreeTDS added extensive sanity checking and input verification to dbrpcparam(), all in accordance with the published specs. The end result is that one can't bind a string value to an input parameter.

Since this involves FreeTDS, core PHP and ADODB I'd say the best fix is the one that breaks the fewest existing installations ... I'd think that Freddy77's patch, which sets maxlen = -1 for all input parameters, will work with both FreeTDS 0.63 and 0.62 without breaking any existing ADODB code.

If I get a chance I'll try to test this on a Windows box (all of my tests have been done on Solaris 9 with SQL Server 2000 so far).
 [2005-08-09 19:54 UTC] akim at freedom dot com
I've tried this patch to php_mssql.c with good results (so far):


*** 2016,2021 ****
--- 2016,2033 ----
        mssql_ptr=statement->link;

        /* modify datalen and maxlen according to dbrpcparam documentation */
+
+       /* handle maxlen for input parameters */
+
+       if (!is_output) {
+               if (is_null) {
+                       maxlen=0;
+               }
+               else {
+                       maxlen=-1;
+               }
+       }
+
        if ( (type==SQLVARCHAR) || (type==SQLCHAR) || (type==SQLTEXT) ) {       /* variable-length type */
                if (is_null) {
                        maxlen=0;
 [2005-09-01 20:47 UTC] akim at freedom dot com
The problem with invalid values of maxlen for input parameters still exists in 5.1RC1.

Previously these published specs were quoted:

-----------------
maxlen
For variable-length return parameters (when type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE), maxlen is the maximum desired byte length for the value parameter returned from a stored procedure.
Set maxlen to -1 in any of these cases:

For fixed-length return parameters (such as when type is SQLINT4).
To pass a NULL fixed-length parameter value (such as when type is SQLINT4) to the stored procedure.
*** For parameters that are not designated as return parameters. *** (my emphasis)
Set maxlen to 0 to pass a NULL variable-length parameter value (when type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE) to the stored procedure.
-----------------

The specific problem is that maxlen should be set to -1 for "parameters that are not designated as return parameters" which is a very roundabout way of saying "input parameters." As currently written mssql_bind() sets maxlen = 0 for *all* parameters of variable-length type, including input parameters (see lines 2033-2043).

This poses a problem with FreeTDS >= 0.63 because an error will be returned if invalid values of maxlen are passed to dbrpcparam().
Previous versions of FreeTDS didn't perform much in the way of validation.

There were multiple problems with SP parameters in FreeTDS
0.63 discussed on the FreeTDS listserv around the same time. Some were bona fide FreeTDS bugs and were fixed; when Freddy noted "fixed in CVS" I think he referred to a FreeTDS bug in handling NULL value parameters, which was fixed in CVS for dblib/rpc.c on Aug. 8. This particular maxlen problem with variable-length input parameters seems to be a bug in php_mssql.c, complicated by certain default settings in ADODB.

Sorry if this seems repetitive but there have been no updates in weeks and I was afraid earlier comments might not have been worded clearly.
 [2005-11-18 21:49 UTC] fmk@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.


 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 17:01:58 2014 UTC