Fix REGEXP-based Rules

[sspendol]

Could use some help with the Regular Expressions for:

• htp.p* with no sys prefix
• sys.htp.p that is not wrapped in apex_escape.

[DougAGault]

Because of Oracle's non-existent support for REGEX Group Look-Ahead/Behind, you have to get creative with the way you structure the SQL Statement .

The following SQL shows probably the most straight forward way to implement this check . Pay particular attention to the where clause.

with string as (  
select q'!So uhh, tic toc and keep ticking, while I get you flipping off what I'm kicking. I come rough, tough like an Elephant tusk. It was the night before New Year's all through the projects, not a handgun was silent, not even a Tec. Yes, the rhythm, the rebel, alone in my level heat it up past the boiling point of metal. I come with that ol' loco style from my vocal, Couldn't peep it with a pair of bi-focals. Now, lo and behold, another deadly episode, bound to catch another charge when I explode Handcuffed in the back of a bus, forty of us.
My DJ the catcher, he's my man, anyway he's the one who sys.htp.p devised the plan. Step through your section with the Force like Luke Skywalker, rhyme author, orchestrate mind torture. Life as a shorty shouldn't be so rough. Leave it up to me while I be livin' proof Step through your  sys.htp.p section with the Force like Luke Skywalker, rhyme author, orchestrate mind torture. Handcuffed in the back of a bus, forty of us. Domino effect arts and crafts, paragraphs contain cyanide. Domino effect arts and crafts, paragraphs contain cyanide. I come with that ol' loco style from my vocal, Couldn't peep it with a pair of bi-focals.
!' s
from dual)
select 'FAIL'
from string 
where regexp_count(string.s,'htp\.p') > regexp_count(string.s,'(^|[^s]sys\.)htp\.p')
/

[sspendol]

Rule is currently set to this:

with string as (select :l_source as s from dual) select count(*) from string where regexp_count(string.s,'htp\.',1,'i') > regexp_count(string.s,'(^|[^s]sys\.)htp\.',1,'i') It will fail if the string starts with htp.XXX.

[DougAGault]

Not simple, but it works.

with string as (select :l_source as s from dual) 
select regexp_count(string.s,'htp\.',1,'i') htp 
     , regexp_count(string.s,'(^[^(a-z_0-9)]?|[^(a-z_0-9)])sys\.htp\.',1,'i') with_sys 
     , regexp_count(string.s,'htp\.',1,'i') - regexp_count(string.s,'(^[^(a-z_0-9)]?|[^(a-z_0-9)])sys\.htp\.',1,'i') DIFF
from string 

Test string used SYS.HTP.P test1.htp.p foo bar bong local.htp.p(flip); htp.prn('FOOL'); sys.htp.p('goof'); blue wagon mysys.htp.p total_sys.htp.p(thing) SyS.Htp.P

Number of HTP's found :8 Number of HTPs with sys : 3 Number of Difference: 5

Even finds sys.htp* and htp.* at the beginning of the line.