Oracle – for when it was like that when you got there

Main menu

Post navigation

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.

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):

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.