How does one escape special characters when writing SQL queries?

Submitted by admin on Wed, 2004-08-04 14:53
Body: 

Escape quotes

Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;

TEXT
--------------------
Franks's Oracle site

SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;

TEXT
----------------
A 'quoted' word.

SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

TEXT
-------------------------
A ''double quoted'' word.

Escape wildcard characters

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

SELECT name FROM emp 
   WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp 
   WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;

Other methods:

Define an escape character:

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF
SELECT '&ABC' x FROM dual;

»

Comments

Submitted by Velu Natarajan (not verified) on Mon, 2006-04-17 07:56.

Oracle10g allows you to define your own string delimiters. Any character that is not present in the string can be defined as:

--Previous syntax
dbms_output.put_line('Hi Ram''s!');

--New syntax
dbms_output.put_line(q'#Hi Ram's!#');
dbms_output.put_line(q'$Hi Ram's!$');

»

Submitted by Tom Myers (not verified) on Wed, 2006-08-23 12:23.

When passing string values from a user interface into an Oracle stored procedure, for use in a dynamicly constructed SQL query, they must be "escaped" to insure the query isn't broken or worse, provide a security whole.

Your examples don't cover this situation, nor how to escape characters in a simple where clause such as:

select * from employee where last_name = vUiInputValueVariable;

Without an escape, a string such as '%';delete from employee
could wipe out the table!

Of course writting dynamic SQL such as this is very bad practice, but its possible if the input string is properly escaped.

One online Oracle documentation source for version 9.2(http://www.lc.leidenuniv.nl/awcourse/oracle/text.920/a96518/cqspcl.htm) indicates braces ( {} ) or a backslash ( \ ) can be used to escape entire strings or characters. However those techniques do not work for some reason on our Oracle 9.2 installation.

I hope this info is helpful.

»

Submitted by lrirwin on Tue, 2014-05-06 11:59.

None of the examples I can find work when the leading and trailing characters in the data are single quotes.
I need to be able to insert/update whatever data arrives in a string.
I'm hoping to be able to simply call an escape routine on every string to protect characters that cause issues and insert/update as needed.

The string content might be:
'ROBERT "BOB"'
Or:
O'Conner 'Conner'
Or:
'ROBERT' "BOB"
Or even (as a worst case):
''&"%\^@'"'"

It should be as easy as this:
SET ESCAPE '\';
CREATE TABLE LRI(ID VARCHAR(4),DESCR VARCHAR(80));
DEF var1='\'ROBERT \"BOB\"\'';
INSERT INTO LRI(ID,DESCR) VALUES('0001','&var1');
SELECT * FROM LRI;

But, it does not work...
=====
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 6 12:40:37 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> CREATE TABLE LRI(ID VARCHAR(4),DESCR VARCHAR(80));
Table created.
SQL> SET ESCAPE '\';
SQL> DEF var1='\'ROBERT \"BOB\"\'';
SQL> INSERT INTO LRI(ID,DESCR) VALUES('0001','&var1');
old 1: INSERT INTO LRI(ID,DESCR) VALUES('0001','&var1')
new 1: INSERT INTO LRI(ID,DESCR) VALUES('0001','')
1 row created.
SQL> SELECT * FROM LRI;
ID |DESCR
----|--------------------------------------------------------------------------------
0001|
=====

Any help on a tried and true method to handle complex combinations of single and double quotes (and/or all other special characters), no matter where they are in a string, would be most appreciated.
We receive ANSI X12 and HL7 data from 3rd paty sources and should not change any content values... just parse it and put it in the proper fields in the database.

TIA, Larry

»

Submitted by lrirwin on Tue, 2014-05-06 12:39.

It appears that if you replace any single quote in a string with 4 single quotes, that SQLPlus will reduce them to one single quote on inserts and updates...
i.e. DEF var1='O''''Connor ''''Connor''''';
And it does not matter where the single quotes are in the string...
SQL> select * from lri;
ID |DESCR
----|--------------------------------------------------------------------------------
0002|'S'
0003|'ROBERT "BOB"'
0004|O'Connor 'Connor'

This still does not provide a single function to handle all the characters that might cause issues, but it seems to work for single quotes.

»