View Full Version : How to handle single quotes in URLs?

Grant Palin
04-30-2004, 07:46 PM
I'm working on an ASP page that gets a list of books from a database. The page first prints out a list of all the books, each title being a clickable link. Each link goes to the same page with a querystring in the URL. I then retrieve the querystring, get the book's title, and get more information about that book from the database and display the info.

This works fine for the most part, but I have a few books with a single quote in the title. When I click the link for those books, the page reloads, as expected, and the querystring appears as expected, except that the book's title is cut off after the single quote. This of course will not allow retrieval of that book's info from the db, as the page doesn't have the full title.

I'm doing this in a loop like this:

While Not objRS.EOF
strList = strList + "<li><a href='MyBooks.asp?title=" & _
objRS("Title") & "'>" & objRS("Title") & "</a></li>" & vbCrLf

strList is used to contain the entire string of HTML, which is then displayed on the page, resulting in a list of books with clickable titles.

The titles appear on the page just fine, single quotes and all. But if I roll the mouse over a book's title with a single quote in it, I see the URL for that info page; it contains the MyBooks.asp, the question mark, and the querystring up to where the single quote would be.

After all that, my question is: how can I get the single quotes to appear in the link URL? I've taken a look in the ASP forum for similar topics, but from what I've seen, it's mostly just submitting info to the database or just displaying the info - I didn't really see anything about links.

I'm using classic ASP and Access 2000, if it makes a difference.


Roy Sinclair
04-30-2004, 08:22 PM
While Not objRS.EOF
strList = strList + "<li><a href='MyBooks.asp?title=" & _
Server.HtmlEncode(objRS("Title")) & "'>" & objRS("Title") & "</a></li>" & vbCrLf

Grant Palin
04-30-2004, 09:10 PM
Okay, I tried that. After I saved and refreshed the page, I clicked one of the troublesome links - no good. The status bar (on mouseover) only showed the querystring up to the single quote. I looked in the html source, and saw that the whole title is in the actual link, including the single quote!

Now that's odd. I wonder if there's some difference between printing the single quote and using it as a link?

Roy Sinclair
04-30-2004, 10:24 PM
Evidently Server.HtmlEncode isn't as complete as it should be but try this:

While Not objRS.EOF
strList = strList + "<li><a href=""MyBooks.asp?title=" & _
Server.HtmlEncode(objRS("Title")) & """>" & objRS("Title") & "</a></li>" & vbCrLf

Server.HtmlEncode is definitely supposed to catch the double quote so this should work even if the title contains a double quote mark. (Doubling up the double-quotes is the easy way to embed a double quote in VBScript).

Grant Palin
04-30-2004, 10:57 PM
Right, that helped. Now, when I roll over that link, the whole querystring is there. I click on the link, and I go to another page. However, it's an error page. The SQL statement that I use to retrieve the book's info chokes on the single quote inside the title string. What could I do about that?

EDIT: The query I use to get the book's info after clicking the link is:

"SELECT * FROM books WHERE title = '" & strTitle & "'"

(note the single quotes!)

Yet, when I get to the error page, the error message reads like:
Syntax error (missing operator) in query expression 'title = 'JavaScript: A Beginner's Guide''

(again, note the single quotes; I think the single quotes in this case are just to enlcose the problem code)

Roy Sinclair
04-30-2004, 11:02 PM
Duh! hand slaps forehead

Use Server.URLEncode instead of Server.HtmlEncode. Sometimes I get so fixated on the first thing that comes to mind that I miss the right answer.

Grant Palin
04-30-2004, 11:18 PM
I've tried that now...The spaces in the querystrings are replaced with character identities, it looks like. And the single quotes have been replaced by entities too.

The links that were working before still work. However, the problem link is still a problem...Grrr!:mad:

Maybe Access doesn't know how to, like, interpret that single quote...Would that single quote need to be converted to some entity on the info page that Access would understand?

Grant Palin
05-02-2004, 04:54 AM
You know what? I found out that if I replaced each single quote inside the title with TWO single quotes, then stuck that title in the database query string, it would work!

I suppose it must be something similar to eascaping one double quote with another one...Weird.

Is there any other way of getting the query to work, other than using two single quotes in the query?

05-02-2004, 02:30 PM
i dont really know what ur asking but, if you want querystrings to include "s you can escape quotes and apostrophes if you do "" instead, if you want it to include 's you do '' instead, like:
response.redirect "somepage.asp?qs=bob''s ""weird"" tutorial"
will send you to
and you can take this title into the SQL where it will read it as
somepage.asp?qs=bob's "weird" tutorial (im not sure about the "s but if they dont work make sure the querystring uses double "s too ("")

spaces change to %20 in the url but are " " in the SQL

this probably wont help you since i dont really know what you are asking but if it is what you want then use it :p

however, i would just send the unique id of the book (ALL of my relational database tables have an id autonumber as the primary key so they have unique identifiers that cannot be modified into duplicate data (stay unique)) then you just do
<a href="somepage.asp?BookID=42">...</a>
and in the SQL do
"SELECT * FROM tablename WHERE ID = " & Request.QueryString("BookID")

EDIT: also, i always just use "s in html, like ..a href=".. not ..a href='.. or ..a href=..

if you have problems with things like single quotes, get the asp to write out what its putting in the sql or whatever and view the generated html (view source or whatever your borowser does that lets you see the html)

05-02-2004, 03:14 PM
You can replace a single quote with two single quote, like:
strTitle = Replace(strTitle, "'", "''")
strQuery = "SELECT * FROM books WHERE title = '" & strTitle & "'"

Try it, hope it helps!

Grant Palin
05-03-2004, 02:44 AM
Thank you, ghell. Like I mentioned in my last post, I had figured out how to handle the single and double quotes in the book title when running a query.

Thank you also for the suggestion about using the unique ID number. Access created that for me automatically, but I never paid attention to it. It would certainly make querying easier!

Thank you too, zzg. That very bit of code was what I came upw ith to solve the quotes problem. I think I'll go with ghell's suggestion about using the unique ID - much simpler.

Thanks everyone! :thumbsup:

05-19-2004, 12:06 AM
This is ages ago now, but I just came across it. The solution is to use Server.URLEncode, not Server.HTMLEncode... that's all folks!

;) :D

05-19-2004, 03:36 AM
Roy Sinclair already corrected that mistake.

Grant Palin, you could have solved your problem (single quotes in SQL) earlier had you read this sticky (http://www.codingforums.com/showthread.php?t=9843).

Grant Palin
05-19-2004, 04:07 AM
I know...been there, done that. I actually resolved my intial problem by not using the book's title in he querystring - I just used the book's unique ID number. Much much easier, and much much less hassle. Thanks though.

05-19-2004, 08:00 PM
Roy Sinclair already corrected that mistake.Ah, so he did. Damn! :rolleyes: