Error "String data, right truncation" when reusing the same prepared statemnt many times under PHP 8.1 with pdo_sqlsrv 5.10 beta1

[acelaya]

PHP Driver version or file name

5.10.0beta1

SQL Server version

I'm using docker image mcr.microsoft.com/mssql/server:2019-latest.

Client operating system

Alpine.

PHP version

8.1 RC6

Microsoft ODBC Driver version

17.5.2.2

Table schema

Included in repo with reproducible sandbox: ddl.sql

CREATE TABLE shlink_test.dbo.domains (
     id bigint IDENTITY(1,1) NOT NULL,
     authority nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     base_url_redirect nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     regular_not_found_redirect nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     invalid_short_url_redirect nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT PK__domains__3213E83F512B36BA PRIMARY KEY (id)
);

Problem description

It took me some time to reproduce this on a small env, and I'm still not completely sure if the error is on the driver itself, or on how the DBAL library I use (doctrine) is handling prepared statements internally, but this is reproducible only with PHP 8.1 and pdo_sqlsrv 5.10. With PHP 8.0 and pdo_sqlsrv 5.9 everything works as expected.

Expected behavior and actual behavior

Expected behavior: Reusing the same prepared statement with new bind params, allows for the same query to be run again.

Actual behavior: The second time the same prepared statement is used, the error [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation is thrown.

Repro code or steps to reproduce

I have prepared a small repository with everything needed to reproduce the steps. The README file explains how to start 3 docker containers, one with MS SQL server, one with PHP 8.0, and one with PHP 8.1, and then how to create the database, and run a simple PHP script in both PHP containers.

But in short, this is the script:

<?php

declare(strict_types=1);

var_dump(PHP_VERSION);

$pdo = new PDO('sqlsrv:server=mssql,1433;Database=shlink_test', 'sa', 'Passw0rd!');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$st = $pdo->prepare(
    'INSERT INTO domains (authority, base_url_redirect, regular_not_found_redirect, invalid_short_url_redirect) VALUES (?, ?, ?, ?)'
);
$authority = 'foo.com';
$base = null;
$notFound = null;
$invalid = null;
$st->bindParam(1, $authority);
$st->bindParam(2, $base);
$st->bindParam(3, $notFound);
$st->bindParam(4, $invalid);
$st->execute();

$authority = 'detached-with-redirects.com';
$base = 'foo.com';
$notFound = 'bar.com';
$invalid = null;
$st->bindParam(1, $authority);
$st->bindParam(2, $base);
$st->bindParam(3, $notFound);
$st->bindParam(4, $invalid);
$st->execute();

[yitam]

Thanks @acelaya for the repro. We will investigate and get back to you on this.

[yitam]

Hi @acelaya

I was able to reproduce this issue with PHP 8.0 and pdo_sqlsrv 5.10.0beta1, but it seems that the recent pull request #1311 has fixed this issue. I ran your script and successfully inserted the records, as shown below:

We are planning to release 5.10.0beta2 with this fix in early December. If you want to test it out earlier, you can apply the patch from the aforementioned pull request.

Hope this helps, and thanks again for the repro scenario!

[acelaya]

Thanks!

I can wait for beta2, and will test it then.

[yitam]

Sure! Will update you with the release

[yitam]

5.10.0-beta 2 is just released. Please give it a try @acelaya

[acelaya]

Thanks for the heads up!

[acelaya]

Works!

[yitam]

Closing this now please feel free to ask us to reopen.