Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Entering a 'Tick' into a Field

    Got a message from a user about an error they are getting. They are trying to enter the name O'Brien into a text field that's defined as a VARCHAR(64). What's happening is that on the insert it's blowing up on the insert.

    I tried running a stored procedure - via stored procedure builder - and entered a value with an apostrophe in the text..worked fine. Of course when I tried to run the stored procedure via command line or a straight insert, it fails because of the tick(s).

    Is there an escape character or is there a setting as in SQL Server (quoted identifiers).

    Thanks!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Try two ticks in front of the tick '''

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    That will work fine for me and someone who workes with db's, but for an ordinary user, would be kind of bad design to put on the web page "please enter two apostrophe's before the actual apostrophe...

    a database setting would be the preferred method, if at all possible...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ansonee
    That will work fine for me and someone who workes with db's, but for an ordinary user, would be kind of bad design to put on the web page "please enter two apostrophe's before the actual apostrophe...
    You don't build your SQL direcly from raw data provided by web users, do you? To me it would look like even worse design...

    I would make the application responsible for data validation and, in the case of a single quote character, data translation before that data gets inserted into the database.

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    We take exactly what they send to us...no data validation done on the app side....

    Not my fault!!! I told them to...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    If it's web-based, you will want to scrub the incoming field anyway. There is probably a function in the language you are using to replace the ' with a '''. For example, if you are using Net.Data on z/os, the function is @DTW_rADDQUOTE

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ansonee
    We take exactly what they send to us...no data validation done on the app side....

    Not my fault!!! I told them to...
    Suggest your developers to read this: http://www.nextgenss.com/papers/adva..._injection.pdf - they may change their minds regarding validation :-)

  8. #8
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    This app is being written in HTML.

    So if I were to instruct the developers on how to resolve this, I would essentially tell them to check the fieeld and whenever you find a tick, replace that with three ticks?

    Am I oversimplifying?

    I'm talking to the developer responsible for this piece of the code and he says he just needs an escape character to toss in front of the troublesome tick....or something to that degree...
    Last edited by ansonee; 09-15-04 at 13:59.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ansonee
    So if I were to instruct the developers on how to resolve this, I would essentially tell them to check the fieeld and whenever you find a tick, replace that with three ticks?
    That's "two ticks", not "three". So, your sample string, "O'Brien" should arrive to DB2 as
    Code:
    'O''Brien'

Posting Permissions

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