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!

Try two ticks in front of the tick '''

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

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.

We take exactly what they send to us...no data validation done on the app side....

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

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

Suggest your developers to read this: http://www.nextgenss.com/papers/adva..._injection.pdf - they may change their minds regarding validation :-)

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

That's "two ticks", not "three". So, your sample string, "O'Brien" should arrive to DB2 as