CRITICAL: Rethrown error from SQL is not thrown in PHP

[mvorisek]

System details:

• PHP: 7.4 - 8.1

• pdo_sqlsrv: 5.10.0

• SQL Server: latest docker image mcr.microsoft.com/mssql/server

• OS: Windows & linux

Table schema

CREATE TABLE [rate] ([id] INT IDENTITY NOT NULL, [dat] NVARCHAR(255), [bid] DOUBLE PRECISION, [ask] DOUBLE PRECISION, PRIMARY KEY ([id]))

Current behaviour & expected behavior

Consider valid PDO connection $pdo created with PDO::ERRMODE_EXCEPTION.

Code

$pdo->exec('insert into [rate] ([dat], [bid], [ask]) values (\'18/12/12\', (\'1.5\' + 0), (2.5 + 0))');

throws

PDOException: SQLSTATE[22018]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the varchar value '1.5' to data type int

this is expected and I post it here only to provide a proof the PDO connection and PHP error handling is working.

Now let's run the same query wrapped inside begin try SQL block, code

$pdo->exec('begin try begin
insert into [rate] ([dat], [bid], [ask]) values (\'18/12/12\', (\'1.5\' + 0), (2.5 + 0))
return
end end try
begin catch
throw
end catch');

does NOT throw an exception but it should throw exactly the same exception as the simple test code above.

as in web sandpit https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e683f9880d5b5b490854244f4ff95eb7 the error is correctly thrown/detected I belive the issue is not in SQL Server itself but in this php extension

Raising error is critical, with the current behaviour, system data can be easily corrupted. For example, when the execution of query above pass and the last AI is read, the AI of previous/different insert is read.

[absci]

Hi, I'm investigating this issue.

[absci]

From pdo doc(https://www.php.net/manual/en/pdo.exec.php), exec returns number of rows affected by the statement. When you do $pdo->exec('begin try begin ..., SQL Server returns 0 rows affected. You could use $count = $pdo->exec() to get the output, so it'll not throw an error. If you wish to get that error message, you could add SET NOCOUNT ON at the beginning of the statement, like this $pdo->exec('SET NOCOUNT ON begin try begin ....

[mvorisek]

so it'll not throw an error

@absci thank you for getting into this issue quickly

the main problem currently is an exception is not thrown, but it must be thrown - are you able to reproduce?

[absci]

so it'll not throw an error

@absci thank you for getting into this issue quickly

the main problem currently is an exception is not thrown, but it must be thrown - are you able to reproduce?

Yes, I can reproduce, I'll look into it.

[mvorisek]

@absci didn't you forget on this issue? It is quite critical as code usually relies on exception beiing thrown and fixing it is highly appreciated. Thanks in advance.

[absci]

@absci didn't you forget on this issue? It is quite critical as code usually relies on exception beiing thrown and fixing it is highly appreciated. Thanks in advance.

Hi, I haven't forgot this issue, sorry for the delay.

[ostrojen-wickedreports]

@absci I have the same issue. pdo_sqlsrv: 5.10.1 php: 8.1.20

May you provide some details about fixing?

[absci]

@absci I have the same issue. pdo_sqlsrv: 5.10.1 php: 8.1.20

May you provide some details about fixing?

I remember adding SET NOCOUNT ON to the beginning of the query, then you can get the error. Could you give a sample of your query?

[mvorisek]

@absci one complete repro is in the issue description, about a year ago, you promised to look into the fix, is now a good time?

[ostrojen-wickedreports]

@absci I have the same issue. pdo_sqlsrv: 5.10.1 php: 8.1.20 May you provide some details about fixing?

I remember adding SET NOCOUNT ON to the beginning of the query, then you can get the error. Could you give a sample of your query?

Our query looks like:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON
 
EXEC ProcedureName..

[absci]

The PHP driver relies on ODBC driver to interact with the server. We tested this query in an ODBC C++ application and got the same behavior. So, it may not be something we can fix in the PHP driver, since the server does not send the error back. It's probably better to avoid this type of query and use the try block in PHP script to handle errors.

I'll keep you posted on how we can proceed with this issue.

[absci]

I have revised this issue, the query here $pdo->exec('begin try begin ... is considered a batch query. So it's necessary to call $stmt->nextRowset() to get all the result set. nextRowset is only available for pdo->query.

For pdo->exec, I'll also fix that so it throw all the errors.

Here's a working example for pdo->query:

$tsql = "begin try begin
insert into [rate] ([dat], [bid], [ask]) values ('18/12/12', ('1.5\' + 0), (2.5 + 0))
return
end end try
begin catch
throw
end catch";

$stmt = $pdo->query($tsql);
$stmt->nextRowset();              // <-- This will throw PDOException: SQLSTATE[22018] ...

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);