Wrongly implicit conversion of unicode nvarchar(MAX) values

[sn4gg0r]

PHP version
7.4.21

PHP SQLSRV or PDO_SQLSRV version
pdo_srlsrv 5.10.0 sqlrv 5.10.0

Microsoft ODBC Driver version
msodbcsql 17 17.9.1.1-1 amd64 ODBC Driver for Microsoft(R) SQL Server(R)

SQL Server version
15.0.4198.2

Client operating system
Webserver: Debian GNU/Linux 10 (buster)

DB Server: Debian GNU/Linux 10 (buster) (mssql-server for linux) https://packages.microsoft.com/ubuntu/18.04/mssql-server-2019 + https://packages.microsoft.com/debian/10/prod/dists/buster/main/

Application: doctrine/dbal 2.13.8 doctrine/orm 2.7.5

Table schema
Only nvarchar(MAX) columns are affected (no other issues known)

Problem description Inserting a string value longer than 4000 Chars causes an SQL Exception: [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation

When rolling back to Version pdo_sqlsrv version 5.8.1 then everything works fine.

Expected behavior and actual behavior This problem only occurs, when working over PHP. An insert query directly executed e.g with mssql studio work just fine.

Since nvarchar(MAX) should be able to save up to 2GB per field, i would expect that there was no error thrown when inserting strings around 20'000 chars, also when using a version higher than 5.8.1.

[absci]

Hi, I tested on an Ubuntu 20.04 with pdo_sqlsrv 5.10.0, and seems not able to reproduce the error. There's some minor version difference which I think shouldn't make a huge difference. I have my environment and PHP script below. I'm just wondering do you get that error if you run the same script? Is it possible for you to enable and post the ODBC trace log? Also some sample code would be helpful too.

I'll test on Debian 10, and try to match your exact version later.

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64)  	
Apr 11 2022 16:24:07  	Copyright (C) 2019 Microsoft Corporation 	
Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>

cat composer.json 
{
    "require": {
        "doctrine/orm": "2.7.5",
        "doctrine/dbal": "2.13.8",
        "doctrine/annotations": "1.13.2",
        "symfony/yaml": "^5.4",
        "symfony/cache": "^5.4"
    },
    "autoload": {
        "psr-0": {"": "src/"}
    }
}

<?php
require_once 'vendor/autoload.php';

$connectionParams = [
    'dbname' => '',
    'user' => '',
    'password' => '',
    'host' => '',
    'driver' => 'pdo_sqlsrv',
];

$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

$long_unicode = str_repeat("PHP❤", 5000);
$conn->executeQuery("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U')) DROP TABLE [dbo].[TestTable]");

$conn->executeQuery("CREATE TABLE [dbo].[TestTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TestData] [nvarchar](max) NULL
)");

$conn->executeQuery("INSERT INTO dbo.TestTable VALUES (N'$long_unicode')");


$statement = $conn->executeQuery("SELECT TOP 1 TestData FROM TestTable");
$out = $statement->fetchAllAssociative();

print($out[0]["TestData"] == $long_unicode ? "Match":"Not Match");

?>

[cziegenberg]

We had the same problem with PHP 8.1 and pdo_srlsrv 5.10.0, but only in some cases. After comparing the code, we found the difference that triggers the truncation of long values inserted into nvarchar(max) columns. It seems to happen when a prepared statement is executed multiple times. Here's some dummy code...

Works (execute a prepared statement once):

// Prepare INSERT/UPDATE statement...
// Execute prepared INSERT/UPDATE statement...

Does not work (execute a prepared statement multiple times):

// Prepare INSERT/UPDATE statement...
foreach ($datasets as $dataset) {
    // Execute prepared INSERT/UPDATE statement...
}

Works again (recreate the prepared statement in the loop):

foreach ($datasets as $dataset) {
    // Prepare INSERT/UPDATE statement...
    // Execute prepared INSERT/UPDATE statement...
}

[jstolp]

Hello, i've also been encoutering this issue. I've adjusted your your script to ensure a correct repro. can you test it @absci

<?php

require_once 'vendor/autoload.php';

$connectionParams = [
    'dbname' => '',
    'user' => '',
    'password' => '',
    'host' => '',
    'driver' => 'pdo_sqlsrv',
];

$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

/** The Short string needs to be SMALL (if you increase this to 3000 the initial buffer is long enough so the error won't be shown  */
$short_string = str_repeat("X", 5);

$long_string = str_repeat("X", 4001);

$conn->executeQuery("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U')) DROP TABLE [dbo].[TestTable]");

$conn->executeQuery("CREATE TABLE [dbo].[TestTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TestData] [nvarchar](max) NULL
)");

$stmt=$conn->prepare("INSERT INTO dbo.TestTable VALUES (?)");
$stmt->execute(array($short_string));
// Uncommenting the line below will make the error disappear
// $stmt=$conn->prepare("INSERT INTO dbo.TestTable VALUES (?)"); // If you Re-Prepare the statement, the error won't be shown!

$stmt->execute(array($long_string));

[talkinnl]

Thanks to the above repro of @jstolp , I've been able to prove that this issue exists for multiple column definitions:

• NVARCHAR(MAX)
• VARCHAR(MAX)
• VARCHAR(4002) or higher.

It certainly looks like this can fix multiple GitHub tickets at once (1391, 1393, maybe 1371).

@absci : I've opened a PR which reproduces this for all 3 such columns: https://github.com/microsoft/msphpsql/pull/1402 , so you can fix this in a TDD way. The CI indeed currently reports a "SQLSTATE[22001]: [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation"

[talkinnl]

@absci , are you able to look into this? Or can you please provide some time frame? The reproductions are clear and I think it’s a pretty serious issue (will fix multiple github issues actually).

For example, Doctrine reuses Statements and can trigger this bug. Note: this is done in DB agnostic code, code which works correctly with other databases or with sqlsrv <= 5.9.

This is a blocker for upgrading to PHP8.1 for projects using Doctrine. And other frameworks or ORM libraries might have similar issues.

Thanks.

[sn4gg0r]

@absci sorry for the delay and thanks for your reply. I can confirm that the code which @jstolp provided will cause the error. For us, it currently works with the pdo_sqlsrv version 5.8.1, which does not produce the same issue.

Have a nice day y'all!

[absci]

@absci , are you able to look into this? Or can you please provide some time frame? The reproductions are clear and I think it’s a pretty serious issue (will fix multiple github issues actually).

For example, Doctrine reuses Statements and can trigger this bug. Note: this is done in DB agnostic code, code which works correctly with other databases or with sqlsrv <= 5.9.

This is a blocker for upgrading to PHP8.1 for projects using Doctrine. And other frameworks or ORM libraries might have similar issues.

Thanks.

We don't have a timeframe at the moment. I'll be working on this issue soon.

[absci]

This issue should be the same as #1371. The fix has been merged into dev branch.