Drupal 4.6.2

If you search for a term like "doesn't", the search fails and and several (four) SQL error messages are created. It doesn't matter if the search term containing the single quote(s) is enclosed in double quotes or not.

Example: search for doesn't

You have an error in your SQL syntax near 't%%' OR t.description LIKE '%%doesn't%%') LIMIT 0, 500' at line 1 query: SELECT DISTINCT(t.tid), t.name, t.description FROM drupal_term_data t WHERE (t.name LIKE '%%%%' OR t.description LIKE '%%%%') AND (t.name LIKE '%%doesn't%%' OR t.description LIKE '%%doesn't%%') LIMIT 0, 500 in /home/groups/tortoisesvn/htdocs/includes/database.mysql.inc on line 66.

Example: search for "doesn't"

You have an error in your SQL syntax near 't%%' OR t.description LIKE '%%doesn't%%') LIMIT 0, 500' at line 1 query: SELECT DISTINCT(t.tid), t.name, t.description FROM drupal_term_data t WHERE (t.name LIKE '%%%%' OR t.description LIKE '%%%%') AND (t.name LIKE '%%doesn't%%' OR t.description LIKE '%%doesn't%%') LIMIT 0, 500 in /home/groups/tortoisesvn/htdocs/includes/database.mysql.inc on line 66.

Searching for doesn finds all the entries I was looking for :-)

Comments

luebbe’s picture

luebbe commented
Status: Active » Needs review

I've got a small fix for this. At least it didnt' cause any visible harm up to now.

Find the function trip_search_parse and add the following line between the $search = trim($search); and $search = split(' ', $search); lines near the beginning of the function.

  $search = addslashes($search);

function trip_search_parse($search) {
  //find quoted strings
  preg_match_all('!-?"(.*?)"!is', $search, $quotes);
  foreach ($quotes[0] as $index => $value){
    // remove the quoted strings
    $search = str_replace($quotes[0][$index], '', $search);
    if (substr($quotes[0][$index], 0, 2) == '-"') {
      $quotes[1][$index] = '-' . $quotes[1][$index];
    }
    // remove extra spaces
    $search = str_replace('  ',' ', $search);
  }
  $search = trim($search);
  $search = addslashes($search);
  $search = split(' ', $search);
  //merge the quotes and non quotes into one array
  $search = array_merge($search, $quotes[1]);

properly passing the results to the highlight module needs a stripslashes in the right place. I'm investigating.

luebbe’s picture

luebbe commented

Find the function trip_search_highlight and add the stripslashes near the end

function trip_search_highlight($parsed_keys) {
if($parsed_keys->require) {
$keys[] = implode(',', $parsed_keys->require);
}
if($parsed_keys->or) {
$keys[] = implode(',', $parsed_keys->or);
}
$keys = stripslashes(implode(',', $keys));
return $keys;
}

I'm still trying to find a way to get rid of the leading comma if a search with double quotes has been made

mindless’s picture

mindless commented

Thanks for the patch.. but it appears this only handles the simple search or "with all words" line in advanced search, correct? I entered a ' in "with the exact phrase" in advanced search and still got an error. Perhaps addslashes in trip_search_translate_keys will help, or somewhere earlier in the processing. Also, is it more "correct" to use db_escape_string?

mindless’s picture

mindless commented

Ah, now I see how advanced search combines all the elements into a single string and then parses that.. so trip_search_parse seems to be the right place.. the patch above just omits escaping for values in the $quotes array.. here's the patch I'm trying (I have some other edits so I omitted lines numbers here, sorry)..

   preg_match_all('!-?"(.*?)"!is', $search, $quotes);
   foreach ($quotes[0] as $index => $value){
     // remove the quoted strings
-    $search = str_replace($quotes[0][$index], '', $search);
-    if (substr($quotes[0][$index], 0, 2) == '-"') {
-      $quotes[1][$index] = '-' . $quotes[1][$index];
-    }
-    // remove extra spaces
-    $search = str_replace('  ',' ', $search);
+    $search = str_replace($value, '', $search);
+    $quotes[1][$index] =
+      (substr($value, 0, 2) == '-"' ? '-' : '') . db_escape_string($quotes[1][$index]);
   }
-  $search = trim($search);
+  // remove extra spaces
+  $search = db_escape_string(trim(str_replace('  ',' ', $search)));
   $search = split(' ', $search);
   //merge the quotes and non quotes into one array
   $search = array_merge($search, $quotes[1]);
mindless’s picture

mindless commented

I resolved the leading comma in "quote only" search by changing this line (see it in my patch above):
$search = split(' ', $search);
to:
$search = empty($search) ? array() : explode(' ', $search);

Then I fixed highlighting using a similar change to the one listed previously:
$keys = implode(',', $keys);
changed to:
$keys = empty($keys) ? '' : stripslashes(implode(',', $keys));
(an advanced search may omit both required and or searches)

and this change in trip_search_translate_keys:

+      $keys = array_map('stripslashes', $keys);
       break;
     case 'mysql_full':

to fix trip_search_excerpt (and presumably trip_search_rank_results too).
Hope this helps..

nedjo’s picture

nedjo
he/him/his
Primary language English
commented

Thanks for the fixes. I haven't found time to address this important issue. If you want to test and apply this patch, please do. But I'm thinking this is only half of the solution. If we're removing apostrophes, we won't get matches. Do we need to work on the search sql, so that matches are found?

nedjo’s picture

nedjo
he/him/his
Primary language English
commented
Status: Needs review » Fixed

I've applied mindless' version of the patch.

dmuth’s picture

dmuth commented

Will there be a new version of this package available for download with the patch already installed?

I manage several Drupal sites and would like to avoid having to go through the process of patching each one. :-)

Thanks,

-- Doug

Anonymous’s picture

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