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;
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;
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;
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.
Confessions of an Oracle Database Junkie - Arup Nanda The opinions expressed here are mine and mine alone. They may not necessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs alone and may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please use at your own risk and after thorough testing in your environment.
Friday, April 28, 2017
Passing Single Quotes in DBMS Assert Package
Subscribe to: Post Comments (Atom)
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
oracle fusion financials classroom training
Workday HCM Online Training
Oracle Fusion HCM Classroom Training
We understand that students in Australia encounter different challenges in completing their Research Paper Writer Services. We offer Cheap Custom Essay Writing Services to students regardless of their specialty, discipline or educational level.
We are the best piping design course in Hyderabad, India. Sanjary academy Offers Piping Design Course and Best Piping Design Training Institute in Hyderabad. Piping Design Institute in India Piping Design Engineering.
Piping Design Course
Piping Design Course in india
Piping Design Course in hyderabad
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
Thanks for sharing information
Best QA / QC Course in India, Hyderabad. sanjaryacademy is a well-known institute. We have offer professional Engineering Course like Piping Design Course, QA / QC Course,document Controller course,pressure Vessel Design Course, Welding Inspector Course, Quality Management Course, #Safety officer course.
QA / QC Course
QA / QC Course in india
QA / QC Course in hyderabad
Its really good blog and very information, we are tempo traveller rental company in Gurgaon providing tempo traveller in gurgaon for local and outstation, we have luxury tempo traveller in gurgaon you can rent 12 seater, 17 seater , 20 seater , 26 seater Tempo traveller gurgaon to outstation tours ~ JBL Tempo Traveller Rentals Delhi NCR ~ 9560075009
Our location :
Tempo traveller in Gurgaon
sex education ss3
Great survey, I'm sure you're getting a great response.สล็อต แตกง่าย
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.
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!
Blog was amazing and worth a while.
sri lanka visa for indians
สล็อต PGSLOTแตกง่าย เล่นง่ายได้เงินจริงผู้ให้บริการเกมสล็อต pg slot ออนไลน์บนโทรศัพท์เคลื่อนที่ที่มีเกมนานาประการให้เลือก เป็นเกมรูปแบบใหม่ที่ทำเงินให้ผู้เล่นได้เงินจริง
ทดลองสล็อต pg เล่นฟรีทุกค่าย PG SLOT รองรับเล่นผ่านมือถือทุกระบบ ไม่ว่าจะเป็น IOS และก็ Android ผู้ใช้สามารถเล่นได้ในทุกเกมแบบไม่ต้องสมัครก่อนใครที่เว็บ PG-SLOT.GAME
PG JOKER มีโปรโมชั่นมากมาย ให้คุณได้เลือก สิทธิพิเศษ สำหรับสมาชิกสุดพิเศษของพวกเรา เมื่อลงทะเบียนสมัครสมาชิกใหม่ รับโบนัสโดยทันที ของยอดฝาก เกมสนุกแถมได้เงินไม่อั้นพีจีสล็อตเว็บเดียวเท่านั้น
Post a Comment