Edit report at https://bugs.php.net/bug.php?id=44278&edit=1
ID: 44278
Comment by: chris at ocproducts dot com
Reported by: ethan dot nelson at ltd dot org
Summary: ODBC: nvarchar(max) mangled
Status: Re-Opened
Type: Bug
Package: PDO ODBC
PHP Version: 7
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2017-11-17 03:34:36] 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.
------------------------------------------------------------------------
[2015-06-05 17:56:20] 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).
------------------------------------------------------------------------
[2014-11-14 12:21:53] 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.
------------------------------------------------------------------------
[2010-03-31 18:09:09] tidelipop at gmail dot com
Well, when will this bug be fixed!? I need to use this now!
/Andreas
------------------------------------------------------------------------
[2009-05-26 18:56:03] 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
------------------------------------------------------------------------
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
https://bugs.php.net/bug.php?id=44278
--
Edit this bug report at https://bugs.php.net/bug.php?id=44278&edit=1