unicode string corruption when using PDO_ODBC with FreeTDS 1.3.6 and MS SQL Server 2016

[jnahmias]

Description

The following code:

<?php
// setup database connection
$u = getenv('DB_USERNAME');
$p = getenv('DB_PASSWORD');
$dsn = 'odbc:testUTF8';
$db = new \PDO($dsn, $u, $p);
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$constant_tests = [
    "SELECT CAST(0x72006f017e016f007600fd00 AS nvarchar(10)) AS pink_in_czech_binary_encoded_utf16;",
    "SELECT CAST(0x6b26 AS nvarchar(3)) AS Beamed_Eighth_Notes_binary_encoded_utf16;",
    "SELECT NCHAR(0x266b) AS Beamed_Eighth_Notes_nchar;",
    "SELECT N'♫' AS Beamed_Eighth_Notes_unicode_string_constant;",
    "SELECT N'růžový' AS pink_in_czech_unicode_string_constant;",
];
foreach($constant_tests as $t) {
    fwrite(STDERR, "Running query '$t'...\n");
    var_dump($db->query($t, \PDO::FETCH_ASSOC)->fetchAll());
}

Resulted in this output:

Running query 'SELECT CAST(0x72006f017e016f007600fd00 AS nvarchar(10)) AS pink_in_czech_binary_encoded_utf16;'...
array(1) {
  [0]=>
  array(1) {
    ["pink_in_czech_binary_encoded_utf16"]=>
    string(9) "růžový"
  }
}
Running query 'SELECT CAST(0x6b26 AS nvarchar(3)) AS Beamed_Eighth_Notes_binary_encoded_utf16;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_binary_encoded_utf16"]=>
    string(3) "♫"
  }
}
Running query 'SELECT NCHAR(0x266b) AS Beamed_Eighth_Notes_nchar;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_nchar"]=>
    string(3) "▒▒"
  }
}
Running query 'SELECT N'♫' AS Beamed_Eighth_Notes_unicode_string_constant;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_unicode_string_constant"]=>
    string(3) "▒▒"
  }
}
Running query 'SELECT N'růžový' AS pink_in_czech_unicode_string_constant;'...
array(1) {
  [0]=>
  array(1) {
    ["pink_in_czech_unicode_string_constant"]=>
    string(9) "růžo"
  }
}

But I expected this output instead:

Running query 'SELECT CAST(0x72006f017e016f007600fd00 AS nvarchar(10)) AS pink_in_czech_binary_encoded_utf16;'...
array(1) {
  [0]=>
  array(1) {
    ["pink_in_czech_binary_encoded_utf16"]=>
    string(9) "růžový"
  }
}
Running query 'SELECT CAST(0x6b26 AS nvarchar(3)) AS Beamed_Eighth_Notes_binary_encoded_utf16;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_binary_encoded_utf16"]=>
    string(3) "♫"
  }
}
Running query 'SELECT NCHAR(0x266b) AS Beamed_Eighth_Notes_nchar;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_nchar"]=>
    string(3) "♫"
  }
}
Running query 'SELECT N'♫' AS Beamed_Eighth_Notes_unicode_string_constant;'...
array(1) {
  [0]=>
  array(1) {
    ["Beamed_Eighth_Notes_unicode_string_constant"]=>
    string(3) "♫"
  }
}
Running query 'SELECT N'růžový' AS pink_in_czech_unicode_string_constant;'...
array(1) {
  [0]=>
  array(1) {
    ["pink_in_czech_unicode_string_constant"]=>
    string(9) "růžový"
  }
}

PHP Version

PHP 8.1.7

Operating System

Debian GNU/Linux testing/bookworm/unstable

[jnahmias]

FYI, here are my odbc settings:

$ odbcinst -q -s -l -n testUTF8
[testUTF8]
Driver=FreeTDS
Servername=utf8test
Database=tempdb
ClientCharset=UTF-8

and here is the snippet from freetds.conf:

[utf8test]
        host = myserver.example.org
        port = 1433
        tds version = auto
        client charset = UTF-8

[cmb69]

Can you please provide the ODBC trace of running that script?

[jnahmias]

Hi @cmb69 - thanks for looking at this. Attached is the odbc trace log.
unicode_test-pdo_odbc.log

[cmb69]

Thanks for the ODBC trace. It looks like this is not exactly based on the given script, but that does not really matter. The relevant point is that the driver claims "Data truncated" several times (that should throw an exception for PDO::ERRMODE_EXCEPTION, though), and this is due to https://bugs.php.net/81688. Could you please try @NattyNarwhal's patch; don't use that in production, though; just for testing whether it solves the issue. And you may want to compare the performance of PDO_ODBC with and without the patch.

[jnahmias]

Hi @cmb69 - I tried out the patch against a machine running php 7.4.30 and it fixed the issue.