Using the 'contains' filter with any string beginning with a B causes the following errors:

    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node.title'ranch%')) AND (node.status = '1')' at line 1 query: SELECT count(node.nid) FROM node node WHERE (UPPER(node.title) LIKE UPPER(''node.title'ranch%')) AND (node.status = '1') in /var/www/vhosts/livelinknewmedia.com/httpdocs/includes/database.mysql.inc on line 172.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node.title'ranch%')) AND (node.status = '1') LIMIT 0, 10' at line 1 query: SELECT node.nid FROM node node WHERE (UPPER(node.title) LIKE UPPER(''node.title'ranch%')) AND (node.status = '1') LIMIT 0, 10 in /var/www/vhosts/livelinknewmedia.com/httpdocs/includes/database.mysql.inc on line 172.

This appears to be because db_query is treating '%b' at the start of '%branch%' as a binary field for substitution. (The above error appears using 'branch' as the filter.)

To duplicate the error:

- Add new view
- Set up to provide page view
- Add filter for Node: Title - set operator to 'Contains' and enter value as any string beginning with 'b'
- Save, view

I mean honestly, who thought '%' was a good character to use for that.

CommentFileSizeAuthor
#16 mw_83.patch1.99 KBmoshe weitzman
#8 percent_b.patch975 bytesmerlinofchaos
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

kingandy’s picture

kingandy CreditAttribution: kingandy commented
Status: Active » Needs review

OK, after some small investigation of my own I've come up with a fix. I don't know how to do a patch, though, so you'll have to bear with me.

The problem is at line 1936 of my views.module (v 1.166.2.43 2007/07/14 18:54:16 merlinofchaos Exp).
The code as it stands is this:

          $query->add_where("UPPER(%s) LIKE UPPER('%%%s%%')",
            $field, $filter['value']);

This seems like a grand idea, except for some reason the later db_query function processes the string replacements (changing it to "UPPER(node.field) LIKE UPPER('%blablabla%')", where $field is 'node.field' and $filter['value'] is 'blablabla'), and then somehow processes it again, spotting the %b in the query string, I don't know.

Anyway, to fix this I lifted the '%%' wrapper out of the query string and into the string being substituted in, like so:

          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');

It seems to work, but I don't know if there are any unexpected ramifications, so if people could give it a good solid hammering I'd much appreciate it.

kingandy’s picture

kingandy CreditAttribution: kingandy commented

Oh, for people with a different iteration of views, this is in the views_handler_filter_like function, in the first case of the $filter['operator'] switch statement (case 'contains').

The thought occurs that maybe similar things should be done with the 'ends' and 'not' cases.

Lausch’s picture

Lausch CreditAttribution: Lausch commented

hi kingandy,

thanks for forward me to this thread,

your solution seems work but what about

contain all words operator?


$words = trim($match[2], ',?!();:-');
            $words = $phrase ? array($words) : preg_split('/ /', $words, -1, PREG_SPLIT_NO_EMPTY);
            foreach ($words as $word) {
              $where[] = "UPPER(%s) LIKE UPPER('%%%s%%')";
              $values[] = $field;
              $values[] = trim($word, " ,!?");
            }

seems more complicated

greets

Lausch

kingandy’s picture

kingandy CreditAttribution: kingandy commented

Hmm, well it's still boiling down to a "x LIKE '%y%'" clause, it's just doing one for each word in the string (assembling the clauses into an array and then imploding them later). So you'd need to do this:

          $words = trim($match[2], ',?!();:-');
            $words = $phrase ? array($words) : preg_split('/ /', $words, -1, PREG_SPLIT_NO_EMPTY);
            foreach ($words as $word) {
              $where[] = "UPPER(%s) LIKE UPPER('%s')";
              $values[] = $field;
              $values[] = '%%' . trim($word, " ,!?") . '%%';
            }

