Passing Single Quotes in DBMS Assert Package

Today, while describing the usefulness of DBMS_ASSERT package to prevent SQL and PL/SQL Injection attacks someone asked me how to pass a string with single quotes successfully to this package.

First, if you don't know what DBMS_Assert is or why you should know about it, check out the presentation on this blogpost. In summary, the ENQUOTE_LITERAL() function strips off all the single quotes from around the string and replace with just a pair of single quotes, which makes it a clean, uninjected string. Here is an example of a string called Joe Pizza.

SQL> select dbms_assert.enquote_literal('Joe Pizza')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL('JOEPIZZA')
---------------------------------------
'Joe Pizza'

As you can see, the string is presented back with a pair of single quotes. Now let's see what happens if we put another pair of single quotes. To be syntactically correct, we will need to escape the single quote with another single quote.

SQL> select dbms_assert.enquote_literal('''Joe Pizza''')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL('''JOEPIZZA''')
-------------------------------------------
'Joe Pizza'

What happened? Well, the Assert package stripped off all extraneous single quotes and replaced them with just one pair of single quotes.

But what happens when we need to put a single single quote as a legitimate character, e.g. Joe's Pizza? This is where the escape sequence in SQL comes in. You can write this as follows:

SQL> select dbms_assert.enquote_literal(q'[Joe''s Pizza]')  from dual;

DBMS_ASSERT.ENQUOTE_LITERAL(Q'[JOE''SPIZZA]')
---------------------------------------------
'Joe''s Pizza'

That's it; "Joe's Pizza" is now perfectly passed.

Wait a minute. It's not Joe's Pizza; it's Joe''s Pizza. There are two single quotes; not one. That's not what we intended, did we? So it's wrong, right?

No; it's perfectly fine. You see, when you pass "Joe's Pizza" inside single quotes, you must escape the single apostrophe inside. The escape character is the single quote. Since the Assert package puts single quotes around the string, the single quote inside the string must be escaped to be syntactically correct. So, it makes sense to have two single quotes; not just one.

Joe's Pizza--if there is something in real world in that name--should probably pay me for the publicity.