php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73725 Unable to retrieve value of varchar(max) type
Submitted: 2016-12-12 16:06 UTC Modified: 2016-12-19 22:12 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (100.0%)
From: honza dot hink at gmail dot com Assigned:
Status: Closed Package: ODBC related
PHP Version: 7.1.0 OS: Windows 10
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: honza dot hink at gmail dot com
New email:
PHP Version: OS:

 

 [2016-12-12 16:06 UTC] honza dot hink at gmail dot com
Description:
------------
If result set contains a column type varchar(max) then all functions (odbc_fetch_array, odbc_fetch_row etc.) return some random bytes.
It looks like buffer overrun.
If you change declaration varchar(max) to varchar(200) then it will work.
Very old bug, I hopped it will be finally fixed in PHP 7, but wainly.


Test script:
---------------
<?php
	$con=odbc_connect("Local64bit","you","yourPWD");
	$res=odbc_do($con,"set nocount on
	create table #t(i int, txt varchar(max))
	insert into #t values(101,'Any text')
	select i,txt from #t
	");
	$r=odbc_fetch_array($res);
	print_r($r);
?>

Expected result:
----------------
Array ( [i] => 101 [txt] => Any text ) 

Actual result:
--------------
Array ( [i] => 101 [txt] => €k ) 

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-12-12 16:10 UTC] honza dot hink at gmail dot com
Related to MS SQL - all versions from 2005 to 2014
 [2016-12-12 21:45 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2016-12-12 21:45 UTC] ab@php.net
Thanks for the report. For completeness, could you please also post an ODBC trace?

Thanks.
 [2016-12-13 15:07 UTC] honza dot hink at gmail dot com
-Status: Feedback +Status: Open
 [2016-12-13 15:07 UTC] honza dot hink at gmail dot com
ODBC Trace content (SQL.LOG):


php-cgi         fc8-204c	ENTER SQLSetEnvAttr 
		SQLHENV             0x0000000000000000
		SQLINTEGER                 201 <SQL_ATTR_CONNECTION_POOLING>
		SQLPOINTER                 2 <SQL_CP_ONE_PER_HENV>
		SQLINTEGER                   0 

php-cgi         fc8-204c	EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
		SQLHENV             0x0000000000000000
		SQLINTEGER                 201 <SQL_ATTR_CONNECTION_POOLING>
		SQLPOINTER                 2 <SQL_CP_ONE_PER_HENV>
		SQLINTEGER                   0 

php-cgi         fc8-204c	ENTER SQLAllocEnv 
		HENV *              0x000001100587A280

php-cgi         fc8-204c	EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
		HENV *              0x000001100587A280 ( 0x00000110053F8190)

php-cgi         fc8-204c	ENTER SQLAllocConnect 
		HENV                0x00000110053F8190
		HDBC *              0x000001100587A288

php-cgi         fc8-204c	EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)
		HENV                0x00000110053F8190
		HDBC *              0x000001100587A288 ( 0x00000110053F8270)

php-cgi         fc8-204c	ENTER SQLConnectW 
		HDBC                0x00000110053F8270
		WCHAR *             0x00000110053F6850 [      -3] "RamsesLocal64bit\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFDFD16CF80 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFDFD16CF80 [      -3] "******\ 0"
		SWORD                       -3 

