One of the big problems people have when they are writing SQL within ASP is dealing with data supplied by users. You may have some code that looks like this:
Dim strSQL
strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _
& Request.Form("Keywords") & "'"
This will return all the customers where the CompanyName field is equal to whatever the person put in the Keywords field on a form. The problem comes when you have a name with a single quote in it, such as the name O'Reilly. The single quote character breaks the SQL statement.
The easy thing to do is to "clean" the input data before putting it into the SQL statement. Here's a quick function you can use, and the SQ constant that holds a single quote.
Const SQ = "'"
Function Clean(strData)
Clean = Replace(strData, SQ, SQ & SQ)
End Function
The previous chunk of code looks like this with the Clean function in place:
Dim strSQL
strSQL = "SELECT * FROM Customers WHERE CompanyName = '" _
& Clean(Request.Form("Keywords")) & "'"