php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #68087 ODBC not reading DATE columns correctly
Submitted: 2014-09-23 19:36 UTC Modified: 2014-10-20 16:35 UTC
From: keyur@php.net Assigned: keyur (profile)
Status: Closed Package: ODBC related
PHP Version: 5.4.33 OS: Linux
Private report: No CVE-ID: None
 [2014-09-23 19:36 UTC] keyur@php.net
Description:
------------
SQL_DATE column data is coming back corrupted when it follows a WVARCHAR column in the SELECT statement.

Test script:
---------------
We have a test table with 2 columns: COUNTRY (WVARCHAR) and MONTH (DATE) and execute the statement: SELECT country, month FROM table;

Expected result:
----------------
Month should be the full date: yyyy-mm-dd

Actual result:
--------------
Month is corrupted: yyyy-mm<4 bytes of junk>

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-09-23 19:36 UTC] keyur@php.net
-Assigned To: +Assigned To: keyur
 [2014-09-23 19:37 UTC] keyur@php.net
Flipping the order in the SELECT is a temporary workaround:

SELECT month, country FROM table;
 [2014-10-07 21:24 UTC] keyur@php.net
-Status: Assigned +Status: Closed
 [2014-10-07 21:24 UTC] keyur@php.net
The fix for this bug has been committed.

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/.

 For Windows:

http://windows.php.net/snapshots/
 
Thank you for the report, and for helping us make PHP better.


 [2014-10-20 16:13 UTC] j dot faitw at yahoo dot com
I notice that this fix did not make it into the recent 5.6.2 or 5.5.18 releases(and not committed to 5.4).
Is this intentional?
If so is it likely to make it into 5.6.3?
 [2014-10-20 16:37 UTC] keyur@php.net
5.4 is closed for everything but security fixes, so I didn't push it there. 

The patch is queued for the next 5.5 and 5.6 release: 5.5.19 (https://github.com/php/php-src/blob/PHP-5.5/NEWS) and 5.6.3 (https://github.com/php/php-src/blob/PHP-5.6/NEWS)
 [2014-10-22 16:58 UTC] j dot faithw at yahoo dot com
I have discovered an issue with postgres and SQL_DESC_OCTET_LENGTH for constant string values.

e.g. given your 'foo' table used in the test
CREATE TABLE FOO(ID INT, VARCHAR_COL VARCHAR(100), DATE_COL DATE)');

select id,varchar_col,date_col,'string constant' from foo;

The postgres ODBC driver returns the 'string constant' column as
  coltype=SQL_VARCHAR
but the SQLColAttributes(result->stmt, (SQLUSMALLINT)(i+1), colfieldid,
                                NULL, 0, NULL, &displaysize);
call sets displaysize=0 when colfieldid=SQL_DESC_OCTET_LENGTH.

As displaysize=0 this causes an allocation of 1 byte of data storage for the field and junk data is returned.

If SQL_COLUMN_DISPLAY_SIZE is used then it returns
  The value set for MaxVarcharSize in the odbc.ini file(4000 in many examples).
  Or if MaxVarcharSize is not set it defaults to 255 unless the string is more than 255 bytes long in which case it returns the number of bytes in the constant string(I tried this with 300 euro symbols, 3 bytes each in UTF8, and got 900 as a result).

But for data from char/varchar columns SQL_COLUMN_DISPLAY_SIZE returns the number of characters(not bytes) which was the original multibyte character problem.

This may be more a postgres ODBC driver issue than a php bug but prior to the change to use SQL_DESC_OCTET_LENGTH this worked ok.

One option could be to check for displaysize==0 and try SQL_COLUMN_DISPLAY_SIZE instead.
 [2014-10-22 21:02 UTC] keyur@php.net
Hi,

I am unable to reproduce that with Vertica. But the problem exists with Postgres.

One workaround is to explicitly set the type as varchar like so: SELECT id, varchar_col, date_col, varchar 'random string'

The bug seems to be in the driver like you alluded to. When no cast is given, Postgres sets the type as PG_TYPE_UNKNOWN but when SQLColAttributes() is called with SQL_COLUMN_TYPE, it returns SQL_VARCHAR (http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l665)

The same "type-promotion" behavior is not found when fetching the octet length though. See here: http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l1275

Let me email the pgsql maintainers and see if they agree.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 09:01:30 2024 UTC