Use a parameterized query. If you don't use concatenation to glom your SQL statements together, the single quotes are taken care of for you. Example:
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.CommandText = "INSERT dbo.Table(Column) values (?)"
.CommandType = adCmdText
.ActiveConnection = "Connection string" or existing open connection
.Parameters.Append .CreateParameter("@p1", adVarChar, adParamInput, 50, "O'Brien")
.Execute
Set .ActiveConnection = Nothing
End With
The method you're currently relying on is not secure. Let's say you have the following dynamic SQL embedded in your ASP page:
sql = "SELECT Name FROM dbo.Students WHERE Id = " & Request("StudentId")
Now let's say someone decided to try to jam the following into the querystring (or form, or what have you):
1; DROP TABLE dbo.Students;
How does replacing this string with single quotes help you avoid SQL injection? In cases where you know, you could potentially do:
sql = "SELECT Name FROM dbo.Students WHERE Id = " & CLng(Request("StudentId"))
So now you have to go in and re-write this code anyway, and find cases where the data type that is opening you up to injection isn't a string.
SQL injection is not defined solely by exploiting single quotes. There are probably a dozen other ways to do this depending on how you are constructing your SQL strings. If you want to feel safe by just protecting those cases, that's up to you. I wouldn't. I would properly parameterize all queries with type safety. Eliminates all the issues and you will sleep better at night even though the up-front cost may be large.
