Oracle function

Question

Is there any function in oracle that avoids invalid characters in sql query? I have code that builds a query from another line, and some of them may contain the character ' , this breaks the sql query.

Answer 1

As Yahia points out, you should always use bind variables and not dynamically collect SQL queries dynamically. This is the right way to protect yourself from SQL injection attacks. Failure provides a much lower level of protection.

However, assuming you are using Oracle 10.1 or later, you can use q quoting syntax. Sort of

  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 characters [and] with the number of other characters, depending on what characters can be displayed 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 ' 

Answer 2

you should never create queries from strings - use parameters instead ... otherwise, there is always a chance that someone will find a way to inject some SQL.

Answer 3

Two single quotes escape a single quotation mark. So use '' instead of ' in your string.