1

Suppose I have a column value as aaa'gh it will throw error in oracle saying sql command not properly ended.

My question is if I don't know how many ' are in my value, how can I escape them safely.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
chikkada
  • 75
  • 2
  • 7
  • escape in the sense what you are going to do. – Exhausted Dec 09 '14 at 07:09
  • Please search on google for "Escape character in Oracle" . www.dba-oracle.com/tips_oracle_escape_characters.htm – AK47 Dec 09 '14 at 07:09
  • @Exhausted I have a variable which i'm fetching from the database.When that variable has `'` in it it is creating issues. Hi AK47 i know how to escape when i have a value in hand. Suppose i dont know what the values are then? – chikkada Dec 09 '14 at 07:25
  • You are selecting a variable, which you are using to build dynamic SQL to execute? And then that dynamic SQL fails if the variable contains a `'`? How about using bind variables to contain the variable content rather than concatenating it into the dynamic SQL string? That way there is no escaping needed and no chance of SQL injection and no killing the shared pool with a lot of unique statements. (Depends of course on what your actual use case is - there can be some cases where the bind variable solution is not possible, but we can't tell from your description ;-) – Kim Berg Hansen Dec 09 '14 at 14:53
  • Hi Kim, here the communication is happening between a client based scripting language and oracle. More precisely i'm using a shell script in which i'm passing this column value as an argument and thereby calling my Client script. Inside client script i'm calling oracle sql and using this column value to fetch something. During this fetching if my column has this `'` value, then my client script fails when trying to execute this sql – chikkada Dec 10 '14 at 14:48

2 Answers2

14

The best way is to use the quoting string literal technique. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

  • !
  • [ ]
  • { }
  • ( )
  • < >

You don't have to worry about the single-quotation marks within the string.

Suppose i have a column value as aaa'gh

So you could simply write the SQL as,

SELECT q'[aaa'gh]' FROM DUAL;

It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Here's the documentation on "q-quoting": http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm – Mr. Llama Dec 09 '14 at 17:37
  • I don't think this solves the problem. If the user input is aaa]'gh, the execution still fails and you can still have SQL injection. – jgosar Apr 18 '19 at 06:23
  • @jgosar There is a reason why these are called special characters and it is the same reason that certain characters and keyword are reserved or special in Oracle, or in any software. When you design your application and expects users to input a value, you will definitely need to have validations on the input. For example, If I want name as input, I should not allow !@#$%^&*(). To avoid SQL injection, better to use bind variables. And the answer exactly solves OP's question and has been accepted it for the same. – Lalit Kumar B Apr 18 '19 at 14:06
8

My simple approach with this problem, was always to just use

replace(string, '''', '''''')

but Lalit Kumar B solution, seems more sophisticated

a.j. tawleed
  • 894
  • 1
  • 8
  • 22
  • Actually the new literal quoting technique saves a lot of time for developers :-) Gone are those days when we developers used to check the dynamic sql using dbms_output, in development database, just to make sure things are at place before moving into production. – Lalit Kumar B Dec 09 '14 at 15:58
  • This actually works better than the accepted answer because it correctly handles inputs such as abc'de, as well as abc]'de – jgosar Apr 18 '19 at 06:33