php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69902 PDO_DBLIB No Exception or warning thrown on divide by zero statement
Submitted: 2015-06-22 17:08 UTC Modified: 2016-10-11 21:38 UTC
From: maxiwheat at gmail dot com Assigned: adambaratz (profile)
Status: Not a bug Package: PDO DBlib
PHP Version: 5.6.10 OS: Linux Slackware 14.1
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: maxiwheat at gmail dot com
New email:
PHP Version: OS:

 

 [2015-06-22 17:08 UTC] maxiwheat at gmail dot com
Description:
------------
When executing queries on Microsoft SQL Server (tested on 2008 and 2014), some queries that should fail and produce an exception (or a warning depending on PDO::ATTR_ERRMODE) just fail silently. This is a problem since we cannot catch or detect and handle them (logging etc.)

I know of "Divide by zero" errors that produce this unexpected behavior, there might be other ones.

However, some other types of SQL errors produce exceptions as expected, there seem to be some specific kind that don't get thrown.

Test script:
---------------
// Make sure all errors are displayed
ini_set('display_errors', '1');
error_reporting(E_ALL);

$dbh = new PDO ("dblib:host=myserver;dbname=MyDB","myUsername","MyPass");

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Dividing by zero should throw an exception
$stmt = $dbh->prepare("SELECT 1/0;");

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_BOTH);

var_dump($result);

Expected result:
----------------
An exception should be thrown.

Actual result:
--------------
No exception thrown

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-09-14 21:56 UTC] adambaratz@php.net
I did some testing with MSSQL + FreeTDS (TDS 7.2). The query result includes the "divide by zero" message, but it's labeled as a result (with a NULL value) rather than an error. This is why an exception doesn't bubble up. I'm following up with the FreeTDS team to get a better understanding of why this is and where the fix should be applied.
 [2016-09-14 21:56 UTC] adambaratz@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: adambaratz
 [2016-10-11 17:50 UTC] adambaratz@php.net
I misread the result before. There is a result packet, but it's followed by an info packet with a "Division by zero occurred." message. It's being ignored because the severity is 0. According to official docs, it should be 10. See message 3607:
https://technet.microsoft.com/en-us/library/cc645601(v=sql.105).aspx

Next hypothesis is that these queries run differently over dblib. This query should produce extended error info:
BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT ERROR_SEVERITY() AS ErrorSeverity;  
END CATCH;  
GO

When run in SSMS, it produces two rowsets: one with no columns, one with the error info:
ErrorNumber	ErrorSeverity	ErrorState	ErrorProcedure	ErrorLine	ErrorMessage
8134	16	1	NULL	2	Divide by zero error encountered.

When run in PHP, it produces two rowsets: one with no columns, one with a single NULL result.
 [2016-10-11 17:57 UTC] adambaratz@php.net
Above, the following query was actually used:
BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
 [2016-10-11 19:21 UTC] adambaratz@php.net
There are some relevant details in the documentation for a similar Perl module:
http://www.sommarskog.se/mssqlperl/mssql-sqllib.html#item_alwaysprint
 [2016-10-11 21:38 UTC] adambaratz@php.net
-Status: Verified +Status: Not a bug
 [2016-10-11 21:38 UTC] adambaratz@php.net
After some more digging, my conclusion is that pdo_dblib is behaving as it's supposed to. That is, it's handling the results from FreeTDS as they're returned. You have to tweak some session settings to get an error. This can be a little confusing since SSMS has different defaults.

<?

$db = new PDO(...);

$stmt = $db->query("
DECLARE @ANSI_WARN VARCHAR(3) = 'OFF';
IF ( (8 & @@OPTIONS) = 8 ) SET @ANSI_WARN = 'ON';

DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';

DECLARE @ARITHIGNORE VARCHAR(3) = 'OFF';
IF ( (128 & @@OPTIONS) = 128 ) SET @ARITHIGNORE = 'ON';

SELECT
  @ANSI_WARN AS ANSI_WARNINGS,
  @ARITHABORT AS ARITHABORT,
  @ARITHIGNORE AS ARITHIGNORE;
");

do {
  $rowset = $stmt->fetchAll();
} while ($stmt->nextRowset());
var_dump($rowset[0]); // off by default

// flip them on
$db->exec('
SET ARITHABORT ON
SET ARITHIGNORE ON
SET ANSI_WARNINGS ON
');

$stmt = $db->query('
BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_PROCEDURE() AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_MESSAGE() AS ErrorMessage;
END CATCH;  ');

// multiple rowsets will be returned
do {
  $rowset = $stmt->fetchAll();
} while ($stmt->nextRowset());

// the last rowset will contain the error info
var_dump($rowset[0]);

?>

Note that the TRY/CATCH is needed to trap the error:

<?

$stmt = $db->prepare('SELECT 1/0');

// the query will succeed
echo 'execute: ' . ($stmt->execute() ? 'y' : 'n') . "\n"; //

// it will have an empty result set
echo "data:\n";
var_dump($stmt->fetchAll());

// with a single column with type int
echo 'column count: ' . $stmt->columnCount() . "\n"; // 1
echo "column meta:\n";
var_dump($stmt->getColumnMeta(0)); // computed int

// you can get the divide-by-zero error if you know to ask
echo "error:\n";
var_dump($stmt->errorInfo());

?>
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Sat Nov 17 03:01:25 2018 UTC