Published: Tuesday, May 18, 1999
How to Deal with Apostrophes in your SQL String
OK, you've been given an assignment to create a web page that collects your
client's information and stores this information in a database. This is your
first big project, and a chance for you to prove your worth to the company!
All that you need to do is grab the client's company name and phone number!
Painstakingly, you write the code, two pages, an HTML page with a form to collect
the user input, and an ASP page to retrieve that information and slap it into
a database. Your ASP page looks something like this:
<%
'Get the form data
Dim strPhoneNumber, strCompanyName
strPhoneNumber = Request.form("PHONE")
strCompanyName = Request.form("NAME")
'Make connection to database
...
'Construct SQL String
Dim strSQL
strSQL = "INSERT ClientTable (CompName,Phone) " & _
"VAULES ('" & strCompanyName & _
"','" & strPhoneNumber & "')"
...
%>
Ah, you've done a smashing job! You are truly an ASP expert, wait until the boss
sees this, he will be so proud! Speaking of which, here he comes, wanting to
give your app a little test. OK, no biggie. He sits down, loads up the form.
For phone number he enters "123-345-6778" and for Company name he enters
"Startbuck's Coffee." When he submits the form, he gets an ADO error!!! Oh
crap! There goes that raise!
What happened? Why did an error occur? The reason has to do with apostrophes.
strCompanyName contains an apostrophe, so when strSQL
is constructed, it equals: "INSERT ClientTable (CompanyName, Phone)
VALUES('Startbuck's Coffee','123-345-6778')"! Note the apostrophe! What is
SQL going to think? Where does the Company Name string end? After the k in
Startbuck's or after the last e in Coffee? Since SQL becomes confused, your
script won't work!
So does this mean that your company can only take on clients who don't have an
apostrophe in their company name? Thanksfully, no. SQL isn't very bright, but
it isn't very dumb either. If SQL sees two apostrophes, one right after the other,
it assumes you want just a single approstrophe its place. The two apostrophes
don't confuse SQL into not knowing where the end is. So, all we have to do is
tell the user to enter the company name as "Starbuck''s Coffee," right?
Well, no. That would be mean. What we will do is write a single line of code
that will replace all instances of single apostrophes with two apostrophes.
Here is how!
strCompanyName = Replace(Request.form("NAME"), "'", "''")
That will take the string in Request.form("NAME"), search for all
single apostrophes, and replace them with two aprostrophes! That's all you need
to do! Had you done this, your boss's test would have worked, you would have been
promoted, you would have eventually become filthy rich! Now you know why the
apostrophe thing is so important. Have a great day!
Related: Replace Function Technical Specifications