I've tried it out in my version and it seems to work as expected. I also modified the 'starts' clause (only appending the $filter['value'] with a wildcard) and 'ends' (only prepending) as well as the 'not' (both ends). So at the end of the day my switch statement looks like this:

      switch ($filter['operator']) {
        case 'contains':
          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');
			/*
			PRECEDING TWO LINES CHANGED 13/8/2007 ANDY MASON - used to be this
          $query->add_where("UPPER(%s) LIKE UPPER('%%%s%%')",
            $field, $filter['value']);
			*/
          break;
        case 'word':
        case 'allwords':
          preg_match_all('/ (-?)("[^"]+"|[^" ]+)/i', ' '. $filter['value'], $matches, PREG_SET_ORDER);
          foreach ($matches as $match) {
            $phrase = false;
            // Strip off phrase quotes
            if ($match[2]{0} == '"') {
              $match[2] = substr($match[2], 1, -1);
              $phrase = true;
            }
            $words = trim($match[2], ',?!();:-');
            $words = $phrase ? array($words) : preg_split('/ /', $words, -1, PREG_SPLIT_NO_EMPTY);
            foreach ($words as $word) {
			  // Following lines changed to move '%%' froom $where to $value
              $where[] = "UPPER(%s) LIKE UPPER('%s')";
              $values[] = $field;
              $values[] = '%%' . trim($word, " ,!?") . '%%';
            }
          }
          if ($filter['operator'] == 'word') {
            $where = '('. implode(' OR ', $where) .')';
          }
          else {
            $where = implode(' AND ', $where);
          }
          // previously this was a call_user_func_array but that's unnecessary
          // as views will unpack an array that is a single arg.
          $query->add_where($where, $values);
          break;
        case 'starts':
			  // Following lines changed to move '%%' froom $where to $value
          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, $filter['value'] . '%%');
          break;
        case 'ends':
			  // Following lines changed to move '%%' froom $where to $value
          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value']);
          break;
        case 'not':
			  // Following lines changed to move '%%' froom $where to $value
          $query->add_where("UPPER(%s) NOT LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');
          break;
        case '=':
          $query->add_where("UPPER(%s) = UPPER('%s')",
            $field, $filter['value']);
          break;
      }

Hope that helps...

Lausch’s picture

Lausch CreditAttribution: Lausch commented

oh maaaan, thank you sooo much!!!

i will not forget this my friend!

i hope this will be included in the next views version.

it "seems" work now.

i done some tests and all seems fine.

I'm so happy now :-)

OK if i copy paste your code, it will not work. i think we use other views versions?

i added your changes manually and now it works

here is the working code for other 5x-1.6 users


switch ($filter['operator']) {
        case 'contains':
		
		 $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');
           
          $query->add_where("UPPER(%s) LIKE UPPER('%%%s%%')",
            $field, $filter['value']);
          
      
        break;
        case 'word':
        case 'allwords':
          preg_match_all('/ (-?)("[^"]+"|[^" ]+)/i', ' '. $filter['value'], $matches, PREG_SET_ORDER);
          foreach ($matches as $match) {
            $phrase = false;
            // Strip off phrase quotes
            if ($match[2]{0} == '"') {
              $match[2] = substr($match[2], 1, -1);
              $phrase = true;
            }
			$words = trim($match[2], ',?!();:-');
            $words = $phrase ? array($words) : preg_split('/ /', $words, -1, PREG_SPLIT_NO_EMPTY);
            foreach ($words as $word) {
              $where[] = "UPPER(%s) LIKE UPPER('%s')";
              $values[] = $field;
              $values[] = '%%' . trim($word, " ,!?") . '%%';
            }
          }
          if ($filter['operator'] == 'word') {
            $where = '('. implode(' OR ', $where) .')';
          }
          else {
            $where = implode(' AND ', $where);
          }
          // previously this was a call_user_func_array but that's unnecessary
          // as views will unpack an array that is a single arg.
          $query->add_where($where, $values);
          break;
        case 'starts':
             
          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, $filter['value'] . '%%');
          break;
        case 'ends':
           
          $query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value']);
          break;
        case 'not':
             
          $query->add_where("UPPER(%s) NOT LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');
          break;
        case '=':
          $query->add_where("UPPER(%s) = UPPER('%s')",
            $field, $filter['value']);
          break;
      }
    break;
  }

many greets

Lausch

kingandy’s picture

kingandy CreditAttribution: kingandy commented

