Stripping Out Quotes For Database Storage

[Colin Steadman]

My ASP page allows user to enter comments into a form. To avoid
errors I'm having to strip out double quotes before saving to the
database. Is there anyway to encode these so that I can store them
instead, in the way was an URLEncode works?

TIA,

Col

 

[Patrice]

Usually you just have to double them to keep them in the db...

If you use parameters for your queries, you' don't even have to double them.

Patrice

 

[Aaron [SQL Server MVP]]

My ASP page allows user to enter comments into a form. To avoid

errors I'm having to strip out double quotes before saving to the
database.

What errors do you get with double quotes? This shouldn't happen unless you
have some weird syntax going on. Can you show an example that fails, and
the error message you get?

The only problem character when building dynamic SQL statements in ASP
should be the ' character.
http://www.aspfaq.com/2035

--
http://www.aspfaq.com/
(Reverse address to reply.)




Is there anyway to encode these so that I can store them

 

[Bob Barrows [MVP]]

What errors do you get with double quotes? This shouldn't happen
unless you have some weird syntax going on. Can you show an example
that fails, and the error message you get?

The only problem character when building dynamic SQL statements in ASP
should be the ' character.
http://www.aspfaq.com/2035

Don't forget, if he's using Access, Access allows you to use " for the data
delimiter instead of '. If that's what he's doing, then an embedded " will
cause this problem, which, of course, has the same solutions:
1. Use parameters instead of dynamic sql
2. Escape the " by doubling it

Bob Barrows

 

[Aaron [SQL Server MVP]]

1. Use parameters instead of dynamic sql

2. Escape the " by doubling it

Or use ' as the delimiter.

 

[sbreply]

or you could use the "replace()" function to replace the quotes with some type of unique string sequence. not the most elegant solution but it works

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

 

[Aaron [SQL Server MVP]]

or you could use the "replace()" function to replace the quotes with some
type of unique string sequence. not the most elegant solution but it works

I don't recommend this. Now your ' is replaced by "some type of unique
string sequence" in the database, which causes at least three problems:

(a) users looking directly at the data in the database will be confused by a
name like O~^^^~Malley;

(b) you need to trap cases where the "unique" string sequence might actually
need to be used in the data; and,

(c) you need to build a reverse function, so you have to handle replacing on
both sides. Not only does this duplicate the work required to store and
retrieve the data, but also, since usually there are more consumers of data,
you may have to duplicate the reverse function in multiple
locations/applications.

I strongly recommend storing the data as it is intended, which means
escaping "problem" characters, as opposed to encoding/obfuscating them.

 

[Aaron [SQL Server MVP]]

Unless you use ADODB.Command, which eliminates the problem altogether...

Yes, and raises some of its own. Of course, we've hashed this over and over
again ad nauseum.

 

[Dave Anderson]

What errors do you get with double quotes? This shouldn't happen
unless you have some weird syntax going on. Can you show an example
that fails, and the error message you get?

There are actually two problems with quotes: Getting them into the DB
(usually a single-quote problem), and getting them into the FORM element
(usually a double-quote problem). The first can be resolved by passing the
value through a parameter to a stored procedure, and the second by use of
Server.HTMLEncode().

1. cn.Execute("mySP '" & Replace(comment,"'","''") & "'")
2. <INPUT
VALUE="<%=Server.HTMLEncode(RS.Fields("Comment").Value)%>" ...>

The only problem character when building dynamic SQL statements in
ASP should be the ' character.
http://www.aspfaq.com/2035

Unless you use ADODB.Command, which eliminates the problem altogether...




--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Bob Barrows [MVP]]

Unless you use ADODB.Command, which eliminates the problem
altogether...

Or the "procedure-as-connection-method" technique.

Bob Barrows

 

[Dave Anderson]

Yes, and raises some of its own.

I note that you call it "troublesome", denounce the "hassle and rigorous
code required by" it, and throw a red herring with "ADOVBS.INC = bad" (ever
heard of a TYPELIB?), but I don't see where it raises problems.

