Remove SQL Injection vulnerability from query

Question

I need to fetch the data from a table which multiple filters and limit rows from java script datatable request

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON 
site_dn = mrbts AND siteid = child_site_id

In my code i have a implementation to append the filter in the query before executing the prepared statement. filters here is List<String[]> filters having values of filters with the column name (UPPER(mrbts) like UPPER('%6105%'))... 6105 is the filter string and mrbts is the column name

private String createFilterWhereClause(List<String[]> filters) {
    StringBuilder sb = new StringBuilder();
        Iterator<String[]> filterParmItr = filters.iterator();
            while (filterParmItr.hasNext()) {
                String[] filterParm = filterParmItr.next();
               sb.append("(")
                        .append(filterParm[ScFilterCriteria.FILTER_PARM_VAL])
                        .append(")");
                if (filterParmItr.hasNext()) {
                    sb.append(" and ");
                }

            }
        return sb.toString();
}

During execution ,it forms the sql query as below and executed in prepared statement.

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
siteid = child_site_id   where UPPER(mrbts) like UPPER('%4105%') and 
((UPPER(technology) like UPPER('%LTE%')))

It has an SQL injection vulnarability. In order to solve that , i am trying to secure it by use prepared statement set string as below,

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
siteid = child_site_id  where ?

Using prepared statement ,

PreparedStatement ps = null;
Connection connection = null;
ps = connection.prepareStatement(sql);
String filters = createFilterWhereClause(filterClause);
ps.setString(1, filters );

Problem here in the sql query formed with single quotes after set string ,

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND 
siteid = child_site_id  where '((UPPER(mrbts) like UPPER(\'%6105%\')))';

How to remove the single quotes during set string and or any other approach to do this ? Could you someone help me.

Joop Eggen · Accepted Answer · 2018-01-05 10:27:20Z

The code for a static SQL statement would look like:

String query = "SELECT coalesce(parent_id,siteid) AS siteid, address, state, status, "
    + "plan, remarks "
    + "FROM archive "
    + "LEFT OUTER JOIN site_mappings ON site_dn = mrbts "
    + "AND siteid = child_site_id "
    + "WHERE UPPER(mrbts) LIKE UPPER(?) "
    + "AND UPPER(technology) LIKE UPPER(?)";

// UPPER probably is not needed; there was one spurious comma after "remarks"

String mrbts = "4105";
String technology = "LTE";

try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
    preparedStatement.setString(1, "%" + mrbts + "%");
    preparedStatement.setString(2, "%" + technology + "%");
    try (resultSet = preparedStatement.executeQuery()) {
        while (resultSet.next()) {
            ...
        } 
        return list; // Or such
    }
}

For a dynamic number of criteria:

StringBuilder sb = new StringBuilder();
List<Object> params = new LinkedList<>();
...
sb.append(" AND mrbts LIKE ? ");
params.add(mrbts);
...
int column = 1;
for (Object param : params) {
    preparedStatement.setObject(column, param);
    ++column;
}

Ananta · Accepted Answer · 2018-01-07 15:56:17Z

2

Change your query to :

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
    remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
    siteid = child_site_id  where ((UPPER(mrbts) like UPPER(?));

Set only parameters with prepared statement parameters.

To add dynamic conditions : //Your base sql statement String sqlString = "Select ...";

//add condition only in few cases
if(mycondition){
    sqlString += "WHERE mycondition = ?"
}

ps = connection.prepareStatement(sql);

//bind the corresponding dynamic parameter you just added in the where clause. 
if(mycondition){
   ps.setString(1, myparameter );
}

It is safe to do that if there isn't any user input concatened with the sqlString.

edited Jan 7, 2018 at 15:56

answered Jan 5, 2018 at 10:04

Ananta

7001 gold badge7 silver badges19 bronze badges

2 Comments

PGS Over a year ago

Thanks for your answer. mrbts is also dynamic based on the column index. I cannot use ((UPPER(mrbts) like UPPER(?)). Before setting in prepared statement i am creating List of filters 'List<String[]>'

Ananta Over a year ago

If the conditions are static but not created with user input you should concatenate the sql query. I have updated my answer.

Valerii Sloboda · Accepted Answer · 2018-01-05 10:40:00Z

0

As Joop Eggen said, use prepared statements

answered Jan 5, 2018 at 10:40

Valerii Sloboda

1283 silver badges13 bronze badges

1 Comment

achAmháin Over a year ago

This answer doesn't really add much.

Collectives™ on Stack Overflow

Remove SQL Injection vulnerability from query

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related