zzapper wrote: [8 Dec 2003 19:07:49 -0000]
Hi Ya, I have a typical select as follows
SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%')
However users a complaining that if they search for for a name that contains a quote eg a development named King's Reach it is not found.
A) Now it simply won't match King's Reach whether I include the quote or not. WHY?? (Have the quotes been converted ie to URL Encoding))
Consider what your query looks like when form.searchtext contains a single quote. Using your example, you get:
SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King's Reach%')
The single quote in King's completes the quoted string! I'll rewrite it with a newline for emphasis:
SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King' s Reach%')
"s Reach%'" is not valid SQL, so I expect that if you checked, you would find that mysql is throwing a syntax error in this case (or waiting for the 4th ').
B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?)
You don't want to filter the quotes from the input string, because they exist in the data. (Well, you could filter from both sides, but that's inefficient.) What you need to do is escape the quotes with backslashes before sending them to mysql. Then your query will look like this:
SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King\'s Reach%')
So, you need to replace ' with \' in your form.searchtext. I'm not familiar with ColdFusion, but I expect you can use a variant of the replace function you mention below to do the job. PHP has a single function call to handle escaping text -- perhaps ColdFusion does as well?
I look forward to seeing how you solve this.
[9 Dec 2003 10:59:11 -0000] > Is there no mysql solution to this? Most Where clauses are thus > > WHERE LHS Like/= RHS > > Now MySQL provides lots of operators for the RHS eg > > WHERE LHS RLIKE RHS > > But what I want to do is preprocess/filter the LHS before doing the > comparison, I can find no information on this. In the past I have > cheated by creating an additional column in my database eg > txtDevNameClean which has all non-alphanumerics removed. am I barking > up the wrong tree??
I think so. As I described above, I think the problem is with your query, not mysql. Hence, you must escape the search text on the application side to ensure valid SQL before sending to mysql.
Note that single quotes are not your only worry. As your form stands now (I'm assuming based on the piece you've shown), imagine what could happen if the user entered something like
King'); DELETE FROM TABLE ytbl_development WHERE txtDevName LIKE '
as the search text (form.searchtext). You would end up with
SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King'); DELETE FROM TABLE ytbl_development WHERE txtDevName LIKE '%')
The moral is that you should always validate user input before passing it to mysql.
[9 Dec 2003 11:54:58 -0000] > Hi Ya. > I seem to have solved my problem if I clean out any punctuation from > my search string (see below (ColdFusion script)) > > <cfset form.searchtext=replace(form.searchtext,'[[:punct:]]','','all') > > > When I perform the WHERE LHS-Containing-Quote LIKE RHS-without-quotes > > The LIKE appears to ignore the quotes (which is what I want) > > BUT IS THAT A FEATURE OF LIKE???
It's hard to say without seeing the query. I don't believe LIKE ignores quotes, but if your wildcard (%) is in the right place, it could seem to. I think your emphasis on LHS versus RHS is confusing the issue. If you'd like to pursue this, post your query and the results you get so we can see what you mean.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
