Showing posts with label PDO. Show all posts
Showing posts with label PDO. Show all posts

Thursday, July 18, 2013

PHP, MSSQL, nvarchar (fetch and write UTF-8 with ODBC)

Today I found pretty annoying thing in PHP MSSQL ODBC driver (I use it via PDO).
This driver doesn't support nvarchar fields (or nvarchar(max) only, doesn't matter for me - I can't change database schema just because of using PHP).
After 2 hours of googling I found solution. It's hack, but it works fine with nvarchar, decimal, varchar, int and others.

To read 

I found it accidentally in this blog and I'm very thankful to David Walsh:
SELECT CAST(CAST([field] AS VARCHAR(8000)) AS TEXT) AS field FROM table

To write

It's from StackOverflow:
$value = 'ŽČŘĚÝÁÖ';
$value = iconv('UTF-8', 'UTF-16LE', $value); //convert into native encoding 
$value = bin2hex($value); //convert into hexadecimal
$query = 'INSERT INTO some_table (some_nvarchar_field)  VALUES(CONVERT(nvarchar(MAX), 0x'.$value.'))'; 

Saturday, July 7, 2012

Incorrect replacement of prepared statements with numeric values in PDO

1. Returned by tester

Found interesting bug yesterday: absolutely correct query show correct result when run it without preparing and incorrect, if use prepared statements.
SELECT `operator`,`region` FROM `phone_code` WHERE `code` = :code AND `from_num` <= :from_num AND `to_num` >= :to_num
Replacement by strtr function shows that all is correct in replacements pairs array. It's unbelievable, but  it's true. Conditions <= and => just doesn't work with prepare.
Thanks to Mikhail Khmelev, tester in our team, he found differences in results (both results looks almost correct in particularly case).

I've tried to change names of statements, change SQL query, and even change spaces in query - I've spent around hour to find reason of this bug.

And, as you read in title, bug is in PHP: PDO numeric values of prepared statements replaces as strings, with quotation:

SELECT `operator`,`region` FROM `phone_code` WHERE `code` = '921' AND `from_num` <= '1234567' AND `to_num` >= '1234567'
So it's became obviously, why this query doesn't work correctly - numeric fields in DB can't be compared with text values by <= or => operators.


2. We have to fix it 

How to fix this behavior of PHP? I don't want to write my own "prepare" function - native should work faster and correctly and test much more heavily. But I have to find workaround, because program should work.

First solution was replace all numeric values of statements by strtr or str_ireplace, but tests shows that names of statements can be replaced incorrectly, when we have :statement and :statement_something_other. So I decide to replace by strtr with replacement pairs array and in add in that array all statements, not only numeric - not numeric statements will be replaced by itself and this replacement will exclude sub-strings replacement.
Final code you can find here: https://github.com/jamm/DataMapper/blob/master/MySQL/Gateway.php#L233 (function getCorrectPreparedQuery).

This bug exists in PHP since 2008: https://bugs.php.net/bug.php?id=44639
Thanks to PHP for this interesting bug and wasted time. Hope it will be fixed in next ten years.