We use it to the exclusion of CN.Execute() when doing anything other than
SELECT. True, the code is more rigorous, but it is also explicitly clear
what data type is expected for each parameter, and I don't consider it any
more "troublesome" than declaring variables or indenting. Using them is
certainly less trouble than anticipating and accommodating all points of
failure for variable cleanup, but I don't see you telling anyone to avoid
cleaning house...

http://aspfaq.com/show.asp?id=2400
http://aspfaq.com/show.asp?id=2330
http://aspfaq.com/show.asp?id=2191

Oh - and I don't understand how the ADODB.Command object creates the "out of
range" error:
http://aspfaq.com/show.asp?id=2406

Of course, we've hashed this over and over again ad nauseum.

Like this?
http://groups.google.com/groups?hl=en&lr=&[email protected]



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

and throw a red herring with "ADOVBS.INC = bad" (ever

heard of a TYPELIB?),

Red herring indeed. Almost every single code sample I see using
ADODB.Command also uses ADOVBS.Inc.

And no, I have never heard of a typelib, what's that? This article must
have just manifested itself:
http://www.aspfaq.com/2112

what data type is expected for each parameter, and I don't consider it any
more "troublesome" than declaring variables or indenting.

Really? So you don't have any problems with the confusion over adDate,
adDBDate, adDBTimestamp, etc.? And you can explain to a newbie the
differences between adChar, adWChar, etc. etc.?

Oh - and I don't understand how the ADODB.Command object creates the "out of
range" error:
http://aspfaq.com/show.asp?id=2406

One example would be specifying an INT parameter and then mistakenly passing
a BIGINT. (I think with more recent versions of MDAC, the error is the 'out
of acceptable range', but haven't cared enough to test it out.) Most of the
research I did for that article came from Google, you could do the same.

Like this?

http://groups.google.com/groups?hl=en&lr=&[email protected]

Okay Dave, you've convinced me. The command object is perfect and has
absolutely no weaknesses. All other techniques are unacceptable and should
never be uttered in public. I'm off to rewrite the entire FAQ, because
obviously it can't be possible that using ADODB.Command has pros and cons,
and not using ADODB.Command has pros and cons.

A

 

[Dave Anderson]

And no, I have never heard of a typelib, what's that? This article
must have just manifested itself:
http://www.aspfaq.com/2112

Sarcasm met with sarcasm. Touché.

Really? So you don't have any problems with the confusion over
adDate, adDBDate, adDBTimestamp, etc.?
No.



And you can explain to a newbie the differences between adChar,
adWChar, etc. etc.?

I believe that if the data matter at all, then even newbies -- perhaps
especially newbies -- must know their types, and therefore must ultimately
understand the difference between adChar and adWChar. The data type was
chosen for a reason, was it not?

Do you really want to put your business in the hands of someone who DOES NOT
understand the nature of the data in his hands? Remember -- I am talking
about *updating* the database, not merely reading it.

One example would be specifying an INT parameter and then
mistakenly passing a BIGINT.

If the parameter is INT, then passing a BIGINT value is a programming error,
not an error introduced by the Command object.

Okay Dave, you've convinced me. The command object is perfect
and has absolutely no weaknesses...

Fair enough - hyperbole met with hyperbole.

In fairness, I never said the Command object was without weaknesses. Sure,
using it is more work than not. But so are a lot of things that we do
anyway, like access control or source control, stored procedures instead of
SQL statements, or specifying Request.QueryString(xxx).Item instead of
Request(xxx).

I offered it as an alternative because it is a fundamentally sound
alternative, not because it is the only way to achieve the task well.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

Sarcasm met with sarcasm. Touché.

I believe that if the data matter at all, then even newbies -- perhaps
especially newbies -- must know their types, and therefore must ultimately
understand the difference between adChar and adWChar.

But if I'm passing a name like 'Jones', why does it matter which one it is?
I mean, I agree that I should know whether it is unicode or not, but having
to match the datatype and cross-reference the ad constant is exactly the
"hassle" I'm usually complaining about. The syntax for setting up those
parameters isn't exactly a cakewalk, either.

You see it a different way. Great. My opinion is still valid.

