php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #48857 fetch mashes data when Stored Procedure uses prepared statements
Submitted: 2009-07-08 21:54 UTC Modified: 2009-09-03 01:00 UTC
From: web at sellingpower dot com Assigned: mysql (profile)
Status: No Feedback Package: MySQLi related
PHP Version: 5.2.10 OS: ubuntu5.6
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-07-08 21:54 UTC] web at sellingpower dot com
Description:
------------
We are using the mysqli extension to interface with MySQL 	5.0.51a.  We are using the prepare/bind_param/execute/bind_result/fetch process against stored procedures.  When the stored procedure pulls the data via a prepared statment, fetch mangles the data when putting it into the bound result variables.

PROCEDURE `p_TestNonPreparedStatement`()

and 

PROCEDURE `p_TestPreparedStatement`()

produce identical results in the query browser but very different results when run through getresult($stmt,$fields)

Unfortunately, this server is behind a firewall and not accessible to the public.

All previous bug reports similar to this have identified this behavior as not a bug without identifying how to get around the problem.

Reproduce code:
---------------
CREATE DEFINER=`robertp`@`` PROCEDURE `p_TestNonPreparedStatement`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
	SELECT * FROM `eBlast`.`veMailList`;

END
--------------------------------------------------------------------------
CREATE DEFINER=`robertp`@`` PROCEDURE `p_TestPreparedStatement`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE `new_query`	VARCHAR(4096)	DEFAULT '';
	
	SET `new_query` = 'SELECT * FROM `eBlast`.`veMailList`';
	
	SET @new_query = `new_query`;
	PREPARE `filtersetlist_query` FROM @new_query;
	EXECUTE `filtersetlist_query`;
	DEALLOCATE PREPARE `filtersetlist_query`;
	

END
--------------------------------------------------------------------------------------
	public function pullSendees($status_id,&$fields)
	{
		$sendees = array();
		$DB = new mysqli(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE_EBLAST);
		if (mysqli_connect_errno()) {
			throw new Exception('Connection to database failed');
		}
		$stmt = $DB->stmt_init();

		if ($stmt->prepare('CALL `eBlast`.`p_TestNonPreparedStatement` (?,?)'))
		{
			$stmt->bind_param('is',$this->id,$status_id);
			$stmt->execute();
			$sendees = getresult($stmt,$fields);
			$stmt->close();
		}
		$DB->close();
		return $sendees;
	}

function getresult($stmt,&$fields)
{
	$result = array();
	$metadata = $stmt->result_metadata();
	$fields = $metadata->fetch_fields();
	for (;;)
	{
		$pointers = array();
		$row = new stdClass();
		$pointers[0] = $stmt;
		$count = 1;
		foreach ($fields as $field)
		{
			$fieldname = $field->name;
			$pointers[$count] = &$row->$fieldname;
			$count++;
		}
		call_user_func_array('mysqli_stmt_bind_result', $pointers);
		if (!mysqli_stmt_fetch($stmt))
			break;
		foreach ($fields as $field)
		{
			$fieldname = $field->name;
			trigger_error('$' . 'row->' . $fieldname . ' = ' . $row->$fieldname);
		}
		$result[] = $row;
	}
	$metadata->free();
	return $result;
}


Expected result:
----------------
Prepared Statment Results:

1  	1  	Robert  	Polickoski  	robertpolickoski@sellingpower.com  	Fredericksburg  	VA  	223  	22406-1126  	1  	1  	6  	7  	2  	1  	1  	1  	1  	1  	0  	0  	1  	1  	0  	0  	1  	1  	1  	1  	 	 	 	 	 	 	


Actual result:
--------------
NonPrepared Statment Results:

1919246959  	1867516532  	 	 	ickoski!robertpolickoski@sellingpower.comFredericksburgVA223 22406-112611672111110  	1  	 	822161665  	�������elli  	-5  	-5  	251  	251  	251  	 	-5  	-5  	0  	 	 	101  	108  	108  	 	 	105  	110  	103  	32  	30575-101-114 32:72:  	2008-10-07 00:00:00  	 	2009-02-09 16:54:30  	4294966272-105-01 00  	 	27694-101-102 116:95



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-07-08 21:58 UTC] web at sellingpower dot com
I misspelled statement in the summary and wanted to correct it for search purposes.
 [2009-08-26 08:39 UTC] uw@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.


 [2009-09-03 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Sat Nov 26 23:05:53 2022 UTC