MySQL - Using single quote in text search produces expression error


Error in expression. [timothy] is not a valid field name
Search term was "timothy's". Before the search was submitted, I replace single quotes ' with two single quotes ''
I also got the error when replacing single quotes ' with escaped single quotes \'
The SQL query works with '' or \'
Closed Nov 9, 2008 at 9:54 PM by activa


activa wrote Nov 3, 2008 at 6:23 PM

Can you show a code sample that fails?

veector wrote Nov 5, 2008 at 7:21 PM

Ok, I confirmed that MySQL is expecting \' to escape a ' (single quote), that's why I use the first line. In the actual code the string "Timothy's" comes from a textbox.

string SearchTerm = "Timothy's".Replace("'", "\'");
myReviewList = Review.List("Comments like '%" + SearchTerm + "%'");

veector wrote Nov 5, 2008 at 7:23 PM

In VisualStudio debug, when you move your mouse over the value of SearchTerm, it will show "Timothy\'s"

So it's sending to the database a string which already has the \ escaped.

veector wrote Nov 5, 2008 at 7:26 PM

This MySQL syntax works: SELECT * FROM REVIEWS WHERE Comments LIKE '%Timothy\'s%'

wrote Nov 9, 2008 at 9:54 PM

veector wrote Nov 26, 2008 at 5:09 PM

why was this closed?

activa wrote Nov 28, 2008 at 7:08 PM

This is closed because it's not an issue. It's how MySQL works. From what I can tell, it has nothing to do with CoolStorage.NET

wrote Feb 13, 2013 at 5:44 PM

wrote May 15, 2013 at 11:27 PM