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.

Preventing SQL and PL/SQL Attacks at New York Meetup

Thank you all for attending my session at New York Meetup and New York Oracle User Group Spring Meeting in New York City on April 27th. I am truly honored by your presence, and especially for the questions.

You can download the presentation here And the scripts I used for the demos here. As always, I will appreciate your feedback either via comments here, or on social media or emails.

Twitter @ArupNanda
Facebook.com/ArupKNanda