| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL quoting question
A copy of this was sent to Robert Smith <rob.smith_at_moh.hnet.bc.ca>
(if that email address didn't require changing)
On Tue, 28 Jul 1998 20:20:38 +0100, you wrote:
>I have a related problem.
>
>I'm trying to build a package for use with the WOW gateway, to allow the user to
>specify text for a VARCHAR2(2000) element. If he enters a single quote, my
>procedure busts a gut, falls flat on its back and kicks its legs in the air.
>
>I've tried using INSERT INTO yadda-yadda .... REPLACE(Fleep,'''','''''').... ,
>which works just fine for preserving apostrophes, as long as the user has the
>grace and foresight to enter two. If the inconsiderate louse just enters one, then
>more bust guts.
>
>I could do something in JavaScript to run through the string and double up the
>apostrophes before my PL/SQL gets its mitts on it, but that seems like a lot of
>trouble for something that ought to have been handled a million times so far, and
>have a built-in solution.
>
that problem is in WOW itself.
Wow will take a URL like: http://hostname/wow/procedure?x=How's+it+going
and build a really big string that says:
begin procedure( x=>'How's it going' ); end;
and execute it (wow is very primitive). I remember hitting this myself with wow about 3 years ago. Once upon a time ago I fixed wow to double quote all variables before putting it in the string (just rewrite the code) but I no longer have wow or the source modifications.
>So, the question in brief: "How do I handle unpredictable use of apostrophes in
>user input to a PL/SQL procedure?"
>
So, the quoting problem happens before the data ever gets into the pl/sql routine itself -- since wow doesn't use bind variables but hard codes all the values into the string it must quote them and it doesn't -- hence the failure.
If wow used bind variables -- this wouldn't be an issue at all (thats the answer to the question above, use bind variables! Its only in character string constants in a (PL/) SQL statement that cause this problem.
>Any assistance greatly appreciated.
>
>Thomas Kyte wrote:
>
>> >Okay, one quotes an apostrophe (') with adding another one (''),
>> >
>> > But how does one quote an ampersand (&), and underscore (_), and a
>> >percent sign (%). So far, doubling those or adding a preceding slash
>> >(\) doesn't seem to work.
>> >
>>
>> well, the & is a sqlplus'ism only. To make that be just another character in
>> sqlplus, simply issue:
>>
>> SQL> set scan off
>>
>> and & will be just like any other character.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 28 1998 - 18:33:35 CDT
 |
 |