php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #70949 SQL Result Sets With NULL Can Cause Fatal Memory Errors
Submitted: 2015-11-20 11:10 UTC Modified: 2015-11-24 09:04 UTC
From: s7g2vp2 at yahoo dot co dot uk Assigned: laruence (profile)
Status: Closed Package: MySQLi related
PHP Version: 7.0.0RC7 OS: Windows Server 2012
Private report: No CVE-ID: None
 [2015-11-20 11:10 UTC] s7g2vp2 at yahoo dot co dot uk
Description:
------------
While testing our app using PHP7RC7 we noticed some of the SQL statements failed to return a result set.

After some investigation it appears that the syntax LIMIT x OFFSET y causes a problem. The alternative syntax LIMIT y,x does appear to work.

We are using MySQLi driver and prepared statements. The LIMIT & OFFSET values are part of the statement and not bound.

All statements work in previous versions of PHP and also work in MySQL Workbench.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-11-20 14:39 UTC] laruence@php.net
-Status: Open +Status: Feedback
 [2015-11-20 14:39 UTC] laruence@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.


 [2015-11-21 00:24 UTC] rasmus@php.net
Are you getting a MySQL error? A common cause of this is that you are sending something than a positive integer as the offset. For example, if you are doing something like:

    $offset = ceil($page * 10);

$offset is now a float because ceil() always returns a float and the driver will pass that as something like "10.0" which will make offset fail. Make sure you cast any offsets you substitute into your query to an int first. eg.

    $offset = (int)ceil($page * 10);
 [2015-11-22 17:29 UTC] s7g2vp2 at yahoo dot co dot uk
-Summary: SQL Statements With Limit And Offset Fail +Summary: SQL Result Sets With NULL Can Cause Fatal Memory Errors -Status: Feedback +Status: Open
 [2015-11-22 17:29 UTC] s7g2vp2 at yahoo dot co dot uk
I have spent some more time investigating this and my original diagnoses is wrong as now, I cannot fix the problem using the method I originally stated.


If I check the Apache error log I see that PHP has crashed with a fatal memory error:
PHP Fatal error:  Allowed memory size of 805306368 bytes exhausted (tried to allocate 1028538193632 bytes)


The crash occurs in a loop that concatenates a php variable using data fetched from the result set.


The SQL Statement is:
SELECT `sr`.`id`, `sr`.`groupcode`, 
(SELECT group_concat(`sr2`.`groupcode`) FROM SHIPPINGRATES sr2 WHERE `sr2`.`parentid` = `sr`.`id`),
`sr`.`companycode`, `sr`.`code`, `sr`.`shippingmethodcode`, `sr`.`shippingzonecode`, `sm`.`name`, `sz`.`name`, `sr`.`productcode`, `pr`.`name`, `sr`.`info`, `sr`.`rate`, `sr`.`active`
FROM SHIPPINGRATES sr 
LEFT JOIN `SHIPPINGMETHODS` sm ON `sm`.`code` = `sr`.`shippingmethodcode`
LEFT JOIN `SHIPPINGZONES` sz ON `sz`.`code` = `sr`.`shippingzonecode`
LEFT JOIN `PRODUCTS` pr ON `pr`.`code` = `sr`.`productcode`
WHERE `sr`.`parentid` = 0 ORDER BY `sr`.`companycode`, `sr`.`code` LIMIT 100 OFFSET 0


The 3rd column of the result set (group_concat) returns NULL for rows 1, 2, 3 and 7. It is during the processing of row 7 in the loop that causes the crash.


The function includes a line that sets the cursor to read-only:
$stmt->attr_set(MYSQLI_STMT_ATTR_CURSOR_TYPE, MYSQLI_CURSOR_TYPE_READ_ONLY);

If I remove that line the crash does not occur.


If I use IFNULL in the SQL statement to return an empty value instead of a NULL, the crash does not occur.


If I just return NULL for the 3rd column the crash does not occur.


so... The problem appears to be related to NULL, group_concat and read-only cursors. My guess is this is some type of memory corruption.


I have tried disabling opcache but that has not made any difference.



I can re-create this in a standalone script but it also requires a database. I can provide a script and MySQL dump but I don't know how I should attach these.


Regards,
Kev.


ps. I am running 64 bit Apache 2.4.16 from Apache Lounge and the 64 bit version of PHP7 RC7 via the Apache module.
 [2015-11-24 05:58 UTC] laruence@php.net
-Assigned To: +Assigned To: laruence
 [2015-11-24 05:58 UTC] laruence@php.net
can you provide a test script and a sh to fill database ?
 [2015-11-24 05:59 UTC] laruence@php.net
-Status: Assigned +Status: Feedback
 [2015-11-24 07:06 UTC] s7g2vp2 at yahoo dot co dot uk
-Status: Feedback +Status: Assigned
 [2015-11-24 07:06 UTC] s7g2vp2 at yahoo dot co dot uk
yes. how do I upload it? I only see an option to attach a patch.
 [2015-11-24 09:04 UTC] laruence@php.net
it accept txt files, or if you prefer mail, you can mail to me. thanks
 [2015-11-27 07:53 UTC] laruence@php.net
Automatic comment on behalf of laruence@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=a347b0be48d892c105198b23868f37a0d4f92dee
Log: Fixed bug #70949 (SQL Result Sets With NULL Can Cause Fatal Memory Errors)
 [2015-11-27 07:53 UTC] laruence@php.net
-Status: Assigned +Status: Closed
 [2015-11-30 15:53 UTC] ab@php.net
Automatic comment on behalf of laruence@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=1561fbad67d02b4bd5148e231245bea4c3fb4031
Log: Fixed bug #70949 (SQL Result Sets With NULL Can Cause Fatal Memory Errors)
 [2016-07-20 11:35 UTC] davey@php.net
Automatic comment on behalf of laruence@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=a347b0be48d892c105198b23868f37a0d4f92dee
Log: Fixed bug #70949 (SQL Result Sets With NULL Can Cause Fatal Memory Errors)
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Dec 11 06:01:28 2024 UTC