Friday, April 28, 2017

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.


11 comments:

pre school in hyderabad said...

Good Post
Sanjary kids is the best playschool, preschool in Hyderabad, India. Start your play school,preschool in Hyderabad with sanjary kids. Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and Teacher Training Program.
best preschool in hyderabad
preschool teacher training
playschools in hyderabad
preschool teacher training in hyderabad

jangjang said...

http://lelandbchapman.com
http://redwoodcoastrealestate.com/
http://montiepower.com/

Unknown said...

sex education ss3

BEO89 said...

Great survey, I'm sure you're getting a great response.สล็อต แตกง่าย

Liberty Shoes said...

Buy Liberty's Kids Shoes Online and Give your kids the perfect gift at affordable rates. Shop for the latest kids' footwear and shoes Online at Liberty. Check out the exclusive collection of casual shoes for kids, school, and sports shoes available at the best price.

Anonymous said...

Women's ethnic tops and kurti can be purchased in India. Sabhyata has a large selection of ethnic tops online and kurti at affordable costs. Now is the time to shop!

pg slot said...

สล็อต PGSLOTแตกง่าย เล่นง่ายได้เงินจริงผู้ให้บริการเกมสล็อต pg slot ออนไลน์บนโทรศัพท์เคลื่อนที่ที่มีเกมนานาประการให้เลือก เป็นเกมรูปแบบใหม่ที่ทำเงินให้ผู้เล่นได้เงินจริง 

ทดลองสล็อต pg said...

ทดลองสล็อต pg เล่นฟรีทุกค่าย PG SLOT รองรับเล่นผ่านมือถือทุกระบบ ไม่ว่าจะเป็น IOS และก็ Android ผู้ใช้สามารถเล่นได้ในทุกเกมแบบไม่ต้องสมัครก่อนใครที่เว็บ PG-SLOT.GAME

PG JOKER said...

PG JOKER มีโปรโมชั่นมากมาย ให้คุณได้เลือก สิทธิพิเศษ สำหรับสมาชิกสุดพิเศษของพวกเรา เมื่อลงทะเบียนสมัครสมาชิกใหม่ รับโบนัสโดยทันที ของยอดฝาก เกมสนุกแถมได้เงินไม่อั้นพีจีสล็อตเว็บเดียวเท่านั้น

vcube said...

fantastic blog I find this blog to be pretty fantastic. What matters is that the content on this blog is written in an intelligible and clear manner. The information is quite informative.

If you are looking for the best institute in Hyderabad for software courses

java full stack developer

python training

V cube software solutions said...

I appreciate you sharing such useful information. Very useful and interesting post. Keep going!
Java Full Stack course in Hyderabad

Translate