"OK if i copy paste your code, it will not work. i think we use other views versions?"

Yeah, looking at your original issue it looks like you're using 1.6-beta5, whereas I think I'm still on the flat 1.6. That said, I think the only difference between our code is that you have an extra break;} at the end - I think when you pasted it in you may have misjudged the switch statement (it's actually inside another switch statement, which looks unneccessary to me as there's only one case and it might as well be an if statement, but I guess they were future-proofing or it was a holdover from an earlier multi-case iteration or ... something ... but I digress.)

BTW, in your 'contains' case:

        case 'contains':

$query->add_where("UPPER(%s) LIKE UPPER('%s')",
            $field, '%%' . $filter['value'] . '%%');
          
          $query->add_where("UPPER(%s) LIKE UPPER('%%%s%%')",
            $field, $filter['value']);
         
     
        break;

You want to remove the second $query->add_where() call, that's the old (nonfunctional) code that I left in inside a comment in case I needed to change it back.

Lausch’s picture

Lausch CreditAttribution: Lausch commented

what? this is impossible

is use views-5.x-1.6.tar.gz

seems like the newest version (update status also say)

but this all is not interesting at the moment.

only that this shixxx works now :-)

today i will try some different exposed searches and will post again whether all seems working

greets

Lausch

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
FileSize
percent_b.patch975 bytes

I believe I understand what the actual problem is.

The problem is that views that have their query cached are getting put through the % handling code twice, and this is causing problems with %b, %d, %f and %s.

I believe this patch will fix it. If anyone here is capable of applying a patch and letting me know, I would appreciate it.

idealso’s picture

idealso CreditAttribution: idealso commented

The patch works for me, no more SQL errors. It also fixes a less obvious problem with strings starting with s, and presumably also with d. In those cases, there would be no error, but it would fail to match.

Thank you, this is much appreciated.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Oh and be sure to clear the Views' cache after applying this patch.

jenlampton’s picture

jenlampton
she / her
CreditAttribution: jenlampton commented

this patch works for me too, thanks!
Jen

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Assigned: Unassigned » merlinofchaos
Status: Needs review » Reviewed & tested by the community
daniel sanchez’s picture

daniel sanchez CreditAttribution: daniel sanchez commented

Perhaps I don't understand how to apply this patch properly. I'm taking the views_cache.inc file in the views folder and replacing the function:
_views_get_query(&$view, $args, $filters)
with the patch code here: http://drupal.org/files/issues/percent_b.patch
I empty cache using the Devel module and Clear Views Cache in the Views Tools panel.
Still I'm getting the same problem. Am I missing something?

Thanks for your help.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

The easiest way to apply the patch is to download it into your modules/views directory, then grab a shell, cd into that directory and type:

patch -p0 < patchfilename

If you're using windows it's a little harder to actually patch things, but the lines with a - in the patch represent lines that should be deleted.

Lines with a + represent lines that should be added.

Lines that have neither are just guide points to tell you where to add/delete lines.

daniel sanchez’s picture

daniel sanchez CreditAttribution: daniel sanchez commented

Thanks for that explanation. Got it cleared up now and it is working perfectly.
Thank you.

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented
Title: 'b...' breaks filters » 'b...' breaks filters - avoid double query argument replacement
FileSize
mw_83.patch1.99 KB

i'm copying the patch from http://drupal.org/node/177543 into this issue since it is more complete (it also gets rid of the now unused _views_replace_args function). i tested this patch and it works so i'll keep this issue in RTBC status.

this is a very subtle bug and i spent too much time finding and fixing it myself only to find that it was already solved in the queue. oops.

devin.gardner’s picture

devin.gardner CreditAttribution: devin.gardner commented

I applied moshe weitzman's patch and it fixes this problem on my setup. Good work.

Me and my QA guy were laughing pretty hard when we discovered there was a bug that gives SQL errors only when you search for a word that begins with a lowercase 'b'. How random is that?

fractile81’s picture

fractile81 CreditAttribution: fractile81 commented

+1 on the patch.

I've applied it in my dev environment and everything appears to be working properly now (sorting on a timestamp).

bjaspan’s picture

