0

I'm querying a MySQL DB with some strings in one field which contain apostrophes which I cannot remove or escape when adding to the DB. How do I format a query not to fail on a string containing an apostrophe? For example, doing a query against a FULLTEXT indexed field:

"SELECT * FROM NationalTideStationsA WHERE MATCH(location) AGAINST('$myState')";

This fails whenever the returned string has an apostrophe, for example, when the location field contains:

"Cedar Tree Neck, Martha's Vineyard, Massachusetts"

all queries for locations in Massachusetts fail.

I cannot work out if SQL offers a way to format the query to cope with that.

The SELECT query works just as desired otherwise.

Dan
  • 527
  • 1
  • 7
  • 25
  • 2
    Read up on "sql injection". Use your db's encoding and escaping methods. – Rudie May 16 '14 at 19:25
  • 1
    **Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. **Running SQL statements built with outside data is like eating soup made from ingredients found on your doorstep.** – Andy Lester May 16 '14 at 20:42

1 Answers1

0

Agreed on the suggestion to read up on sql injection. For the immediate, replace all single quotes with two single quotes.

Jaaz Cole
  • 3,119
  • 1
  • 15
  • 20