10

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: enter image description here

Empty, though it shouldn't be: enter image description here

Returns all, including the record that should have been there with the other 2: enter image description here

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.

Community
  • 1
  • 1
durbnpoisn
  • 4,666
  • 2
  • 16
  • 30
  • 1
    This is a problem with your data. Prepared statements has nothing to do here. Try a regular query with same data. – Your Common Sense May 09 '16 at 13:43
  • Not that I'm not willing to entertain that idea. But I am searching VARCHAR fields. And nothing in the data in those fields in unusual. Could you give me an example of how my data could be messed up? – durbnpoisn May 09 '16 at 13:47
  • I have tried a regular query. I have it open in the background in PHPMyAdmin right now. I still cannot see how there is any difference. – durbnpoisn May 09 '16 at 13:50
  • 1
    So running `SELECT * FROM tablename WHERE \`Name\` LIKE '%Lawton%' order by \`Name\`` in PHPMyAdmin works but your prepared statement does not? – MonkeyZeus May 09 '16 at 13:51
  • Are you positive that `$whatName` equals precisely what you think it does? – MonkeyZeus May 09 '16 at 13:53
  • To both questions. Yes. $whatName is passed in via POST and cleaned with "mysqli_real_escape_string". If I echo out what it resolves to, it will show %Ken L%, or %Lawton% exactly as it should. – durbnpoisn May 09 '16 at 14:02
  • And what result you obtain through query that in phpmyadmin? – Marcos Pérez Gude May 09 '16 at 14:06
  • Through PHPMyAdmin, I get back the correct records. What's strange is that the statement seems to work just fine for some things, as I mentioned. But ignores records that it shouldn't, other times. – durbnpoisn May 09 '16 at 14:08
  • 2
    Don't use mysqli_real_escape_string on values that you're going to bind; binding does that for you, so your'e potentially double-escaping – Mark Baker May 09 '16 at 14:16
  • I've tried it with the escaping turned off, and got the same result. But I can try that again. – durbnpoisn May 09 '16 at 14:17
  • Have you tried logging on the server too see exactly what the server sees? – Ignacio Vazquez-Abrams May 12 '16 at 11:16
  • I'm not sure what you mean. phpMyAdmin is the only resource I have. And, as mentioned above, the querries return correct results there. – durbnpoisn May 12 '16 at 11:27
  • It sounds like you need to look at the generated query. Ideally this would be by using a mysql log file, but it sounds like you are using a web host where you can't turn that sort of thing on, so you could try writing your own wrap function per [this question](http://stackoverflow.com/questions/962986/how-to-echo-a-mysqli-prepared-statement?lq=1) and check the returned statement that way. Then test it in PHPMyAdmin. Testing what you *think* the prepared statement is returning is not a fair test. – toomanyredirects May 12 '16 at 12:36
  • Yes, I do believe that is what I need to do. And, you'd be right in assuming I don't know how to that. I've been echoing the parts I can echo. But it still doesn't tell me what is ultimately executed. I will follow up on that link you provided. Stay tuned. – durbnpoisn May 12 '16 at 13:36
  • Ok - I've gone through that thread and used two of their suggestions as to check out the full prepared statement. I tried it like 6 different ways. All I could do was either break the program, or get the exact same results. So, now I am equally stumped. – durbnpoisn May 12 '16 at 16:04
  • You should post your code that builds the query. You should also NEVER post people's personal information online without their consent. Get some dummy data in there and update your question. – miken32 May 17 '16 at 21:43
  • There is nothing special to show about how the string is generated. In fact, the relevant part is there. I do, btw, agree that the images may have had too much information. I blocked most of it out. But maybe not enough. So, I'll edit those images and put them up with blocked out data. – durbnpoisn May 17 '16 at 21:59
  • @durbnpoisn Did you checked that you have no invisible chars into your chains ? I already had things like \n & \r and it was mad ! you can easily check it by doing simple check like : `md5($db_val)` & `md5($test_val)` or simply using `count()` on each ;) – Bobot May 18 '16 at 15:13
  • thanks for the update @durbnpoisn, does GoDaddy not offer `utf8mb4_` ? Another question from me I'm afraid - how was the data inserted into the database, as in, if the data was inserted with another (very similar) character set this will cause the issues that you find as it *looks like* a space but is infact another character, as sort of touched on by Bob0t's comment above me. – Martin May 18 '16 at 15:29
  • is it intentional that your Like statement is not in quotes ? (as in should be `LIKE '%Ken L%'` in your example queries) – Martin May 18 '16 at 15:30
  • Another question, are you able to tell us what type of column the `Name` column is, as in VARCHAR and its *exact* collation and details? Cheers – Martin May 18 '16 at 15:35
  • @BobOt: The data was imported from a CSV file. Plain ASCII text. So nothing funny there. But I also have manually updated the data since that point, in order to strip out any strange things like escaped characters. – durbnpoisn May 18 '16 at 15:59
  • @Martin: I do not know, actually. I created the table with the default settings. And yes, the string is made without the surrounding quotes because that's the way prepared statements work. So it's actually correct. – durbnpoisn May 18 '16 at 16:00
  • The column is a VARCHAH(50). Which is about as typical as it gets. It should be noted that though we are discussing the "name" column, this same problem occurs in any column I try to search. The whole issue is that my prepared statements work great and have no errors - except when I use LIKE - then I get incomplete results. – durbnpoisn May 18 '16 at 16:03
  • Ok, personally I'm running out of ideas as to a distinct cause, but I think it's essentially a character encoding issue. As mentioned in my anwser I've memories of having a vaguely similar issue and finding that CONCAT in the MySQL solved it for me, so that's what I do now. Although (At least for me) wildcards do still work in bound parameters. – Martin May 18 '16 at 16:10
  • did you say that the issue still exists when using the MySQL `CONCAT` method? – Martin May 18 '16 at 16:12
  • I couldn't get CONCAT to work. It either generated a statement with bad syntax. Like, it would still say "LIKE CONCAT('%',?,'%') " in the query, in which case it would get dropped. Either that or just break the code completely. – durbnpoisn May 18 '16 at 16:33
  • @durbnpoisn that's wrong, CONCAT wouldn't break the code. Sounds like a symptom of something else at play here. Sounds more like a structural isse with the settings for the DB ... :-/ – Martin May 18 '16 at 17:09
  • Also I can get a notification of your comments if you do the @durbnpoisn in comments too :-D – Martin May 18 '16 at 17:10
  • 1
    I would suspect your 'L' is just not an 'L' (just something that looks and sorts like an 'L') or that you have invisible characters in your string. Can you add the hex-representation of your name to your query and post the results? `select ..., Name, HEX(cognome) as hex, length(Name) as len, ...` – Solarflare May 18 '16 at 17:16
  • Haha... It's an "L". Trust me. Besides... Like I pointed out, this is just one example. It's not just this one field or this one string. – durbnpoisn May 18 '16 at 19:18
  • I am officially awarding @Martin the bounty on this one. Because it was the answer that generated the most discussion. I may not have found the exact solution yet. But there is enough here to research to feel that I WILL find it. Peter Darmis - you too were a big help. But I can only award the bounty once. – durbnpoisn May 18 '16 at 19:20
  • Thanks @durbnpoisn sorry it wasn't a perfect solution. When you do find out the cause please write a comment on this thread and update me, I'd be curious what the solution turns out to be :-) – Martin May 18 '16 at 20:39
  • Did you try replacing your [space] characters in the query with % signs? str_replace(' ', '%', $whatName) Sometimes when I've imported CSVs the spaces in the data aren't true spaces ... but something else. – danielml01 May 18 '16 at 20:47
  • Yes. I did try that. To no avail. @Martin... I will indeed update this thread once I figure this out. Unless I never do... haha... :) – durbnpoisn May 18 '16 at 20:53
  • @durbnpoisn I have found something that *might* help you, try running a few of these concepts, from [this question](http://stackoverflow.com/questions/17049903/converting-mysql-table-with-incorrectly-encoded-data-to-utf-8) annd [this question](http://stackoverflow.com/questions/9407834/mysql-convert-latin1-characters-on-a-utf8-table-into-utf8) you may have to have a good fiddle with the data so backup the table but if the CSV was not the correct character set then these questions will help you get solid standard base level to work from :-) – Martin May 18 '16 at 21:05

2 Answers2

5

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

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
  • There's nothing wrong with passing the percents with the parameter. There's nothing wrong with using `CONCAT` either, but it's certainly not going to fix the problem. – miken32 May 17 '16 at 23:22
  • I appreciate this answer, and the explanation behind it. But here's the thing. I know how to do this by just generating a string. And yes, that does work just fine (I've tested it). But I'm trying to avoid doing that because of SQL injection. This is why I'm doing it with the parameter. In my view, I'd rather have a slightly less reliable search if it means the program is more secure. Just the same - I'd like to get as close to 100% reliable as I can. – durbnpoisn May 18 '16 at 01:07
  • Actually - reading your answer again - I did NOT try it that way. That's actually a good idea. I will try that. – durbnpoisn May 18 '16 at 01:08
  • @durbnpoisn using prepared statements you don't need to worry about SQL injection. See how this method works for you. Also if you can tell us the `Collation` of your `Names` column that would be helpful too. – Martin May 18 '16 at 07:13
  • @miken32 I've done some tests, you are correct. I'm in a habit of using `CONCAT` although I don't remember the specifics of why when doing LIKE searches. – Martin May 18 '16 at 07:44
  • Between this answer and the one provided by @Peter Darmis, I have some new ideas. But it doesn't appear that either one solves the problem completely. Is there a way for me to award 25 bounty to each? Because it doesn't feel right to award the entire 50 to just one. – durbnpoisn May 18 '16 at 13:00
  • @durbnpoisn no. But what's the current status of your issue? If it's not entirely solved can you update your question (edit) with the answers to my questions at the bottom of my answer. Cheers. – Martin May 18 '16 at 13:16
  • I'll do that. I want to put the screen grabs back in too. Stay tuned. – durbnpoisn May 18 '16 at 13:26
