Regex broken when encounter linebreak

Question

I'm trying to match pattern for sql injection prevention. However, this test case failed.

 @Test void testDropPattern() { var string = " {\r\n" + " \"fullName\": \"Peter Parker\",\r\n" + " \"email\": \"[email protected]\",\r\n" + " \"birthDate\": \"2000-12-31\",\r\n" + " \"gender\": \"M', '2000-12-31'); DROP table users --\"\r\n" + " }\r\n" + "\r\n"; var regex = "(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)"; var pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE); assertTrue(pattern.matcher(string).matches()); }

It is broken because there is newline character (\r\n) at the end of the string. This is the original json body with newline.

{ "fullName": "Peter Parker", "email": "[email protected]", "birthDate": "2000-12-31", "gender": "M', '2000-12-31'); DROP table users --" }

What regex that can be used to handle this use case?

var pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); — ControlAltDel
– ControlAltDel, Commented Dec 22, 2020 at 23:32
Not a solution (@ControlAltDel gave you that) but why do you need to test for it? are you sending whatever you get in as raw SQL instead of parameters? Or are you just out to block bots who try to do it? — Fredrik
– Fredrik, Commented Dec 22, 2020 at 23:34
Frame Challenge: The fact that you think you need this indicates you are doing it wrong. If you use a JSON parser, parameterized queries (with "?" for values) and prepared statements, you completely prevent Little Bobby Tables from messing with your database. You can generate SQL dynamically if needed, but never provide data values as literals. — Jim Garrison
– Jim Garrison, Commented Dec 23, 2020 at 1:08
This is used to create spring filter. I'm working with legacy apps which builds dynamic SQL by joining string, so it's prone to sql injection. Rather than fix all the legacy apps, I try to create web filter that blocks sql scripts upfront — Timothy
– Timothy, Commented Dec 24, 2020 at 1:20
@Timothy - Just so that you know that >you< will get the blame if your filters are ineffective and your site gets hacked. — Stephen C
– Stephen C, Commented Dec 24, 2020 at 1:29

ControlAltDel · Accepted Answer · 2020-12-22 23:37:04Z

Including the multiline flag allows you to run your regular expression without having to work about the new lines

@Test void testDropPattern() { var string = " {\r\n" + " \"fullName\": \"Peter Parker\",\r\n" + " \"email\": \"[email protected]\",\r\n" + " \"birthDate\": \"2000-12-31\",\r\n" + " \"gender\": \"M', '2000-12-31'); DROP table users --\"\r\n" + " }\r\n" + "\r\n"; var regex = "(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)"; var pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); assertTrue(pattern.matcher(string).matches()); }

The regex itself doesn't seem to be correct😊. Did you try System.out.println(pattern.matcher(string).matches());?
The regex matches his example after 6984 steps. regex101.com/r/wl4pjK/1
This works on the online tester, but not in junit test. Weird

Timothy · Accepted Answer · 2020-12-24 01:26:08Z

I end up with replacing all newline character before validating regex. Just seems not too clean for me.

 @Test void testDropPattern() { var stringOriginal = " {\r\n" + " \"fullName\": \"Peter Parker\",\r\n" + " \"email\": \"[email protected]\",\r\n" + " \"birthDate\": \"2000-12-31\",\r\n" + " \"gender\": \"M', '2000-12-31'); DROP table users--\"\r\n" + " }\r\n" + "\r\n"; var regex = "(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)"; var stringWithoutLineBreak = stringOriginal.replaceAll("\\r\\n|\\r|\\n", ""); var pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE); assertTrue(pattern.matcher(stringWithoutLineBreak).matches()); }

Collectives™ on Stack Overflow

Regex broken when encounter linebreak

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related