bjaspan CreditAttribution: bjaspan commented

Subscribe. I think my issue http://drupal.org/node/183118 is a duplicate of this one, I'll test this patch soon and mark that issue duplicate if it is.

davej’s picture

davej CreditAttribution: davej commented

mw_83.patch worked for me: resolved a problem in sorting by date, which caused the following SQL to be used when supposedly sorting with granularity day:

SELECT term_data.name, term_data.weight, term_data.tid, count( DISTINCT(node.nid)) AS num_nodes, DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m0') AS node_created_orderby, node.title AS node_title, node.changed AS node_changed
  FROM node node ...

The '%Y%m0' comes from '%Y%m%%d' in views.module views_handler_sort_date.

Thanks,
Dave Jenkins
Circle Interactive

mdekkers’s picture

mdekkers CreditAttribution: mdekkers commented

mw_83.patch works for me also - fixed case sensitive issue on views - Thanks Moshe!

Jody Lynn’s picture

Jody Lynn
she/her
Primary language English
CreditAttribution: Jody Lynn commented

Same thing for me - patch fixed my problem in the Organic Groups view by allowing the search to be case-insensitive.

amanuel’s picture

amanuel CreditAttribution: amanuel commented

Moshie's patch in #16 worked well for me.

+1 to commit this.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Reviewed & tested by the community » Fixed

Committed to -dev.

Matthew Davidson’s picture

Matthew Davidson CreditAttribution: Matthew Davidson commented

Any reason why the patch removes _views_replace_args()? This breaks tagadelic_views, at the very least.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Because _views_replace_args() is no longer used and shouldn't really have been used; it was a bad idea. tagadelic_views shouldn't be using it, as the _ indicates it's a private function, anyhow.

Matthew Davidson’s picture

Matthew Davidson CreditAttribution: Matthew Davidson commented

Cool. Posted a bug there. Thanks for the quick response; you're a legend.

nath’s picture

nath CreditAttribution: nath commented

We've got hit by this bug as well. Thanks for the fix. Is the 1.x-dev usable or should we apply only the patch?

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

There's only a couple of patches in 1.x-dev so it's perfectly usable.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

xamount’s picture

xamount CreditAttribution: xamount commented

I have applied Moshe's patch and cleared the views cache but I still get the error if i search for a keyword beginning with 'b'.

I have mysql cache set up as well....do you think this can be causing the error to remain? Or is there something I missed?

xamount’s picture

xamount CreditAttribution: xamount commented

Ahh...think I figured it out (but someone can tell me if I am wrong).

I realised that the 'b' queries were only working if I was logged in with admin but not for anonymous users. So this led me to disabled the drupal normal cache mode (which was enabled).

I then ran a 'b' query and all was fine as an anonymous user. I then re-enabled the drupal normal page cache mode and all is still fine so far.

kulfi’s picture

kulfi CreditAttribution: kulfi commented
Status: Closed (fixed) » Reviewed & tested by the community

I'm still experiencing this in 5.x-1.6, should I be using the -dev version?

nath’s picture

nath CreditAttribution: nath commented

I think so, as the fix came after 1.6.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Reviewed & tested by the community » Closed (fixed)

*shakes head at people who re-open closed issues*

akahn’s picture

akahn CreditAttribution: akahn commented

Sun, can you make a new stable 5.x release that includes this fix?

(Got here from #212906: Posted Full Date argument partially broken)

dooug’s picture

dooug CreditAttribution: dooug commented

I'm experiencing this bug using Views 5.x-1.6. The exposed filter is on a CCK Address field.

Is there a release without this bug?

akahn’s picture

akahn CreditAttribution: akahn commented

dooug, it is fixed in the current 5.x-1.x-dev version of Views.

yolene’s picture

yolene CreditAttribution: yolene commented

Hi,
Sorry to bother you guys with a newbie's question, but :
i tried to update to from 1.6 to 5.x-1.x-dev version of views : so i transfered the module in the module folder, erased the previous one, then i run update.php but it tells me there is no update for views ... i guess i't because it's not an "update" but a dev version ... what am i supposed to do then ?
Thanks a lot in advance for your help.

kingandy’s picture

kingandy CreditAttribution: kingandy commented

Update.php manages modifications to the database. Not all updates require a change to the database - if you update a module and there's no update on the update.php page, the changes probably involved code rather than data. (Or, to put it another way, there was an improvement to the way the system fetches and handles information, but the part of the system that actually stores the information wasn't changed.)

