Friday, February 26, 2010

Handling strings containing quotes properly in SQL

Everyone is aware that quotes in strings used in SQL cause all sorts of problems. As a developer, it is our duty to ensure that an end user never sees an SQL error because we didn't handle his input appropriately! But how the heck do you ensure that quotes are handled correctly? The following function will take a string and return it enclosed with quotes and all instances of quotes in the string will be doubled up. This will work in SQL.

For example if I call QuotedString("This is a "string" example") then it will return:

"This is ""a string"" example"


Public Function QuotedString( _
strText As String) As String

Const conQuoteChar = """"

QuotedString = conQuoteChar _
& Replace$(strText, conQuoteChar, conQuoteChar & conQuoteChar) _
& conQuoteChar
End Function