[buddypress-trac] [BuddyPress] #4933: Members Directory Search doesn't find O'Connor

[buddypress-trac]

#4933: Members Directory Search doesn't find O'Connor
-------------------------------------+------------------------------
 Reporter:  dontdream                |       Owner:
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Core                     |     Version:  1.7
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+------------------------------
Changes (by boonebgorges):

 * keywords:  has-patch => has-patch needs-testing


Comment:

 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.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4933#comment:2>
BuddyPress <http://buddypress.org/>
BuddyPress