Make WordPress Core

Opened 2 years ago

Closed 8 months ago

Last modified 7 months ago

#19738 closed enhancement (wontfix)

meta_query should check for wildcards when using LIKE operator

Reported by: ejdanderson Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.2
Component: Query Keywords:
Focuses: Cc:


This seems intentional, but when using the LIKE operator in a meta query, the % wildcards are automatically added to the start and end of the value when generating the SQL.

I think a more robust way of doing this would be to only add said wildcards if one cannot be found within the value itself.

Change History (6)

comment:1 follow-up: scribu2 years ago

  • Keywords 2nd-opinion added; needs-patch removed

I'm on the fence on this one. I don't think we expose MySQL's '%' wildcards anywhere ATM.

comment:2 in reply to: ↑ 1 ejdanderson2 years ago

Replying to scribu:

I'm on the fence on this one. I don't think we expose MySQL's '%' wildcards anywhere ATM.

I dont think so either, there are a few places in the code that act in a similar way get_terms() for example, although in that case its a search parameter and seems appropriate.

I can see why exposing this is a bad idea, we don't want user input to be able to set SQL wildcards.

There have been numerous times though when I need to filter in custom SQL for a LIKE operator. A large majority of these have been to remove the leading '%'. Maybe a better solution is a flag to describe where '%' should be set - preceding, trailing, or both.

comment:3 SpaceMacGyver23 months ago

  • Cc SpaceMacGyver added

While I appreciate the need to abstract the SQL away from the query, as ejdanderson suggests a flag to control this would indeed be the appropriate means of handling this. As a stopgap measure, though, a filter on get_meta_sql to modify the WHERE clause should hopefully address this (example code provided below to remove the first % from the LIKE condition):

function remove_sql_wildcard_prefix($q)
        $q['where'] = preg_replace("/(LIKE ')%(.*?%')/", "$1$2", $q['where']);
        return $q;
add_filter('get_meta_sql', 'remove_sql_wildcard_prefix');

(disclaimer: the provided code is not yet tested, but in theory I think it works)

comment:4 SergeyBiryukov12 months ago

#23919 was marked as a duplicate.

comment:5 wonderboymusic8 months ago

  • Keywords 2nd-opinion removed
  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from new to closed

This might need better docs, but anyone doing wildcard matching for a meta_query should catch the error the first time they debug it.

comment:6 SergeyBiryukov7 months ago

#25431 was marked as a duplicate.

Note: See TracTickets for help on using tickets.