#33963 [Com]: [PATCH] Binding some stored procedure parameters fails

ID: 33963 Comment by: akim at freedom dot com Reported By: paul dot robinson at groupbc dot com Status: Assigned Bug Type: MSSQL related Operating System: Linux (RHEL 4) PHP Version: 5CVS-2005-08-06 Assigned To: fmk New Comment: 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; Previous Comments: ------------------------------------------------------------------------ [2005-08-09 18:56:35] 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 09:01:46] 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-06 08:03:32] 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-06 01:31:45] sniper@php.net Frank, check this out too.. ------------------------------------------------------------------------ [2005-08-05 16:52:00] 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. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/33963 -- Edit this bug report at http://bugs.php.net/?id=33963&edit=1