php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #37937 bound params inside of a CONCAT do not work
Submitted: 2006-06-27 20:27 UTC Modified: 2006-07-30 19:24 UTC
Votes:3
Avg. Score:5.0 ± 0.0
Reproduced:3 of 3 (100.0%)
Same Version:3 (100.0%)
Same OS:3 (100.0%)
From: ibexris at gmail dot com Assigned: georg (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5.1.4 OS: linux, fedora core 5
Private report: No CVE-ID: None
 [2006-06-27 20:27 UTC] ibexris at gmail dot com
Description:
------------
Like the summary says...  using a bound parameter inside of a CONCAT() produces strange results.

Reproduce code:
---------------
<?php

$dbh = mysqli_init();
$dbh->real_connect('localhost', 'dbuser', '', 'dbname');

$sh = $dbh->prepare('SELECT CONCAT("xxx", ?, "xxx"), CONCAT("xxx", "abc", "xxx")');

$sh->bind_param('s', $x);
$sh->bind_result($r, $r2);

$x = 'abc';

$sh->execute();
$sh->fetch();

echo "\n$r\n$r2\n\n";

$dbh->close();

Expected result:
----------------
xxxabcxxx
xxxabcxxx

Actual result:
--------------
On my system, this prints out:

xxxabcxA
xxxabcxxx

The "A" changes on other systems (or goes away).  However, both lines *should* be the same. Change the x's around in the first field and watch the results get even stranger, or remove them completely to have it work as expected (except that I need the CONCAT for my query).


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-06-27 20:30 UTC] ibexris at gmail dot com
Further digging shows that there are also hidden characters returned from the query, and that they often change between executions of the code.
 [2006-06-27 20:54 UTC] ibexris at gmail dot com
Still more info..  added a web page to test it (sorry, internal network only) and added a bunch more x's to the concat stuff..  Getting all kinds of interesting stuff that seems to indicate a corrupt memory fragment.  The key being that one time I got "d.8.0.4) Gecko/20060609 Firefox/1.5.0." stuffed away in the response string.
 [2006-06-27 20:56 UTC] tony2001@php.net
Please also tell the version of MySQL you're using.
 [2006-06-27 20:58 UTC] ibexris at gmail dot com
5.0.22.  Also, my main machine is running php 5.0.4, but I replicated it on another machine with 5.1.4 before submitting the bug (same version of mysql).
 [2006-06-27 21:03 UTC] tony2001@php.net
Doesn't look like PHP problem to me, but I'm assigning it to the maintainer.
 [2006-06-27 21:08 UTC] ibexris at gmail dot com
Not sure what else it could be.  The mysql logs clearly show the correct queries going in, and when you run them in the client, they produce correct results.  It *only* happens with bound parameters, crosses several versions of php and mysql (forgot to mention that I started with 5.0.14), and is present on at least two different systems (fc4 + official mysql packages, and fc5 with distro-supplied packages).  The fact that part of my browser useragent came back in one of the results makes it pretty clear that php is at least somehow involved.
 [2006-07-30 11:26 UTC] georg@php.net
mysqli_bind_result has to be called after mysqli_stmt_execute, not before.

	$stmt = $mysql->prepare("SELECT CONCAT('xxx', ?, 'xxx') FROM DUAL");
	$stmt->bind_param('s', $a);
	$a = "abc";

	$stmt->execute();
	$stmt->bind_result($concat);

	$stmt->fetch();
	var_dump($concat);

Result:
string(9) "xxxabcxxx"

 [2006-07-30 19:24 UTC] ibexris at gmail dot com
Shouldn't php then throw an error at least a warning?  The whole point of a function like this is to avoid extraneous data assignments.  My initial desire to use it was like:

prepare
bind
loop
  execute
  loop
    fetch
    [code]
  endloop
endloop
finish

It's trivial to move the bind inside the first loop, but seems redundant.  Nowhere in the documentation does it say that bind must be called after execute, and as you see, it *does* work, just doesn't work correctly.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Dec 08 03:01:28 2024 UTC