Stripping Out Quotes For Database Storage

Discussion in 'ASP General' started by Colin Steadman, Nov 30, 2004.

  1. 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
    Colin Steadman, Nov 30, 2004
    #1
    1. Advertising

  2. Colin Steadman

    Patrice Guest

    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" <> a écrit dans le message de
    news:...
    > 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, Nov 30, 2004
    #2
    1. Advertising

  3. > 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
    Aaron [SQL Server MVP], Nov 30, 2004
    #3
  4. 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.
    Bob Barrows [MVP], Nov 30, 2004
    #4
  5. > 1. Use parameters instead of dynamic sql
    > 2. Escape the " by doubling it


    Or use ' as the delimiter.
    Aaron [SQL Server MVP], Nov 30, 2004
    #5
  6. Colin Steadman

    Guest

    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...
    , Nov 30, 2004
    #6
  7. > 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.)
    Aaron [SQL Server MVP], Nov 30, 2004
    #7
  8. > 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.
    Aaron [SQL Server MVP], Nov 30, 2004
    #8
  9. 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").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.
    Dave Anderson, Nov 30, 2004
    #9
  10. 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.
    Bob Barrows [MVP], Nov 30, 2004
    #10
  11. Aaron [SQL Server MVP] wrote:
    >> Unless you use ADODB.Command, which eliminates the problem
    >> altogether...

    >
    > 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=&selm=



    --
    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.
    Dave Anderson, Nov 30, 2004
    #11
  12. > 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=&selm=

    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
    Aaron [SQL Server MVP], Nov 30, 2004
    #12
  13. Aaron [SQL Server MVP] wrote:
    > 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.
    Dave Anderson, Nov 30, 2004
    #13
  14. > Sarcasm met with sarcasm. Touché.

    :)

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


    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.

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


    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
    Aaron [SQL Server MVP], Nov 30, 2004
    #14
  15. Aaron [SQL Server MVP] wrote:
    >> ...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?


    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.
    Dave Anderson, Dec 1, 2004
    #15
  16. > 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.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Dec 1, 2004
    #16

  17. >-----Original Message-----
    >> 1. Use parameters instead of dynamic sql
    >> 2. Escape the " by doubling it

    >
    >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
    Colin Steadman, Dec 1, 2004
    #17
  18. Colin Steadman wrote:
    >
    > 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&selm=

    http://groups.google.com/groups?hl=...=1&selm=ukS$6S$

    http://www.google.com/groups?selm=&oe=UTF-8&output=gplain

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Dec 1, 2004
    #18
  19. > 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")

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Dec 1, 2004
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris White

    Quotes/Double Quotes in Image Control

    Chris White, Sep 22, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    4,837
    Hermit Dave
    Sep 22, 2004
  2. Chris
    Replies:
    1
    Views:
    13,610
    Oisin
    Mar 24, 2006
  3. Lawrence Tierney

    Multiline quotes - escaping quotes - et al

    Lawrence Tierney, Dec 24, 2003, in forum: Java
    Replies:
    3
    Views:
    4,483
    Andrew Thompson
    Dec 24, 2003
  4. jOhn
    Replies:
    1
    Views:
    213
    Phlip
    Jan 29, 2008
  5. Richard Sandoval
    Replies:
    5
    Views:
    195
    7stud --
    Apr 26, 2011
Loading...

Share This Page