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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: web at sellingpower dot com
New email:
PHP Version: OS:

 

 [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

Pull Requests

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-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 14:01:32 2024 UTC