When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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:

- continued -

'

<%
'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



ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article