MySQLi won't return certain strings from DB?

[ultddave]

Hey everybody.

I'm using this code to select all 3 columns from my database table.


$stmt = $mysqli->prepare("SELECT * FROM entries")) {

/* Query uitvoeren */
$stmt->execute();

/* bind result variables */
$stmt->bind_result($id, $title, $body);

/* fetch value */
$stmt->fetch();

}
echo $id;
echo $title;
echo $body;


($mysqli contains a working connection.)

Now. The echo statements at the bottom of the code only produce output for $id and $title. $body is an empty string.

After doing some research, I noticed that mysqli don't bind results to a variable if the result in the database contains these characters: < or >.
So if i would change my title from "This is a title" to "<This is a title". The $title variable will be empty after the bind_result call.

PS: My body always contains HTML code. Like:
<h3>Subtitle</h3>
<p> text </p>

Very simple HTML code. But it seems that mysqli won't return values with a greater than or lesser than sign.. :(

PS: I use a MySQL database.

Does anyone know how I could 'fix' this. Or knows a workaround.

Thanks in advance.

Greetings,
Dave

[ultddave]

Also strings with quotes are not returned.

I tried replacing < and > with entities like &lt; and &gt; etc. But strings containing those are not returned either. I seriously don't see what's the problem.

Database uses: MySQL - 5.0.82sp1

When I use normal mysql statements instead of mysqli, it does work.

$result = mysql_query("SELECT * FROM `entries`");
while($row = mysql_fetch_array($result)){
extract($row);
echo $body;
}


Greetz,
Dave

[PeejAvery]

You have invalid bracketing in your MySQLi example. It should look like the following...

$stmt = $mysqli->prepare("SELECT * FROM entries");

/* Query uitvoeren */
$stmt->execute();

/* bind result variables */
$stmt->bind_result($id, $title, $body);

/* fetch value */
$stmt->fetch();

echo $id;
echo $title;
echo $body;

[ultddave]

Thanks for your reply. :D

My appologies, that was my mistake. In fact the code looked like this.

if($stmt = $mysqli->prepare("SELECT * FROM entries")) {

/* Query uitvoeren */
$stmt->execute();

/* bind result variables */
$stmt->bind_result($id, $title, $body);

/* fetch value */
$stmt->fetch();

}
else
echo "Query voorbereiding is mislukt"; // Dutch error message

echo $id;
echo $title;
echo $body;


But the "if - else" test doesn't really help to solve my problem, so I thought i'd remove it in my forum post. But it seems I forgot to remove the ")" and " { } " brackets. Sorry for that.

By the way, I think your code also has a ")" too much in this line;

$mysqli->prepare("SELECT * FROM entries"));


But I understand what you mean. But that's not the problem unfortanely :(. I also noticed that strings containing characters like quotes, and "&" signs aren't returned either.

(I've tried escaping quotes etc. But that doesn't work.)

It's very weird. Could it be some kind of "injection" protection? Or something? ;)

I've looked on the internet but I didn't find any information about this. But at the moment I'm using normal mysql (which works just fine). So if anybody might have some information regarding this issue, please post it here. That would be very nice. If not -> I'll just use normal mysql as 'workaround'. ;)

PS: It's not an urgent matter.

PS: You're doing an awesome job here mate. Last time I visited the forum was almost a year ago. And you're still helping everybody. Respect. Keep up the good work. ;)

PS: Sorry for my English. :D

Greetz,
Dave

[PeejAvery]

Yeah...I removed my extra parenthesis.

I think we overlooked something bigger though...You're using mysqli_prepare() without any variables to be prepared. Since there is no where statement in your select query, just use mysqli_query().

[ultddave]

Thanks for the reply :D.

I've tried it like this:

if($stmt = $mysqli->query("SELECT * FROM entries")) {
if($stmt->num_rows > 0)
{
$obj = $stmt->fetch_object();
echo $obj->body;
}
else
echo "Empty resultset";

}
else
echo "Query failed.";

$stmt->close();
$mysqli->close();


And it works! Now it does fetch my string with HTML code. I think the 'prepare' function of mysqli has a build-in "injection protection". So any data that's received/sended from/to the database is checked for possible injections. Atleast that's what I think. ;)

Atleast it works perfectly now. Thanks again!!! :D

Greetz,
Dave

[PeejAvery]

You don't have to think that it does...That's exactly what the prepare statement does.

And, you're welcome. :wave: