IIS - apostrophe problem

Asked By Rich

26-Oct-09 03:53 PM

Hi,

I am trying to make a dynamic dropdown list box that contains value
pulled from an Access database.  The code is working properly except
when one of the values contains an apostrophe for example O'Leary.
When O'Leary shows up I get:
The system says there is an Extra quote character found or quote
character missing:
How can I fix it?

Thanks,

TR/html4/strict.dtd">
html, body {
height: 100%;
min-height: 100%;
}
body{
border:0;
margin:0px;
background-color:white;
color:black;
text-align:center;
}
select {
width:200px;
}
p {
width:200px;
}

Dim objconn,objRS,strSQL1

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver
(*.mdb);DBQ=" & Server.MapPath("db.mdb")
objconn.Open

Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL1 = "SELECT name FROM Table1 ORDER BY name ASC"
objRS.Open strSQL1, objconn
Response.Write "<p>Search by Name: "
Response.Write "<option value='' selected>Name</
option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objrs("Name") &"'>"& objRs("Name")
&"</option>"
objRS.MoveNext
Loop
Response.Write "</p>"
objRs.Close
objconn.Close

%>

06-Oct-09 06:39 PM

Escape it using the Replace function:

Response.Write "<option value='" & Replace(objrs("Name"),"'","\'") ...
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
do not check it very often. If you must reply off-line, then remove the

07-Oct-09 03:33 AM

Bob Barrows wrote on 07 okt 2009 in microsoft.public.inetserver.asp.db:


I replace all apostrophes in db text fields with `, the "back quote",
only to reverse that in actual html text.

It has the added bonusses that char count is not disturbed and that
parameter injection can be more easily shielded.

However in simple html, why not do:


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

26-Oct-09 03:53 PM

Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'Replace'

Response.Write "<option value=3D'" & Replace(objrs("Name"),"'","\'") &
Thanks,

26-Oct-09 03:53 PM

.

The html is dynamically generated from the data in the access
database.  I cannot change the data so the apostrophe is a back quote.

Thanks,

07-Oct-09 11:01 AM

Use double quotes for your attribute values (double them up in strings to
print them), and HTML encode your values.

Response.Write "<option value=""" & Server.HTMLEncode(objrs("Name")) &""">"
& Server.HTMLEncode(objRs("Name")) &"</option>"

If you really must use a single quote (apostrophe) for your attributes, then
replace the apostrpophes in your values with '

Response.Write "<option value='" &
Replace(Server.HTMLEncode(objrs("Name")),"'","'") &"'>" &
Server.HTMLEncode(objRs("Name")) &"</option>"


You should never just write data from anywhere, database or otherwise, into
HTML unless you are sure it is already been encoded correctly, as you leave
yourself option to XSS vulnerabilities if your variables/data is
compromised.

--
Dan

07-Oct-09 11:06 AM

That means the value of the Name column in your recordset is a Null value,
in which case the code I suggested in my other reply will not work either. You
would need to do something like this:


If IsNull(objrc("Name")) Then
sName = ""
Else
sName = Replace(Server.HTMLEncode(sName),"'","'")
End If

Response.Write "<option value='" & sName & "'>" & sName &"</option>"& VbCrLf


Depending on whether you use double quotes or single quotes to encapsulate
attribute values, replace them with ' or "  (Server.HTMLEncode
replaces " with " so already do this for you if you use double quotes
for your attributes).

--
Dan

07-Oct-09 03:04 PM

Rich wrote on 07 okt 2009 in microsoft.public.inetserver.asp.db:


[please do not quote signatures on usenet]


I would not accept that on my websites, as I am the webmaster there.

I do not accept any apostrophs to be in my database records to begin with.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

26-Oct-09 03:53 PM

Cheers for using a strict doctype!



You could always put this into an include file.  That way you do not have
to rewrite it all the time.  I do <!-- include file = "conn_inc.asp" -->


I would probably put this into a getrows array.  Open the connection,
put the results in an array, and close your connection.  This can
significantly improve speed, and is less work for the server.

So I would do:

if not objrs.eof then
rsarr = objrs.getrows()
else
'tell the client it is an empty record set
end if
objrs.close
set objrs = nothing

I would put your queries before you output any HTML.  Makes debugging
easier, and you do not have to wait for the browser.

One of the good things about ASP is that it is easy to drop in and out
of HTML.  it is easier to debug as well.



Where is your form element? Is this a post operation or a get operation?
Where is supposed to process?  If you have no form element, the brower
MIGHT send the request to the same page, but it might not.  Best to be
safe and use the form element with appropriate attributes.

("script_name")%>">

it is also better to double quote all your attributes.  Although HTML
does not require you to quote attributes, it is a good practice.  This
is especially true if you ever need to use XHTML, where quoting of
attributes is mandatory.



And I would rewrite this as:

08-Oct-09 07:09 AM

Handling apostrophes in data is trivial. So how do you deal with text that
uses them? Do you really never have any data that requires it?

--
Dan

08-Oct-09 07:48 AM

Dan wrote on 08 okt 2009 in microsoft.public.inetserver.asp.db:


You may find so indeed, so I handle this "trivial" problem.

Others might not match your experienced triviality level.


I explained that that above. Please read the quoted.


Never yet. And I doubt I will ever.


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

26-Oct-09 03:53 PM

e
-->
=A0

24-Nov-09 01:44 AM

Glad to know everything worked out okay.  Happy Thanksgiving if you are in
the US.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share