In article <d2cvdssll9027tfvssur9r4drafari948h_at_4ax.com>,
tkyte_at_us.oracle.com wrote:
> 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".
This is what I'm referring to (maybe I'm using wrong terms to describe
this problem):
I executed this SQL statement with executeUpdate() method (java):
INSERT INTO my_table(my_text) VALUES ('this chokes on ' (single
quote ).')
Mon Mar 27 16:00:43 EST 2000: Caught Exception
java.sql.SQLException: ORA-01756: quoted string not properly terminated
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch
(OCIDBAccess.java)
at oracle.jdbc.driver.OracleStatement.doExecuteOther
(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecuteWithBatch
(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecute
(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout
(OracleStatement.java)
at oracle.jdbc.driver.OracleStatement.executeUpdate
(OracleStatement.java)
...
...
> 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).
Maybe that is my problem (not using bind variables).
I'm currently using variables passed to a java method to create a String
(Buffer) that forms an SQL statements, and then executing it with
stmt.executeQuery() or stmt.executeUpdate().
Bad, ha?
I'm all ears! :)
How would I use bind vars to replace this simple statement?
String sql = "SELECT user_id FROM user_auth WHERE username='" +
username + "'";
rs = executeNonModQuery(sql)
...
...
Thank you!
Otis
> 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.
Received on Mon Mar 27 2000 - 15:09:20 CST
