Java regular expression to prevent SQL injection

Question

During my burp suite test there are some SQL Injection vulnerabilities reported by the tool even after using prepared statement

Eg :

SELECT address, state, status, plan, remarks, FROM archive  
LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
siteid = id where ((UPPER(technology) like 
UPPER(?)))

 ps.setString(1, "%" +value+ "%");

Below are few SQL injection strings used by the tool

1. '%2b(select*from(select(sleep(20)))a)%2b'

2. '

3. "

I have filter function to white list the value and raise SQLException to prevent injection.

Pattern pattern = Pattern.compile("['\"*$]");
        Matcher matcher = pattern.matcher(value);
        if (matcher.find()) {
            throw new SQLException("Invalid filter value");
        }

It does not work for '%2b(select*from(select(sleep(20)))a)%2b' .

Issue detail :

The payload '+(select*from(select(sleep(20)))a)+' was submitted in the parameter. The application took 20011 milliseconds to respond to the request, compared with 24 milliseconds for the original request, indicating that the injected SQL command caused a time delay.

How to create regular expression to prevent from SQL injection ?

Don't use regex for this. Use prepared statements. See [Java - escape string to prevent SQL injection](https://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection) — ctwheels, Jan 16 '18 at 16:50
I agree; I was just going to say use parsing for this, not a regex. — markspace, Jan 16 '18 at 16:50
@ctwheels I am already using prepared statements to form the SQL query. Still the tool is reporting SQL injection vulnarable. — PGS, Jan 16 '18 at 16:51
@ctwheels Prepared Statements is what he uses as far as I understand "even after using prepared statement" — Lothar, Jan 16 '18 at 16:51
Have you actually tried to execute the statement with the supposed SQL-injection-parameter-value? I assume a blind alarm. Oh, and what database and JDBC-driver are you using? — Lothar, Jan 16 '18 at 16:52
Also we could use what your actual desired input is. Are you literally allowing users to add wildcards to their input string? What sort of strings does `upper(technology) like upper(?)` actually process? — markspace, Jan 16 '18 at 16:54
@Lothar Scan result without using prepared statements and with prepared statements are same. I am confused now. — PGS, Jan 16 '18 at 16:54
@markspace. It actually expects some string `a-zA-Z` and may be _. — PGS, Jan 16 '18 at 16:56
Both expand the same way? There's no other limits? What are some actual expect inputs? — markspace, Jan 16 '18 at 16:57
@markspace Some expected inputs are LTE , 3G or WCDMA , GSM . We have similar fields which has accepts [a-z][A-Z][0-9]_ . etc — PGS, Jan 16 '18 at 16:59
I think you could use something like `Pattern.compile("[a-zA-Z_]+").matcher(input).matches()` to at lest remove the punctuation from those sql injection strings. — markspace, Jan 16 '18 at 17:00
So if your fields accept [a-z][A-Z][0-9]_ why aren't they filtering out the sql injection strings? Feels like somebody didn't do the gui right. — markspace, Jan 16 '18 at 17:03
`[a-zA-Z0-9_]` is equivalent to `\w`. If you want to remove anything that isn't in that set, you can replace all `\W` with nothing. — ctwheels, Jan 16 '18 at 17:05
@markspace . Burp tool records the proxy based on the requests and try to inject it with various attacking string s. It does not have anything to do with gui. — PGS, Jan 16 '18 at 17:09
Please provide the source that does the preparation and execution of the statement. If done right, there shouldn't be any SQL injection possible (or there is a bug in the JDBC-driver and/or database) — Lothar, Jan 16 '18 at 18:54
Again: Please provide the source that does the preparation and execution of the statement. If done right, there shouldn't be any SQL injection possible (or there is a bug in the JDBC-driver and/or database) — Lothar, Jan 17 '18 at 21:55

Java regular expression to prevent SQL injection

0 Answers0