Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Stripping Out Quotes For Database Storage

Reply
Thread Tools

Stripping Out Quotes For Database Storage

 
 
Colin Steadman
Guest
Posts: n/a
 
      11-30-2004
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
 
Reply With Quote
 
 
 
 
Patrice
Guest
Posts: n/a
 
      11-30-2004
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" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed) om...
> 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



 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      11-30-2004
> 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



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-30-2004
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.


 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      11-30-2004
> 1. Use parameters instead of dynamic sql
> 2. Escape the " by doubling it


Or use ' as the delimiter.


 
Reply With Quote
 
sbreply@comcast.net
Guest
Posts: n/a
 
      11-30-2004
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...
 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      11-30-2004
> 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.)


 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      11-30-2004
> 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.


 
Reply With Quote
 
Dave Anderson
Guest
Posts: n/a
 
      11-30-2004
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.HTMLEncode().

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



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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      11-30-2004
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
regex to avoid escaped quotes or double quotes jOhn Ruby 1 01-29-2008 08:31 PM
PHP double quotes inside double quotes MSB Computer Support 11 10-21-2006 01:09 PM
Asp.NET Javascript string, want to pass '(single quotes' within '(single quotes) Chris ASP .Net 1 03-24-2006 09:03 PM
Quotes/Double Quotes in Image Control Chris White ASP .Net 1 09-22-2004 06:22 AM
Multiline quotes - escaping quotes - et al Lawrence Tierney Java 3 12-24-2003 05:12 PM



Advertisments