php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76815 PDOStatement cannot be GCed/closeCursor-ed when a PROCEDURE resultset SIGNAL
Submitted: 2018-08-30 08:38 UTC Modified: 2020-12-10 09:47 UTC
Votes:3
Avg. Score:4.7 ± 0.5
Reproduced:3 of 3 (100.0%)
Same Version:0 (0.0%)
Same OS:2 (66.7%)
From: monier at reinom dot com Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 7.2.9 OS: Windows Linux
Private report: No CVE-ID: None
 [2018-08-30 08:38 UTC] monier at reinom dot com
Description:
------------
When a MySQL procedure has a resultset that calls a function (SELECT tst2()) and when that function SIGNALs an error (because it calls the MySQL SIGNAL statement directly or because it has an invalid RETURN type) then the PDO statement seems "stuck": you cannot call closeCursor on it, and it cannot even be garbage collected by PHP.

It *might* lead to a security issue if user is able to make the MySQL function SIGNAL (like, by being able to have the MySQL function return a too-long string like their email, message, or pseudo): in such case, the PHP process is stuck, and if you run this in parallel, you can easily freeze a server for a while.

Test script:
---------------
<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test_sql', 'mta', 'mta', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

$pdo->query('DROP FUNCTION IF EXISTS tst');
$pdo->query('DROP PROCEDURE IF EXISTS tst2');
$pdo->query('DROP PROCEDURE IF EXISTS tst3');
$pdo->query('CREATE FUNCTION tst() RETURNS VARCHAR(5) BEGIN RETURN \'x12345\'; END');
//$pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT * FROM (SELECT tst()) AS t; END');
$pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT tst(); END');

(function () use ($pdo) {
	$st = $pdo->prepare('CALL tst3()');
	try {
		$st->execute();
	} catch (Throwable $ex) {
		var_dump("tst3: " . $ex->getMessage());
		// $st->closeCursor(); // Stucks too
	}
	var_dump('end of function');
})(); // Stucks here until you KILL the connection from another MySQL client
var_dump('end');

Expected result:
----------------
The statement should be closable ($st->closeCursor() should not stuck) or at least, the $st variable should be garbage collectable.

Actual result:
--------------
Command: php -f C:\Users\212636336\Desktop\pdo-stuck.php

Result:
C:\Users\212636336\Desktop\pdo-stuck.php:20:
string(99) "tst3: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'tst()' at row 1"
==> This is expected

C:\Users\212636336\Desktop\pdo-stuck.php:23:
string(15) "end of function"
==> This is expected

==> But process is stuck forever now
==> If you KILL the connection from another MySQL client, then you get:

Warning: {closure}(): MySQL server has gone away in C:\Users\212636336\Desktop\pdo-stuck.php on line 24

Call Stack:
    0.0043     389648   1. {main}() C:\Users\212636336\Desktop\pdo-stuck.php:0
    0.0326     405464   2. {closure:C:\Users\212636336\Desktop\pdo-stuck.php:14-24}() C:\Users\212636336\Desktop\pdo-stuck.php:24


Warning: {closure}(): Error reading result set's header in C:\Users\212636336\Desktop\pdo-stuck.php on line 24

Call Stack:
    0.0043     389648   1. {main}() C:\Users\212636336\Desktop\pdo-stuck.php:0
    0.0326     405464   2. {closure:C:\Users\212636336\Desktop\pdo-stuck.php:14-24}() C:\Users\212636336\Desktop\pdo-stuck.php:24

==> And script can end properly
C:\Users\212636336\Desktop\pdo-stuck.php:25:
string(3) "end"

------------------

The same happens with mysqli
The same happens with ERRMODE_SILENT
The same happens with or without EMULATE_PREPARE
The same happens without prepared statement
The same happens with PHP 5.6.35 or PHP 7.1.16 or PHP 7.2.4 or PHP 7.2.9
The same happens on Windows 7, on Windows 10, and on Linux

BUT

This does not happen if PDO calls the resultset query directly $pdo->prepare('SELECT tst()')
This does not happen if the SIGNAL occurs outside a PROCEDURE resultset $pdo->query('CREATE PROCEDURE tst3() BEGIN SET @i := tst(); END');
This does not happen if the SIGNAL occurs in a subquery $pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT * FROM (SELECT tst()) AS t; END');

It seems to not happen with MariaDB according to this StackOverflow comment:
https://stackoverflow.com/questions/52084085/pdostatement-is-stuck-when-a-resultset-throws-a-signal?noredirect=1#comment91124054_52084085

-----

I suspect that what actually happens is:
- PDO prepares the CALL statement
- PDO runs it to the server
- MySQL says to PDO "I'll give you a resultset, be prepared"
- PDOStatement says "OK, I'll wait for the data"
- MySQL SIGNAL because the function call fails
- PDO gets the SIGNAL, and throw an exception
- PHP tries to GCed the PDOStatement (or dev tries to closeCursor it)
- PDOStatement says "no way I'll delete myself: I'm awaiting for MySQL data..."
And since these data never comes, then PHP is stucked...
On KILL, the connection is lost so PDOStatement forgets about the resultsetdata, and says "ok, I can close my cursor/be GCed since I lost the connection"

This makes sense seeing that "SELECT * FROM (SELECT tst()) AS t" behaves properly: MySQL does not say "You'll now get a resultset" to PDOStatement, because MySQL fails when creating the subquery, so before "telling" PDOStatement that it will get a resultset.
(These last line are still purely speculative)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-04-01 12:38 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2020-12-10 09:47 UTC] nikic@php.net
The problem seems to be that we're expecting another result set, even though an error occurred during store_result.
 [2020-12-10 10:24 UTC] nikic@php.net
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=e450621f5e0a9490c287c8c71650f8b4d5ebbc2b
Log: Fixed bug #76815
 [2020-12-10 10:24 UTC] nikic@php.net
-Status: Verified +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 22:01:26 2024 UTC