4

It probably is some sort of mixed up encoding issue in $whatName.

Check if your $whatName variable encoding, is UTF8. 

mb_detect_encoding($whatName, 'UTF-8', true) // should return true

if not then you will have to use mb_detect_encoding and mb_convert_encoding on $whatName to convert it to utf8.

If you have not done this already

Set proper charset

$mysqli->set_charset('utf8mb4'); 
// if your MySQL version is lower than 5.5.3 then
// use $mysqli->set_charset('utf8');

before your prepared statement

$stmt = $mysqli->prepare($selectStr);
$stmt->bind_param('s', $whatToBind);
$stmt->execute();
  • Am am looking into this now. It appears that the character set used is ASCII. That could be causing the trouble. If I can get the convert_encoding to work and that does the trick, then you win. Stay tuned. – durbnpoisn May 13 '16 at 12:47
  • I went through this as well as I could. I successfully got the encoding to UTF-8. It made exactly zero difference. This was a great idea, though. – durbnpoisn May 17 '16 at 19:37
  • one last proposal, i have not tested this but i think i might be of use. https://github.com/neitanod/forceutf8 –  May 17 '16 at 19:55
  • I will look into this. From what I gather, the m_encoding functions may or may not be working on my server anyway. I'm not getting any errors. But they don't appear to do anything either. So, perhaps some other method would work. This is REALLY strange. – durbnpoisn May 17 '16 at 19:57
  • On the other hand... I really have no idea how to implement that thing in the context of my project. haha... – durbnpoisn May 17 '16 at 19:59
  • after including the class to your script `$utf8_whatName = Encoding::toUTF8($whatName);` your new `$whatToBind` would be then `'%'. $utf8_whatName .'%';` –  May 17 '16 at 20:05
  • Not to sound totally foolish, but I couldn't find how to download the function. I've yet to get up to speed with Github. I'm that guy. – durbnpoisn May 17 '16 at 20:16
  • 1
    @durbnpoisn i understand the fact that you did not see the `download zip` button on the right :) if you are stack with a problem sometimes and spend a lot of hours in front of your PC or MAC eyes may start failing. So basically it does not sound foolish. –  May 17 '16 at 20:37
  • Ha... You're right. There it is. Okay... Current status: I've got the class. I've got it up on my server and ready to include. But I'm out of time to implement it for now. I'll be back later. So check back later today or tomorrow. Thanks! – durbnpoisn May 17 '16 at 20:43
  • @PeterDarmis please note that MySQL UTF-8 is unsuitable and will cause as many problems as it solves. Please update your answer to use `UTF8mb4` for the MySQL character set/collations. – Martin May 18 '16 at 13:18
  • @Martinyes you are right and i am going to edit my answer accordingly, utf8mb4 is a superset of utf8, but i seriously doubt if this is the problem in this question. But as written in thew beginning you are right on the charset. –  May 18 '16 at 16:05