php-cgi         fc8-204c	EXIT  SQLConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HDBC                0x00000110053F8270
		WCHAR *             0x00000110053F6850 [      -3] "RamsesLocal64bit\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFDFD16CF80 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFDFD16CF80 [      -3] "******\ 0"
		SWORD                       -3 

		DIAG [01000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'master'. (5701) 

		DIAG [01000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english. (5703) 

php-cgi         fc8-204c	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000110053F8270
		SQLHANDLE *         0x000001100585C118

php-cgi         fc8-204c	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000110053F8270
		SQLHANDLE *         0x000001100585C118 ( 0x0000011006583100)

php-cgi         fc8-204c	ENTER SQLGetInfoW 
		HDBC                0x00000110053F8270
		UWORD                        8 <SQL_FETCH_DIRECTION>
		PTR                 0x00000001729FC6F0
		SWORD                        4 
		SWORD *             0x00000001729FC640

php-cgi         fc8-204c	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000110053F8270
		UWORD                        8 <SQL_FETCH_DIRECTION>
		PTR                 0x00000001729FC6F0 ( 0x00000110000000BF)
		SWORD                        4 
		SWORD *             0x00000001729FC640 (4)

php-cgi         fc8-204c	ENTER SQLSetStmtOption 
		HSTMT               0x0000011006583100
		UWORD                        6 <SQL_CURSOR_TYPE>
		SQLPOINTER                 3 <SQL_CURSOR_STATIC>

php-cgi         fc8-204c	EXIT  SQLSetStmtOption  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000011006583100
		UWORD                        6 <SQL_CURSOR_TYPE>
		SQLPOINTER                 3 <SQL_CURSOR_STATIC>

php-cgi         fc8-204c	ENTER SQLExecDirect 
		HSTMT               0x0000011006583100
		UCHAR *             0x000001100586C0B8 [      -3] "set nocount on\ d\ a\ 9create table #t(i int, txt varchar(max))\ d\ a\ 9insert into #t values(101,'Any text')\ d\ a\ 9select i,txt from #t\ d\ a\ 9\ 0"
		SDWORD                    -3

php-cgi         fc8-204c	EXIT  SQLExecDirect  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HSTMT               0x0000011006583100
		UCHAR *             0x000001100586C0B8 [      -3] "set nocount on\ d\ a\ 9create table #t(i int, txt varchar(max))\ d\ a\ 9insert into #t values(101,'Any text')\ d\ a\ 9select i,txt from #t\ d\ a\ 9\ 0"
		SDWORD                    -3

		DIAG [01S02] [Microsoft][SQL Server Native Client 11.0]Cursor type changed (0) 

php-cgi         fc8-204c	ENTER SQLNumResultCols 
		HSTMT               0x0000011006583100
		SWORD *             0x000001100585C128

php-cgi         fc8-204c	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000011006583100
		SWORD *             0x000001100585C128 (2)

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x000001100587A500
		SQLSMALLINT                256 
		SQLSMALLINT *       0x00000001729FC5A8
		SQLPOINTER          [Unknown attribute 1011]

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x000001100587A500
		SQLSMALLINT                256 
		SQLSMALLINT *       0x00000001729FC5A8 (1)
		SQLPOINTER          [Unknown attribute 1011]

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT                  2 <SQL_DESC_CONCISE_TYPE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x000001100587A610

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT                  2 <SQL_DESC_CONCISE_TYPE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x000001100587A610 (4) <SQL_INTEGER>

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT                  6 <SQL_DESC_DISPLAY_SIZE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x00000001729FC5A0

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  1 
		SQLSMALLINT                  6 <SQL_DESC_DISPLAY_SIZE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x00000001729FC5A0 (11)

php-cgi         fc8-204c	ENTER SQLBindCol 
		HSTMT               0x0000011006583100
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                0x0000011005869010
		SQLLEN                    12
		SQLLEN *            0x000001100587A608

php-cgi         fc8-204c	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000011006583100
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                0x0000011005869010
		SQLLEN                    12
		SQLLEN *            0x000001100587A608 (0)

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x000001100587A618
		SQLSMALLINT                256 
		SQLSMALLINT *       0x00000001729FC5A8
		SQLPOINTER          [Unknown attribute 1011]

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x000001100587A618
		SQLSMALLINT                256 
		SQLSMALLINT *       0x00000001729FC5A8 (3)
		SQLPOINTER          [Unknown attribute 1011]

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT                  2 <SQL_DESC_CONCISE_TYPE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x000001100587A728

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT                  2 <SQL_DESC_CONCISE_TYPE>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          0x000001100587A728 (12) <SQL_VARCHAR>

php-cgi         fc8-204c	ENTER SQLColAttribute 
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          [Unknown attribute 1013]

php-cgi         fc8-204c	EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000011006583100
		SQLSMALLINT                  2 
		SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000000000000
		SQLPOINTER          [Unknown attribute 1013]

php-cgi         fc8-204c	ENTER SQLBindCol 
		HSTMT               0x0000011006583100
		UWORD                        2 
		SWORD                        1 <SQL_C_CHAR>
		PTR                0x0000011005801010
		SQLLEN                     1
		SQLLEN *            0x000001100587A720

php-cgi         fc8-204c	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000011006583100
		UWORD                        2 
		SWORD                        1 <SQL_C_CHAR>
		PTR                0x0000011005801010
		SQLLEN                     1
		SQLLEN *            0x000001100587A720 (0)

php-cgi         fc8-204c	ENTER SQLExtendedFetch 
		HSTMT               0x0000011006583100
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x00000001729FC680
		UWORD *             0x00000001729FC700

php-cgi         fc8-204c	EXIT  SQLExtendedFetch  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HSTMT               0x0000011006583100
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x00000001729FC680 (1)
		UWORD *             0x00000001729FC700 (5)

		DIAG [01004] [Microsoft][SQL Server Native Client 11.0]String data, right truncation (0) 

php-cgi         fc8-204c	ENTER SQLFreeStmt 
		HSTMT               0x0000011006583100
		UWORD                        1 <SQL_DROP>

php-cgi         fc8-204c	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000011006583100
		UWORD                        1 <SQL_DROP>

php-cgi         fc8-204c	ENTER SQLDisconnect 
		HDBC                0x00000110053F8270

php-cgi         fc8-204c	EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000110053F8270

php-cgi         fc8-204c	ENTER SQLFreeConnect 
		HDBC                0x00000110053F8270

php-cgi         fc8-204c	EXIT  SQLFreeConnect  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000110053F8270

php-cgi         fc8-204c	ENTER SQLFreeEnv 
		HENV                0x00000110053F8190

php-cgi         fc8-204c	EXIT  SQLFreeEnv  with return code 0 (SQL_SUCCESS)
		HENV                0x00000110053F8190
 [2016-12-19 11:19 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2016-12-19 11:19 UTC] ab@php.net
Thanks for posting the trace. Please pastebin it somewhere next time. On my side, I cannot reproduce the wrong behavior, please check https://gist.github.com/weltling/a8b7087b78bb059b909e1c073862ceb2#file-bug73725-log-L211 . In your variant, it tells

DIAG [01004] [Microsoft][SQL Server Native Client 11.0]String data, right truncation (0) 

Is the PHP you use 64-bit build as well? Probably shouldn't work otherwise, but anyway. Some environment/configuration difference might cause this, probably more about it. Or some other code, surrounding the reproducer. Also seems we've used different drivers. I'd suggest you to check and compare the traces, the environment difference is for sure to read from there.

Thanks.
 [2016-12-19 22:12 UTC] honza dot hink at gmail dot com
-Status: Feedback +Status: Open
 [2016-12-19 22:12 UTC] honza dot hink at gmail dot com
Of course, the 64-bit architecture is used in both PHP and ODBC, but the same behavior is in 32-bit versions. You used different driver "ODBC SQL Server Driver" that has worse bugs. I used the newest "SQL Server Native Client 11.0".

The worse bug is - text column type must be placed at the end in select list else error is reported. See my comment here:

http://stackoverflow.com/questions/6361340/java-sql-sqlexceptionmicrosoftodbc-driver-manager-invalid-descriptor-in/31963700#31963700

Both errors are in this drivers for >7 years and to say truth - I dont believe much I will live to see the see a fix.
 [2017-03-21 14:23 UTC] david dot js dot campbell at gmail dot com
Having the exact same issue (PHP 5.6, reproducible on any version as it seems).   The issue is we cannot use the native {SQL Server} driver as it does not support TLS 1.2+

So far the only solution is to cast the column to text when selecting, which is not a solution at all.
 [2018-01-19 11:43 UTC] Jan_Oonk at hotmail dot com
Had the same problem.
Simple to recreate:
make a table in Microsoft SQL Server Express (64-bit) v13.0.4001.0 NT x64
with a column varchar(max)

I used 
Apache/2.4.18 (Win64) 
PHP/7.0.3 
PDO with {ODBC Driver 13 for SQL Server}

Do a simple select query on the column and echo it and you will notice the text will be garbled.

The solution was to use a fixed size varchar column for example varchar(1024)
 [2018-01-19 11:57 UTC] honza dot hink at gmail dot com
I am afraid the problem will remain open for next decade too.
 [2018-01-22 16:05 UTC] ab@php.net
Automatic comment on behalf of ab
Revision: http://git.php.net/?p=php-src.git;a=commit;h=2384ade53c9ddb45b56e803a8d8494737732b84b
Log: Fixed bug #73725 Unable to retrieve value of varchar(max) type
 [2018-01-22 16:05 UTC] ab@php.net
-Status: Open +Status: Closed
 [2018-03-06 22:06 UTC] c141483 at nwytg dot com
The problem is still relevant and not solved :(

In fact, the problem is solved only for the varhar(max)
If a varchar column has a fixed size (for example, varhar (20)) and it contains 20 characters of text (completely filled), then the problem with buffer overflow and displaying random bytes again gets out.

It is clear that STILL is allocated less memory than necessary.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Nov 04 17:01:28 2024 UTC