Posted by luebbe on August 4, 2005 at 9:55am
4 followers
Jump to:
| Project: | SQL Search (Trip Search) |
| Version: | 4.6.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
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
#1
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.
#2
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
#3
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?
#4
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]);
#5
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..
#6
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?
#7
I've applied mindless' version of the patch.
#8
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
#9