php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44278 ODBC: nvarchar(max) mangled
Submitted: 2008-02-28 00:51 UTC Modified: 2020-10-01 11:21 UTC
Votes:19
Avg. Score:4.6 ± 0.7
Reproduced:17 of 17 (100.0%)
Same Version:6 (35.3%)
Same OS:9 (52.9%)
From: ethan dot nelson at ltd dot org Assigned:
Status: Re-Opened Package: ODBC related
PHP Version: 7 OS:
Private report: No CVE-ID: None
 [2008-02-28 00:51 UTC] ethan dot nelson at ltd dot org
Description:
------------
I'm using PDO and the ODBC extension.  Also I've installed the latest version of the SQL 2005 client tools so that I could create a System DSN using the SQLNative driver.

That in place, I've run into trouble when selecting data out of nvarchar(max) fields.  nvarchar works fine, nvarchar(max) fails.

Additionally, it appears that I can insert data into these columns without issue.

Try something simple... 



Reproduce code:
---------------
$pdo = new PDO("odbc:systemdsn");
$query = "SELECT CAST('asdfasdfasdf' AS nvarchar) AS good, CAST('asdfasdfasdf' AS nvarchar(max)) AS garbled";
$poo = $pdo->prepare($query);
$poo->execute();
$row = $poo->fetch(PDO::FETCH_ASSOC);

print_r($row);

Expected result:
----------------
Array ( [good] => asdfasdfasdf [garbled] => asdfasdfasdf )

Actual result:
--------------
Array ( [good] => asdfasdfasdf [garbled] => ��L �L��� )

Patches

varcharmax-fix-v2.diff (last revision 2017-11-18 11:30 UTC by chris at ocproducts dot com)
varcharmax-fix.diff (last revision 2017-11-17 03:35 UTC by chris at ocproducts dot com)

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-10-28 20:21 UTC] ethan dot nelson at ltd dot org
<?php

ini_set("display_errors","yes");

$poo = new PDO("odbc:DEVELOPMENT");

$query = "SELECT CAST('asdfasdfasdf' AS nvarchar) AS good, 
CAST('asdfasdfasdf' AS nvarchar(max)) AS garbled";
$stm = $poo->prepare($query);
echo $query."<br>\n";

$id = 1;
$stm->bindParam(':id',$id,PDO::PARAM_INT);
$id2 = 1;
$stm->bindParam(':id2',$id2,PDO::PARAM_INT);

echo "<pre>\n";
if ($stm->execute()) print_r($stm->fetchAll(PDO::FETCH_ASSOC));
else print_r( $stm->errorInfo());
echo "</pre>\n";

phpinfo();

?>

-----------Results in:

SELECT CAST('asdfasdfasdf' AS nvarchar) AS good, CAST('asdfasdfasdf' 
AS nvarchar(max)) AS garbled
Array
(
    [0] => Array
        (
            [good] => asdfasdfasdf
            [garbled] => ? ?
        )

)

PHP Version 5.2.7RC2-dev
 [2009-04-25 14:54 UTC] jani@php.net
Please try using this CVS snapshot:

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

  http://windows.php.net/snapshots/


 [2009-05-03 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2009-05-19 22:51 UTC] ethan dot nelson at ltd dot org
No difference in behavior from what I can see.  Can you reproduce the bug on your end?  My screen reports the following using my reproduce code (with a few lines from the phpinfo command()):

SELECT CAST('asdfasdfasdf' AS nvarchar) AS good, CAST('asdfasdfasdf' AS nvarchar(max)) AS garbled

Array
(
    [0] => Array
        (
            [good] => asdfasdfasdf
            [garbled] => &#65533;?[(?[&#65533;&#65533;&#65533;
        )

)

PHP Logo
PHP Version 5.2.10-dev

System 	Windows NT LTD-PWWW2 5.2 build 3790
Build Date 	May 19 2009 11:39:28
Configure Command 	cscript /nologo configure.js "--enable-snapshot-build" "--enable-debug-pack" "--with-snapshot-template=d:\php-sdk\bin\\..\snap_5_2\vc6\x86\template" "--with-php-build=d:\php-sdk\bin\\..\snap_5_2\vc6\x86\php_build" "--with-pdo-oci=D:\php-sdk\oracle\instantclient10\sdk,shared" "--with-oci8=D:\php-sdk\oracle\instantclient10\sdk,shared"
Server API 	ISAPI
 [2009-05-26 06:52 UTC] ian at mutexlabs dot com
I can confirm this bug on Windows 2003, PHP 5.2.7, 5.2.9 and the 5.2.10-dev release linked in this bug.

I'm actually using the odbc_* functions, not PDO, but the behaviour is the same.

I worked around the problem by rebuilding the database using nvarchar(255) instead of nvarchar(max) types - it was safer than modifying the PHP app.

I saw similar (corruption) behaviour on varchar and char types, though I didn't investigate closely.

text columns are OK, but unsuitable for my application.
 [2009-05-26 18:56 UTC] ethan dot nelson at ltd dot org
The following article is important even though it has to do with 
encryption.  The bug report exposes what PDO is using to execute 
queries, sp_prepexec.  The comment from an MS moderator is that it is 
an unsupported feature.  There may be another choice for use by PDO 
than prepexec.

http://social.msdn.microsoft.com/Forums/en-
US/sqlsecurity/thread/e7e54926-27d5-4c84-99af-a5335c72ef3c
 [2010-03-31 18:09 UTC] tidelipop at gmail dot com
Well, when will this bug be fixed!? I need to use this now!
/Andreas
 [2013-06-12 04:53 UTC] ssufficool@php.net
-Summary: nvarchar(max) mangled +Summary: ODBC: nvarchar(max) mangled
 [2014-01-01 12:52 UTC] felipe@php.net
-Package: PDO related +Package: PDO ODBC
 [2014-11-14 12:21 UTC] tawnos at darkhosts dot org
Same issue
PHP: 5.6.1
OS: Debian/Linux 7.6
DB: AzureSQL (MSSQL2012)
Driver: PDO_ODBC / MS ODBC Driver for Linux 11.0

What's more, we have detected that if you have TEXT column anywhere before nvarchar(max) in table, all castings from NVARCHAR(MAX) works as a charm, hovewer TEXT/NTEXT/IMAGE fields are deprecated and are already removed in MSSQL2014, so that's only a hack people with 2k8 and 2k12 databases can use.
 [2015-06-05 17:56 UTC] cmb@php.net
-Status: Open +Status: Duplicate -Assigned To: +Assigned To: cmb
 [2015-06-05 17:56 UTC] cmb@php.net
This is a duplicate of bug #54169 (well, actually it is the other
way round, but the other ticket already has a patch attached).
 [2017-11-17 03:34 UTC] chris at ocproducts dot com
That other bug ticket is closed (I think I know why), and the issue still happens in PHP7.

The issue is caused because of a bad assumption in the code - that only binary or long results require a full SQLGetData call (as opposed to SQLBindCol). In fact, if a vallen of <=0 is returned by reference from SQLBindCol, a SQLGetData will be required because this may mean SQL_NO_TOTAL (-4 value for vallen). This is actually a basic memory safety issue (vallen is used for malloc), so this is worse than just data mangling.

Certainly FreeTDS is for me using a normal VARCHAR result (not LONGVARCHAR) for VARCHAR(MAX). Hence breaking the aforementioned assumption.

I also got a zero value for vallen when running as an Apache module, which I had to treat with SQLGetData too. This is confusing to me, I think it may have something to do with asynchronous execution (this is how ODBC seems to be specified) but I can't find the PHP lib even touching that, so I'm unsure.

I have been able to fix the issue on my machine and am about to attach a patch.

Truth is this code could do with a major refactoring. There is a lot of copy and pasting, and my patch doesn't try and solve that.
 [2017-11-17 03:53 UTC] requinix@php.net
-Status: Duplicate +Status: Re-Opened -Operating System: win2k3 +Operating System: -PHP Version: 5.2.7 +PHP Version: 7 -Assigned To: cmb +Assigned To:
 [2017-11-18 11:30 UTC] chris at ocproducts dot com
On further testing, I found my patch was mostly correct, but insufficient. There is also another manifestation of the bug fixed in #69975, but for varchar(max) rather than nvarchar(max).

I am uploading a new patch that covers this, improves code commenting a bit, fixes a couple of mistakes in my prior patch, and is more defensive in case SQLBindCol never runs.

This resolves my confusion re "I also got a zero value", and I now understand the asynchronous execution relates specifically to the behaviour of SQLBindCol binding rows as the cursor advances (previously I thought it was something to do with app responsiveness in the face of DB latency).

This is stable for me now. I got the full Composr CMS test set passing on ODBC using SQL Server Express 2017, with webserver on a Mac using freeTDS. This was a long journey, quite a few issues, notably also my bug filed as #75534.
 [2017-11-18 11:34 UTC] cmb@php.net
> That other bug ticket is closed

To clarify: the ticket is private.
 [2018-01-22 16:31 UTC] ab@php.net
@chris at ocproducts dot com, thanks for the patch. Could you also add some tests, please? It concerns both nvarchar and varchar now.

Thanks.
 [2018-01-22 17:56 UTC] chris at ocproducts dot com
It'd take me a few days to get back into this and to the point of writing working PDO tests, I just don't have that kind of time I'm afraid.
 [2018-01-22 18:54 UTC] ab@php.net
I see. Naturally bugfixes are checked by the tests, which is also helpful for a review and to ensure future bugfixes don't breach existing behaviors. I was just going through some ODBC related tickets today, so stumbled upon this one. From what is done in the second patch - it is similar to what is going in ext/odbc. The place with the vallen < 0 is however something interesting, namely to know, how the negative length is produced. Perhaps it needs to be done in ext/odbc, too. Anyway, i might check your patch later then and see for tests, as we shouldn't loose the good work. Or perhaps you'll find some time for it.

Thanks.
 [2020-10-01 11:21 UTC] cmb@php.net
-Package: PDO ODBC +Package: ODBC related
 [2020-10-01 11:21 UTC] cmb@php.net
Both attached patches are for ext/odbc, and it seems to me this
issue has been resolved for ext/pdo_odbc, so I'm changing the
affected package.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Dec 03 05:01:32 2024 UTC