Re: escaping all 'weird' chars in SQL statements

A copy of this was sent to Otis Gospodnetic <otis_at_my-deja.com> (if that email address didn't require changing) On Mon, 27 Mar 2000 18:21:37 GMT, you wrote:

>Hello,
>I would think my question is a FAQ, but I can't find answers to it on
>deja.com nor on technet.oracle.com.
>I'm wondering if there is a way to escape _all_ characters 'weird' SQL
>characters, like %, &, _, ', ;, etc. before executing an SQL statement?
>This is something I would like to do from my java code, so I'm not
>looking for sqlplus solution. I would also love to be able to do
>something like this:
>sql = "SELECT * FROM my_table WHERE col1='3-4' col2='How?; Here!' ";
>sql = sql.escapeAllBadChars(sql);
>Is this possible?
>Thanks,
>Otis

There are *no* bad or weird characters in SQL that need escaping.

In SQLPlus there is a macro facility that will look for & by default and do a string replacement. This is disabled in sqlplus by "set define off" or "set scan off".

In JDBC or any other 3gl interface -- there are no "bad" character to escape. Your query above would select all column from the table MY_TABLE where col1 is equal to the string

3-4

and col2 is equal to the string

How?; Here!

It would be *better* if you used bind variables (so the query was select * from my_table where col1 = :b1 and col2 = :b2)

It is better for a number of reasons:

o its better on the shared sql area. better use of resources on the server o it's easier to code with. Say you wanted to search for the string:

How's this

You would have to hard code:

select * from t where c = 'How''s this'

(note 2 quotes = 1 quote in a character string constant).

The only concept of "escape" characters is in a SQL Like clause:

select * from t where c like '%\%%' escape '\';

would find all strings that contain a % (the \% with escape '\' means don't treat % as a wildcard).

>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 13:09:10 CST