Re: Problem with single quote character

Brian Peasland wrote:
> Chris L. wrote:
> > Hi,
> >
> > The problem: Building dynamic SQL sometimes results in statements like
> > this:
> >
> > execute immediate
> > 'update owner.customers set city=''Unknown'', country=''Cote D'Ivoire''
> > where cust_id=37'
> > ;
> >
> > This is a problem, because the quote in [Cote D'Ivoire] renders the sql
> > statement invalid.
> >
> > Easily fixed, duplicating the quote as below:
> >
> > 'update owner.customers set city=''Unknown'', country=''Cote
> > D''Ivoire'' where cust_id=37'
> >
> > My question: What other characters could "break" the sql? Maybe [:],
> > [\], [&], [%]? Is there a complete list somewhere?
> >
> > Furthermore, I remember seeing on documentation an option to make other
> > characters play the role of the single quote but no matter what I
> > search terms I use, I can't seem to find it.
> >
> > Oracle 9i 9.2.0.4.0
> >
> > Thanks all in advance.
> >
>
> My first opinion is that coding UPDATE statements dynamically is often
> bad coding. If you just code the UPDATE statement in a PL/SQL block and
> put the values in variables, then you won't run into this issue.
>
> If you insist on using dynamic SQL in this manner, then you'll have to
> pre-parse the values. So if you have "Cote D'Ivoire" in a variable, then
> run it through REPLACE first to change the single quote to two single
> quotes.
>
> About the only other character I can think of off the top of my head
> that would interfere in your EXEC IMMED statement is the semicolon,
> which is a statement terminator.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

I think underscore can have different meanings - such as wildcard single character, so you have to be careful with that.

Dean Received on Fri Oct 06 2006 - 09:35:05 CDT