Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Location
    Chapel Hill
    Posts
    9

    Question Unanswered: Single quotes in DB2

    Is it just accepted that all you should not have single quotes (') in DB2? I have an applicaiton that creates select and insert statements from a user suplied surveys. SO,
    1) Does the app have to check to see if there are quotes and prevent their inclusion,
    2) do I have to compensate for the quote on each query
    3) or can I universally compensate for them?

    I appreciate any help that can be given.

    P. Alston

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Single quotes in DB2

    Originally posted by phalston
    Is it just accepted that all you should not have single quotes (') in DB2? I have an applicaiton that creates select and insert statements from a user suplied surveys. SO,
    1) Does the app have to check to see if there are quotes and prevent their inclusion,
    2) do I have to compensate for the quote on each query
    3) or can I universally compensate for them?

    To include single quotes in SQL string literals all you need to do is just double them. For example, if you want to store value "I don't know" in the ANSWER field your statement would look like

    Code:
    UPDATE TABLE1 SET ANSWER='I don''t know' WHERE...
    If you deal with variables in your application you shouldn't worry about that at all:

    Code:
    String s = "I don't know";
    myPreparedStatement.setString(1, s);
    Hope this helps.

    Nick

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1) Does the app have to check to see if there are quotes and prevent their inclusion,

    yes

    2) do I have to compensate for the quote on each query

    depends on what you do with the SELECT results, but for INSERTS and UPDATES, yes

    3) or can I universally compensate for them?

    there are no nice and easy solutions, i'm afraid

    some people think that by changing the single quote to, say, a hexadecimal encoding such as &#39 (for html applications), they avoid the problem, but this introduces other problems

    use two single quotes in a row whenever specifying a literal that contains a quote

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •