Nesting Quotes in SQL

Recently, I came across one of those situations where you need to get some data from a Production instance and transform it in some way. The catch is that you only have read access on the database in question. All of the usual methods of extracting data are blocked by either an extremely long and tedious battle to get the required access or are prohibited by various security policies.
I will need to do this quite regularly, so it looks like I’ll have to write some slave SQL to extract the data and then load it into a non-production database, where I have more privileges.
Once again, I’m going to have to struggle to remember the new Oracle Q quote syntax.

But before all that, a brief history of quotes.

In the beginning, there was the escape character. In SQL*Plus, this happens to be a single quote …

select 'It''s only a game' from dual;

Things got a bit more complicated when you needed to build a string that included column values...

SELECT 'This is '||USER||'''s session' 
 FROM dual;

There was however an alternative to runaway punctuation.

SELECT 'This is '||USER||CHR(39)||'s session'
FROM dual;

Good old ASCII.
Now we come bang up to date with Oracle's new quote syntax ….

SELECT 'This is '||USER||q'['s session]'
FROM dual;

Note to self – the syntax is :
q'[Hey, that's my quoted string]'
The number of times I get those brackets in the wrong place !

So, back to my current predicament.
Let's take a topical example.
I've got a table of teams in the Cricket World Cup ( going on in India and Bangladesh as I write). Included in the table is the time and date of each team's first game in the tournament.
Anyway, here's the DDL for the table and the DML to populate it so you can play along if you're so inclined.

CREATE TABLE teams ( 
    country_code VARCHAR2(3), 
    country_name VARCHAR2(30), 
    ranking NUMBER(2), 
    first_match DATE) 
/ 
DECLARE 
    PROCEDURE ins( p_code VARCHAR2, p_name VARCHAR2, p_rank NUMBER, 
        p_date DATE) IS 
    BEGIN 
        INSERT INTO teams( country_code, country_name, ranking, 
            first_match) 
        VALUES( p_code, p_name, p_rank, 
            p_date); 
    END ins; 
BEGIN 
    ins( 'AUS', 'AUSTRALIA', 1, TO_DATE('21/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'IND', 'INDIA', 2, TO_DATE( '19/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'SL', 'SRI LANKA', 3, TO_DATE( '20/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'SA', 'SOUTH AFRICA', 4, TO_DATE( '24/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'ENG', 'ENGLAND', 5, TO_DATE( '22/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'PAK', 'PAKISTAN', 6, TO_DATE( '23/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'NZ', 'NEW ZEALAND', 7, TO_DATE( '20/02/11 09:30', 'DD/MM/RR HH24:MI')); 
    ins( 'BAN', 'BANGLADESH', 8, TO_DATE( '19/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'WI', 'WEST INDIES', 9, TO_DATE( '24/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'IRE', 'IRELAND' ,10, TO_DATE( '25/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'ZIM', 'ZIMBABWE', 11, TO_DATE( '21/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'NET', 'NETHERLANDS', 12, TO_DATE( '22/02/11 14:30', 'DD/MM/RR HH24:MI')); 
    ins( 'KEN', 'KENYA', 13, TO_DATE( '20/02/11 09:30', 'DD/MM/RR HH24:MI')); 
    ins( 'CAN', 'CANADA', 14, TO_DATE( '20/02/11 14:30', 'DD/MM/RR HH24:MI')); 
END; 
/

I'll want to capture this data regularly so I need to write a bit of slave SQL so that I can generate insert statements for these records to drop into another database.
At this point, the usual dire warnings about the dangers of SQL Injection apply.
So, if I'm not completely sure that there are no nasty surprises in the data I'm extracting, I can always strip out any dodgy characters. All the SQL injection attack methods I know of require some sort of punctuation character, so by stripping those out, I should guard against that particular problem. I know that I shouldn't have any such characters in the VARCHAR columns I'm selecting from so I won't corrupt any of the data by transforming it in this way.
As for the other columns, well, I want to retain the time element of the date anyway, and explicitly stating the date format is always a good move if you want to avoid vulnerability to an injection via NLS_DATE_FORMAT.
So, with the q syntax, the query is going to look something like this ( running in SQL*Plus):

set heading off 
set lines 300 
set pages 100 
set feedback off 
spool old_quote_ins.sql 
SELECT 'INSERT INTO remote_teams( country_code, country_name, ranking, first_match) ' 
    ||' VALUES ( ' 
    ||q'[']'||REGEXP_REPLACE(country_code,'[[:punct:]]')||q'[',]' 
    ||q'[']'||REGEXP_REPLACE(country_name,'[[:punct:]]')||q'[',]' 
    ||ranking||',' 
    ||'TO_DATE('||q'[']'||TO_CHAR( first_match, 'DD-MM-RR HH24:MI')||q'[','DD-MM-RR HH24:MI'));]' 
FROM teams 
/ 
spool off
set feedback on 
set heading on 

Yep, we sill need 3 quotes to print out a single quote. However, by mixing the ASCII character approach with the quotation syntax we get this :

set heading off 
set lines 300 
set pages 100 
set feedback off 
spool mixed_quote_ins.sql 
SELECT 'INSERT INTO remote_teams( country_code, country_name, ranking, first_match) ' 
    ||' VALUES( ' 
    ||CHR(39)||REGEXP_REPLACE(country_code,'[[:punct:]]')||CHR(39)||',' 
    ||CHR(39)||REGEXP_REPLACE(country_name,'[[:punct:]]')||CHR(39)||',' 
    ||ranking||',' 
    ||'TO_DATE( '||CHR(39)||TO_CHAR( first_match, 'DD-MM-RR HH24:MI')|| q'[', 'DD-MM-RR HH24:MI'));]' 
FROM teams 
/ 
spool off 
set feedback on 
set heading on 

Anyway, I now have a series of insert statements that I can point at the table in my remote database


INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'AUS','AUSTRALIA',1,TO_DATE( '21-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                   
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'IND','INDIA',2,TO_DATE( '19-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                       
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'SL','SRI LANKA',3,TO_DATE( '20-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                    
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'SA','SOUTH AFRICA',4,TO_DATE( '24-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                 
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'ENG','ENGLAND',5,TO_DATE( '22-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                     
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'PAK','PAKISTAN',6,TO_DATE( '23-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                    
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'NZ','NEW ZEALAND',7,TO_DATE( '20-02-11 09:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                  
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'BAN','BANGLADESH',8,TO_DATE( '19-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                  
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'WI','WEST INDIES',9,TO_DATE( '24-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                  
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'IRE','IRELAND',10,TO_DATE( '25-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                    
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'ZIM','ZIMBABWE',11,TO_DATE( '21-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                   
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'NET','NETHERLANDS',12,TO_DATE( '22-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'KEN','KENYA',13,TO_DATE( '20-02-11 09:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                      
INSERT INTO remote_teams( country_code, country_name, ranking, first_match)  VALUES( 'CAN','CANADA',14,TO_DATE( '20-02-11 14:30', 'DD-MM-RR HH24:MI'));                                                                                                                                                     

Oh well, that should keep me occupied whilst I wait to see just how the giants of World Cricket will fare. You know, India, Australia, South Africa...Ireland. Honestly, I think it'd be better if England just didn't bother turning up to any tournament calling itself “World Cup”. It'd just save time.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s