||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
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.
For more information on this error be sure to check out:
-- FAQ: How do I get SQL to accept an apostrophe in various queries, such as when I try to add a user named "O'Brien"?
-- Microsoft KB Article on the Issue