Properly encoding a SQL statement through a Function

One of the biggest questions that people come to me regarding SQL statements is when the data that they need to store in the database contains a single quote (my dog's name is scoobie). Normally, if you were to do a simple select statement on a database it would give you an error. This is because in most popular databases the single quote is used as a text delimiter. That is, it is used to mark the start and end of a string.



SELECT * FROM authors WHERE fname = 'nothing'

Above, the value "rob" is delimited by the single quotes. Now if we attempted:

SELECT * FROM authors WHERE comments = 'my dog's name is scoobie'

You can see the obvious mistake. - comments = 'my dog'

The rest of the string will generate an error.

SOLUTION:
The solution is simple. What we need to do is double up the single qutoes. So, from our example above,

SELECT * FROM authors WHERE comments = 'my dog's name is scoobie'

Now becomes
SELECT * FROM authors WHERE comments = 'my dog''s name is scoobie'

The replace function will enable us to do this very easily.

The easiest way to solve this problem is to include a simple function in our utilities.inc file:


Function sqlEncode(sqlValue)
     sqlEncode = replace(sqlValue, "'", "''")
End function

Now whenever we build any sql statements all we need to do is use the sqlEncode function to encode our data.

sql = "SELECT * from FROM WHERE comments = '" & sqlEncode(strComments) & "'"


Author:
Robert Chartier
nothingmn@netexecutive.com
October 2, 1999
blog comments powered by Disqus