oracle escape function

Question

Are there any function in oracle that escapes wrong characters in sql query? I have code that builds query from different string, and some of them may contain ' character, this breaks sql query.

Answer 1

As Yahia points out, you should always use bind variables rather than dynamically assembling SQL statements dynamically. That's the proper way to protect yourself from SQL injection attacks. Escaping a string provides a far lower level of protection.

That being said, assuming you're using Oracle 10.1 or later, you can use the q quoting syntax. Something like

  1  select q'[This is a string with an embedded ']' str
  2*   from dual
SQL> /

STR
-----------------------------------
This is a string with an embedded '

You can replace the [ and ] characters with a number of other characters depending on what characters might appear in the string

  1  select q'<This is a string with an embedded '>' str
  2*   from dual
SQL> /

STR
-----------------------------------
This is a string with an embedded '

SQL> ed
Wrote file afiedt.buf

  1  select q'{This is a string with an embedded '}' str
  2*   from dual
SQL> /

STR
-----------------------------------
This is a string with an embedded '