By cherylchase on

I'm converting a site with several hundred static html pages to drupal (by lightly parsing the pages with perl, then directly inserting data into the node and term_node mysql tables). I ran cron.php and began to experiment with searching. If my search input contains an apostrophe, I get the following error:

user error: 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 's increasingly succesful efforts%' OR n.body LIKE '%MAB will he
query: SELECT n.*, e.* FROM event e INNER JOIN node n 
ON n.nid = e.nid WHERE 
(n.title LIKE '%MAB will help guide ISNA's increasingly succesful efforts%' 
OR n.body LIKE '%MAB will help guide ISNA's increasingly succesful efforts%' OR 
n.teaser LIKE '%MAB will help guide ISNA's increasingly succesful efforts%'  OR 
e.location LIKE '%MAB will help guide ISNA's increasingly succesful efforts%') 
ORDER BY n.changed DESC LIMIT 0, 20 in 
/Library/WebServer/Documents/drupal/includes/database.mysql.inc on line 90.

1. This doesn't happen on drupal.org, but it does happen on northbaydean.org. Is this a known bug that is fixed in cvs? If search.module is interpolating the text of the user's search input directly into sql, that sounds like a scary security hole.



2. Also, is there a way to search just drupal bug reports?

Comments

ax’s picture

ax commented

2. http://drupal.org/project/drupal/issues/search

1. you find yourself ;)

terre’s picture

terre commented

I have two drupal sites, on the same server. One site does not have this problem with apostrophes in search but the other produces the same error. Example:

user error: You have an error in your SQL syntax near 's%' OR n.body LIKE '%children's%') ORDER BY n.changed DESC LIMIT 20' at line 1 query: SELECT n.*, b.* FROM event b INNER JOIN node n ON n.nid = b.nid AND n.nid = b.nid WHERE (n.title LIKE '%children's%' OR n.body LIKE '%children's%') ORDER BY n.changed DESC LIMIT 20 in /home/httpd/vhosts/mysite/httpdocs/includes/database.mysql.inc on line 90.

Neither site has a problem with posting of any content with apostrophes. .htaccess appears to be honored for each. Phpinfo for both sites shows global magic_quotes on.

The site that has the search error shows locally magic_quotes is off, while the site that performs searches fine with apostrophes has magic_quotes on locally - as determined with phpinfo.

So the magic_quotes setting being off locally seems to screw up only the search when an apostrophe is used but not for any other type of posting (no problem with backslashes, and search_index does fine.)

Like the poster above I've done plenty of searches but the solutions all are for cases where magic_quotes turned on is causing a problem, not for where magic_quotes turned off is causing a problem. And this is only in the search function.

The problem seems like it would be solved by changing the search term with an apostrophe into a wild card. This even has the added advantage of producing more correct search results (not perfect), whereas currently an apostrophe in search does not return any search results.

Anyone know of how to modify search to make the apostrophe a wildcard?

Leeteq’s picture

Leeteq commented

This issue for trip_search is still not solved. Strange, for this type of bug.
I have several sites that will still not make the move to 4.7, so I am interested in how to get around or rid of this problem.

Would appreciate any advice/pointer.
I just asked for an update in the trip_search issue tracker too.
http://drupal.org/node/8894

.
--
( Evaluating the long-term route for Drupal 7.x via BackdropCMS at https://www.CMX.zone )

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented

Just a note that I have a possible fix for this issue.

With the development time supported, of course, by pingVision, LLC.

--
Morris Animal Foundation