Do you really want to put your business in the hands of someone who DOES NOT
understand the nature of the data in his hands? Remember -- I am talking
about *updating* the database, not merely reading it.

So if you have a stored procedure that updates the database, you advocate a
command object. But if it's a stored procedure that returns data, you
don't? Note that both types of stored procedures can take parameters.

If the parameter is INT, then passing a BIGINT value is a programming error,
not an error introduced by the Command object.

Maybe I used a bad example. There are plenty of scenarios in this error
genre that will fail because the explicitly declared parameter doesn't
perfectly match the stored procedure definition, however if you pas it

It is also much easier to debug

response.write sql

Than to try and figure out the SQL statement created by 80 discombobulated
lines of ADODB.Command. Again, IMHO.

In many shops, it's considered a benefit to separate the database logic from
the application logic. You're doing the reverse if you tightly couple the
datatypes in the interface to the stored procedure to the hard-coded
parameter declarations in the ASP code. Again, IMHO.

If I want to change the parameter of a stored procedure from datetime to
smalldatetime (or vice versa), I shouldn't have to tell all my ASP
developers to update their ADODB.Command code because now it might bomb. If
they pass the date value implicitly, this change doesn't affect them (but
they still can't pass some bogus date into the stored procedure).

In fairness, I never said the Command object was without weaknesses.

You took off when I implied that the command object had weaknesses. Here we
are.

I offered it as an alternative because it is a fundamentally sound
alternative, not because it is the only way to achieve the task well.

And I offered replacing ' with '' because is also a way to achieve the task
at hand.

A

 

[Dave Anderson]

But if I'm passing a name like 'Jones', why does it matter
which one it is?

First of all, you don't know in advance that every name will be as simple as
"Jones". More to the point, if you *do* know that, then there is no need to
design the database to accommodate Unicode. Conversely, if the design
anticipates the need, then so should your script. Enough said.

So if you have a stored procedure that updates the database,
you advocate a command object. But if it's a stored procedure
that returns data, you don't?

Our shop standard is that updating the DB requires the use of a Command
object, yes. We do not require it for merely reading the DB (though we
ALWAYS use stored procedures). Why the difference? Because one is a time of
risk to the data and the other is not. We *want* the developer to take the
time to do it right when updating. We *want* to generate an error
immediately if parameters are passed incorrectly. Mix up the order of Phone
and Fax while reading the DB? Yes, that is an error, but at least you
haven't corrupted the data.

In practice, this means we often use strings to represent lists of
parameters when reading, just as you seem to prefer for every purpose.

It is also much easier to debug

response.write sql

Than to try and figure out the SQL statement created by 80
discombobulated lines of ADODB.Command. Again, IMHO.

I totally disagree. 80 lines of ADODB.Command corresponds to roughly 75
parameters in my book**. The error returned by the Command object explicitly
names the parameter, which is trivial to find. Contrast that to finding that
parameter in a string concatenation of 75 parameters. To each his own.

**Each parameter is declared and assigned in one line of code, similar to
this:
CMD.Parameters.Append(CMD.CreateParameter("@LastName",adVarChar,adParamInput
,50,Request.Form("LastName").Item||null))

In many shops, it's considered a benefit to separate the
database logic from the application logic. You're doing the
reverse if you tightly couple the datatypes in the interface
to the stored procedure to the hard-coded parameter
declarations in the ASP code. Again, IMHO.

No go. If you want to unbind the data and application logic, make the
parameters all NVARCHAR and cast/convert them in the procedure. But if you
write the procedure to expect a certain type, then you have ALREADY mingled
the logic.

If I want to change the parameter of a stored procedure
from datetime to smalldatetime (or vice versa), I shouldn't
have to tell all my ASP developers to update their
ADODB.Command code because now it might bomb.

Surprisingly enough, this will not affect them (I have actually done this).
SQL Server coerces parametrized data pretty flexibly. But again, you need
not change the SP parameters to change the data type behind the scenes.
Nevertheless, I maintain that if your assertion were true, you SHOULD have
to tell your developers when you change

***********
*** any ***
***********

interface you provide them.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

immediately if parameters are passed incorrectly. Mix up the order of
Phone