yolene’s picture

yolene CreditAttribution: yolene commented

ok thanks, yet i have a biiiiiiiiiiig problem now. I update to the new dev version, and i re submitted the module page as precised in the readme file but now : all my views have problems !

Here is what it says :

* user warning: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay query: SELECT count( DISTINCT(node.nid)) FROM node node LEFT JOIN users_roles users_roles ON node.uid = users_roles.uid INNER JOIN users users ON node.uid = users.uid LEFT JOIN profile_values profile_prenom ON users.uid = profile_prenom.uid AND profile_prenom.fid = '2' LEFT JOIN profile_values profile_regionlive ON users.uid = profile_regionlive.uid AND profile_regionlive.fid = '11' LEFT JOIN profile_values profile_startdateevs ON users.uid = profile_startdateevs.uid AND profile_startdateevs.fid = '18' LEFT JOIN profile_values profile_enddateevs ON users.uid = profile_enddateevs.uid AND profile_enddateevs.fid = '19' LEFT JOIN profile_values profile_cooriginevs ON users.uid = profile_cooriginevs.uid AND profile_cooriginevs.fid = '37' LEFT JOIN profile_values profile_coevs ON users.uid = profile_coevs.uid AND pr in /home/sites/sve-connexion.fr/public_html/includes/database.mysql.inc on line 174.
* user warning: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay query: SELECT DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, profile_prenom.value AS profile_prenom_value, profile_regionlive.value AS profile_regionlive_value, profile_startdateevs.value AS profile_startdateevs_value, profile_enddateevs.value AS profile_enddateevs_value, profile_cooriginevs.value AS profile_cooriginevs_value, profile_coevs.value AS profile_coevs_value, profile_cityevs.value AS profile_cityevs_value FROM node node LEFT JOIN users_roles users_roles ON node.uid = users_roles.uid INNER JOIN users users ON node.uid = users.uid LEFT JOIN profile_values profile_prenom ON users.uid = profile_prenom.uid AND profile_prenom.fid = '2' LEFT JOIN profile_values profile_regionlive ON users.uid = profile_regionlive.uid AND profile_regionlive.fid = '11' LEFT JOIN profile_values profile_startdateevs ON users.uid = p in /home/sites/sve-connexion.fr/public_html/includes/database.mysql.inc on line 174.

What happened ? what shall i do ? How to get back to the previous version (i'd rather have a problem with views with "b" filters than with ALL views!) ?

Please i really need your help to fix this.
Thanks

kingandy’s picture

kingandy CreditAttribution: kingandy commented

You probably need to clear your views cache, but in all honesty you'll get more help by creating a new issue than posting in an old, dead topic like this.

yolene’s picture

yolene CreditAttribution: yolene commented

Ok, thanks a lot. I did clear the cache but still gets this problem.
According to your advice i posted a new issue here
http://drupal.org/node/455836

If anyone can help, it would be great. Otherwise i'll have to find a way to get back to the previous version of views, the one with the "b" bug :-(

Thanks in advance for any help

yolene’s picture

yolene CreditAttribution: yolene commented

OK here is the trick which may help many people to fix this problem (although it doesn't really solve it).
I've tried to upgrade tu 5.1dev version as suggeted, it broke some views, i rolled back.

Anyway : one quick (and amazing) fix for this bug is to enter the word in your argument with a capital B instead of a small b.
Ex :
Field region contains Bourgogne works and gives entries with "bourgogne", "Bourgogne" and "BOURGOGNE"
Field region contains bourgogne returns an SQL error

How amazing is that uh ? I'm not a programmer, i was just desperate and tried any single option there could be and ended up on that one.
I hope it can help ... waiting for the next official update of the module ...

matteogeco’s picture

matteogeco CreditAttribution: matteogeco commented

Subscribing.