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

--

"Colin Steadman" <co************ @gmail.com> a écrit dans le message de
news:52******** *************** ***@posting.goo gle.com...

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

[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 instead, in the way was an URLEncode works?

TIA,

Col

[Bob Barrows [MVP]]

Aaron [SQL Server MVP] wrote:

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

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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.

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

[Dave Anderson]

Aaron [SQL Server MVP] wrote:

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.HTMLEnco de().

1. cn.Execute("myS P '" & Replace(comment ,"'","''") & "'")
2. <INPUT
VALUE="<%=Serve r.HTMLEncode(RS .Fields("Commen t").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.

[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.

[Bob Barrows [MVP]]

Dave Anderson wrote:

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

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

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.