and Fax while reading the DB? Yes, that is an error, but at least you
haven't corrupted the data.

What are you talking about? How does using the command object prevent you
from assigning the wrong variable to that parameter?

Anyway, as I tried to do earlier, I'm going to bow out. There is no way in
hell either of us is going to convince the other that their approach is
perfect, because neither approach is perfect. Period.

 

[Colin Steadman]

-----Original Message-----

Or use ' as the delimiter.

Firstly my apoligies, I made a mistake in my original post
as its single quotes I've been having problems with, not
double quotes as suggested.

The database I'm using is Access. I've read your article
on ASPFAQ about the perils of using Access. But I needed
to get this site up quickly, and because its an internal
site with very few users Access was a suitable choice.

This is the ASP script I'm using to update the database:

strComments = Request.Form("comments")

sql = "UPDATE vehicles SET vehicles.comments = '" & _
strComments & _
"' WHERE vehicles.key = " & Session("key")

Set conn = Server.CreateObject("ADODB.Connection")
conn.open Session("connectionString")
conn.execute sql, , &H00000080


If type in this quote (for example):

Two canibals are eating a clown, one turns to the other
and says 'does this taste funny to you?'

My ASP code generates this SQL:

UPDATE vehicles SET vehicles.comments = 'Two canibals
are eating a clown, one turns to the other and
says 'does this taste funny to you?'' WHERE vehicles.key
= 1803

Which causes this error when executed:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query
expression ''Two canibals are eating a clown, one turns
to the other and says 'does this taste funny to you?'''.

/ovms2/comments_update_execute.asp, line 36

Which is why I've been removing the single quotes with a
regular expression (I wasn't ware of the replace
command). The http://www.aspfaq.com/show.asp?id=2035
method you posted doesn't seem to work either. I've
modified the code to look like this:

sql = replace(sql,"'","''")

And now get this SQL returned:

UPDATE vehicles SET vehicles.comments = ''Two canibals
are eating a clown, one turns to the other and
says ''does this taste funny to you''?'' WHERE
vehicles.key = 1803

And this error when it executes:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query
expression '''Two canibals are eating a clown'.

/ovms2/comments_update_execute.asp, line 37

Shouldn't this have worked? I really can see why its
failing.

TA,

Colin

 

[Bob Barrows [MVP]]

sql = replace(sql,"'","''")



And now get this SQL returned:

UPDATE vehicles SET vehicles.comments = ''Two canibals
are eating a clown, one turns to the other and
says ''does this taste funny to you''?'' WHERE
vehicles.key = 1803

Here is the sql you need to generate (which you can verify by testing with
the Access Query Builder):

UPDATE vehicles SET vehicles.comments = 'Two canibals
are eating a clown, one turns to the other and
says ''does this taste funny to you''?' WHERE
vehicles.key = 1803

Those are all single quotes (apostrophes) BTW.

You see? The string on the right side of the = sign is delimited by a single
quote at each end. The embedded apostrophes inside the string have to be
escaped by doubling them up.

So, you need to accomplish this by using the Replace function, not on the
entire sql statement, but only on the data that is being concatenated into
the resulting sql statement, i.e., the variable containing

data ="'Two canibals are eating a clown, one turns to the other " & _
"and says 'does this taste funny to you'?"

data = replace(data,"'","''")

sql="UPDATE vehicles SET vehicles.comments = '" & _
data & "' WHERE vehicles.key = 1803"

Response.Write sql

Again, this whole silly business can be avoided by using parameters. See:

http://groups.google.com/groups?hl=...=1&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://www.google.com/[email protected]&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

Bob Barrows

 

[Aaron [SQL Server MVP]]

command). The http://www.aspfaq.com/show.asp?id=2035

method you posted doesn't seem to work either. I've
modified the code to look like this:

sql = replace(sql,"'","''")

You should be using this replace against the data, not the entire SQL
command!

So do it here, when building sql:

strComments = Request.Form("comments")

sql = "UPDATE vehicles SET vehicles.comments = '" & _
replace(strComments, "'", "''") & _
"' WHERE vehicles.key = " & Session("key")