Whether it be dynamically changing the where clause on a Forms block, building a string to run in an EXECUTE IMMEDIATE statement, or generating a bit of slave SQL, sooner or later you’ll come across a situation where you need to build a literal string with embedded quotes.
The default SQL escape character in Oracle? It’s a quote.
For a literal string that includes quotes, this isn’t too bad…
SELECT 'Hey Mike, the Pub''s open !' FROM dual;
Unfortunately, as soon as you start dropping in quoted variables, those pesky quotes multiply like rabbits …
SELECT 'SELECT table_name FROM all_tables WHERE owner = '||''''|| username ||''''||';' FROM all_users WHERE username IN ('MIKE', 'HR');
OK, not the most inspired example, but hey, it’s Friday night and the pub’s now closed.
Add to this quote proliferation, the fact that different environments do have a tendency to treat quotes differently and it all adds up to a lot of squinting and cursing whilst you play find the missing ( or extraneous) quote.
One of the great things about programming for a living is that you’re always likely to learn something from a colleague who has taken an approach to a problem that you never thought of.
So, with thanks to Justin for providing this week’s “that was worth getting out of bed for” moment…
SELECT 'SELECT table_name FROM all_tables WHERE owner = '||CHR(39)|| username || CHR(39)||';' FROM all_users WHERE username IN ('MIKE', 'HR');
The result is the same as for the previous query :-
SELECT table_name FROM all_tables WHERE owner = 'HR';
SELECT table_name FROM all_tables WHERE owner = 'MIKE';
How does this magic happen? Well, the CHR function returns the ASCII character for the value passed in ( 39 is a single quote ).
Because we’re concatenating this into the string, there’s no need to escape it. Our quote population is back under control.
Incidentally, ASCII is another one of those brilliantly simple inventions – a rosetta stone for devices to talk to each other using a simple 8-bit code to represent any printable character or escape sequence. 50 years later and it’s still in use.
Care should be taken when using the technique described in this post as it may result in your database being susceptible to SQL Injection. Have a look here for more details.