Members Directory Search doesn't find O'Connor

[dontdream]

Enter a text containing quotes, e.g. O'Connor, in the Members Directory search box. The answer is always "Sorry, no members were found." even if many members are named O'Connor.

The bug is fixed replacing the WHERE clause in bp-core-classes.php, line 321.
Replace:

WHERE value LIKE %s", '%%' . like_escape( $search_terms ) . '%%'

with:

WHERE value LIKE %s", '%' . mysql_real_escape_string( $search_terms ) . '%'

The double % characters are replaced by single % characters, but that's optional, while the different escape function actually removes the bug.

[dontdream]

edited copy of bp-core-classes.php

[boonebgorges]

Confirmed. Thanks for the report, dontdream.

I found that simply swapping out the like_escape() wasn't enough to make the query succeed. In order to get things to work, I had to *double* escape the LIKE clause going in. See 4933.patch.

The first escape makes sense, since the apostrophe is escaped in the database. In other words:

SELECT * FROM wp_bp_xprofile_data;

|  5 |        1 |       5 | Foo\'Bar \"The Bomb\" | 2013-04-15 09:00:14 |

Based on this, you'd think that the following query would work:

SELECT user_id FROM wp_bp_xprofile_data WHERE value LIKE 'oo\\\'Bar';

But it doesn't - you need one more layer of escaping. I'm not 100% sure why this is the case. I think it has something to do with the way that MySQL interprets LIKE clauses - it could be that the escape character has an additional role in LIKE clauses (though I can't find any documentation that backs this up). So we end up needing a query that looks like this:

SELECT user_id FROM wp_bp_xprofile_data WHERE value LIKE 'oo\\\\\\\'Bar';

Also, we can't just remove call to like_escape(), because that'll break searches with percent-signs or underscores.

4933.patch contains my reworked logic for the search_terms query, as well as unit tests for the cases of apostrophe, underscore, and percent-sign. They're all passing for me (the automated tests as well as when I test manually in the browser). But because of the double-encoding issue with the apostrophe, and because dontdream did *not* report that double-encoding was necessary, I would like to have a sanity check from another core dev before committing.

[dontdream]

I realized that this issue has been addressed before, see #2776, and has to do with input strings not being "stripslashed" before being stored in the database.

I agree that the issue should be addressed when data is put into the database, rather than on output. Anyway I found that bp-core-classes.php always uses like_escape() together with $wpdb->escape(), and extending this technique to our case solves this ticket. Please see the attached patch.

[boonebgorges]

Thanks, dontdream. Your patch does pretty much exactly what my 4933.patch does.

You mention fixing how BP stores content "on the way in". Neither of our patches does this. Fixing the way that BP escapes stored data is hard because we will have to migrate all existing (incorrectly stored) content. The patch on #2776 is a good start for that.

[boonebgorges]

I've had another look and I'm going to go ahead with my patch. Thanks for the help, dontdream.

[boonebgorges]

In 7008:

Improve search_terms SQL clause in BP_User_Query

Standardizing the way that apostrophes and other special characters are
escaped in the LIKE claues means that we won't miss items "O'Conner" in member
search.

Fixes #4933

Props dontdream