"drop" key word is not working in threat protection of sql injection

[kkashiviswanath]

Hi @Sai Saran Vaidyanathan

I am using Regular Threat protection policy in which im using sql injection expression "[\s]*((delete)|(exec)|(drop\s*table)|(insert)|(shutdown)|(update)|(\bor\b))".

when i am doing testing threat detected for delete, exec,insert,update and shutdown except drop.

Threat is not detected for "drop" keyword in query param. Please help me out.

And also when i pass "drop" key word in json object will it work

[deboraelkin]

The regular expression you mention is trying to match "drop table" (with potentially multiple blanks in between the 2 words), are you testing with just "drop"?

The regular expression protection policy will check for these patterns on the part of the request you specify. If you'd also like to check the request payload, use the <JSONPayload> element: https://docs.apigee.com/api-platform/reference/policies/regular-expression-protection#jsonpayloadele...

[kkashiviswanath]

Hi @deboralkin

Thanks for update, i tried with multiple or single blocks but it didnt worked out.

Ex: https://api-xxxx.com/abc/xxxxx?abc=drop abc table

https://api-xxxx.com/abc/xxxxx?drop table xyz

https://api-xxxx.com/abc/xxxxx?drop table xyz

The above are the scenarios i have tested, Can you suggest how to use pattern for above testcase?

Thanks in advance..

[deboraelkin]

URL encoding may be getting in the way.

Can you show your Regular expression protection policy configuration?

Also, it might help to do a trace and see the value of the URL (I guess that's where you're applying the policy to)

Also, your first example will not be picked up, because the regular expression is looking for "drop table", it won't match "drop abc table"

[kkashiviswanath]

HI

PFA of regularthreatprotection.txt Regular expression policy and i have added it in preflow.

ex: test case: https://xxxxx.com/abc/xxxxxx?drop table tablename

[deboraelkin]

It was URL encoding indeed.

If you traced a proxy that contained a policy like yours, you'd notice that the value of request.uri is

abc/xxxxxx?drop%20table%20tablename

because URIs are encoded. So if you want to capture this you want to modify the pattern for request.uri to something like this

<Pattern ignoreCase="true">[\s]*((delete)|(exec)|(drop(\s|\%20)+table)|(insert)|(shutdown)|(update))</Pattern>

Notice that the \bor\b pattern matching wouldn't work either for exactly the same reason. I have removed it, as matching for "or" surrounded by whitespaces in any query parameter might be too greedy. If you still would like to do it, you'd need to replace \b with \%20 (The regular expression matching the encoded whitespace)