Quotation Marks in Generated SQL

If you generate SQL statements at runtime, in response to search forms for example, make sure that any quotation marks in the SQL are dealt with correctly.

I have seen too many websites fall over just by entering a quotation mark in the search field.

The code behind the search form probably contained something like:

Sql="SELECT * FROM BigTableOfData
WHERE Keyword='" & SearchString & "'"

If SearchString just happens to contain an apostrophe, then the resulting Sql will contain an odd number of single quotes, as single quotes and apostrophes are the same thing in ASCII.  And you cant stop people from misusing apostrophe's.

To generate foolproof SQL, any single quote within SearchString needs to be replaced with two single quotes.  In VB6, the nice folks at Microsoft have made it easy, with a function called "Replace".

Sql="SELECT * FROM BigTableOfData
WHERE Keyword='" & Replace(SearchString,"'","''")

If I type "Bob's donuts" into a search form coded this way, the generated SQL will look like:

Sql="SELECT * FROM BigTableOfData
WHERE Keyword='Bob''s donuts'"

In SQL, a pair of single quotes within a string literal will be interpreted as a single quote, as long as the quotes around the string itself are also single quotes.