| 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: escaping all 'weird' chars in SQL statements
Hi Otis
If only the ' > '' is a problem, here is a function to escape a String:
static String createString(String s) {
StringBuffer b=new StringBuffer().append('\'');
if(s!=null) {
for(int i=0,len=s.length();i<len;i++) {
char c=s.charAt(i);
if(c=='\'') {
b.append(c);
}
b.append(c);
}
Otis Gospodnetic schrieb:
> 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).
>
 |
 |