Wildcard in prepared MySQLi returning bad values

Question

Please see the bottom of this post for newest information and current status

Following advise from posts like this one: Using wildcards in prepared statement - MySQLi

I have my statement set up and it works with no errors. But it does not return the correct data.

My select statement has this for the WHERE:

 WHERE `Name` LIKE ?  order by `Name`

My string to set up the binding, and then the actual binding.

$whatToBind = '%'. $whatName .'%';

$stmt = $mysqli->prepare($selectStr);
$stmt->bind_param('s', $whatToBind);
$stmt->execute();

When I get my return, it will completely miss records that it should match. Like, if I send in "Ken L", I get back records for "Ken Linton" but not "Ken Lawton". If I put in "Lawton", I get no return at all.

This is typical behavior across the board. If I search a phone number field, I get returns on "658", but no returns on "609-658".

If anyone can clue me in on what I'm missing, that would be great.

Example returns that show the exact examples I'm referring to:

Incomplete:

Empty, though it shouldn't be:

Returns all, including the record that should have been there with the other 2:

Questions to answer: Some further things to check:

Check the MySQL / PHP interaction character set is set correctly, typically with: $mysqli->set_charset("utf8mb4"); right after database connection is established.

It is set to utf8. Although it behaved the same before this was set.

Can you show any output from $mysqli->error ?

There are no errors. Just incomplete returns

Can you show us your whole SQL query?

It's included in the screen grabs. Although, that's just a plain string. And it doesn't account for what the prepared statement looks like.

Can you show the Collation / MySQL structure of the Names column?

It is all utf8 as per GoDaddy's phpMyAdmin

Can you show what the value of $whatName is right before binding?

It's at the top of the screen grab. It's echoed back to show it before anything else happens.

At this point I am thinking that the issue lies in what happens when the field I'm searching has a space or other character that is not a letter. Not what I'm passing in exactly. But more like, once the statement is prepared what is prepared is not matching what is in the field it is searching. This doesn't happen when you search the field prior to where the space exists. This is why "Ken" works 100% of the time, but "Lawton" fails completely. It's after the space.

I have tried all manner of converting the encoding type. And I have tried the various methods of concatenating the string. The results I'm getting are either no better, of completely breaking it.

Still 21 hours left on this bounty, if anyone has any more ideas. At this point, I'd be more happy to award 25 each to the two dudes that provided the best information. Seems unfair to reward one and not the other.

Answer 1

Please note that the details on this answer are unlikely to resolve the question on their own. With my own further testing I established that appending % or _ wildcards to strings before they are bound does not effect the way they are bound to the query.

What you are currently trying to do is Concatenate the data ($whatName) with an SQL instruction % each side of it, and the Prepared Statement parser is simply not having this, as it defeats the security purposes of prepard statements.

So, the solution is that you need to manually concatenate the variable into the LIKE statement only at the point of insertion, and not before, as you are doing at the moment.

The example below will do as you intend:

$selectStr = WHERE `Name` LIKE CONCAT('%',?,'%') ORDER BY `Name`
$whatToBind = $whatName;
$stmt = $mysqli->prepare($selectStr);
$stmt->bind_param('s', $whatToBind);
$stmt->execute();

Note that the data and the query never mix, until the prepared statement is executed.

---

A note on UTF-8 and character sets on MySQL:

Do not use the utf8_ MySQL character sets as they are an incomplete subset of true UTF-8 and so can still raise serious issues with character recognition. Instead you want to be using utf8mb4_ character sets/collations/etc.

Character encoding may be a related issue to your problem, and I highly recommend reading up on the excellent answers given on this Stack Overflow Question as well as using the PHP mb_ multibyte string functions.

---

Some further things to check:

• Check the MySQL / PHP interaction character set is set correctly, typically with: $mysqli->set_charset("utf8mb4"); right after database connection is established.

• Can you show any output from $mysqli->error ?

• Can you show us your whole SQL query?

• Can you show the Collation / MySQL structure of the Names column?

• Can you show what the value of $whatName is right before binding? (while your question makes sense, having a specific example of a specific situation and a specific result as well as an intended result is very useful for us to debug.

• Silly thing but ensure you don't have a LIMIT on your results! :-D