0

I have a query which fetch data from database which having multiple search filters. I am using prepared statements to construct the where condition and set the values in the query.

Program :

sql = "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(?)))"

preparedStatement= connection.prepareStatement(sql);

int positionIndex = 1
while (iterator.hasNext()) {
            Integer key = iterator.next();
            if (key == 6) {
                String value = filterMap.get(key);
                String[] filterPatterns = value.trim().split(" ");
                for (int i = 0; i < filterPatterns.length; i++) {
                    preparedStatement.setString(positionIndex++, "%" + filterPatterns[i] + "%");
                }
            } else {
                preparedStatement.setString(positionIndex++, "%" + filterMap.get(key) + "%");
            }
        }
preparedStatement.executeQuery();

Example SQL 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('%4105%') and 
((UPPER(technology) like UPPER('%LTE%')))

During the security scan ,it causes vulnarabilities for the below input in the search

Example request: https://1.1.1.1/sample_servlet/DataServlet?TYPE=getListDataURL" -p sSortDir_0 --suffix=' ' --level=3 --method=POST --data="sEcho=1&iColumns=11&sColumns=&iDisplayStart=0&iDisplayLength=10&mDataProp_0=actions&mDataProp_1=drilldown&mDataProp_2=modifiedtime&mDataProp_3=siteid&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3='%2b(select*from(select(sleep(20)))a)%2b'&bRegex_3=false&bSearchable_3=true&iSortCol_0=2&sSortDir_0=desc&iSortingCols=1&bSortable_0=false&bSortable_1=false&bSortable_2=true&bSortable_3=true&REQUESTTYPE=getListData" -a

Request 1: sSearch_3='%2b(select*from(select(sleep(20)))a)%2b'

I guess the SQL query will be formed 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 UPPER(mrbts) like UPPER('%4105%') and 
((UPPER(technology) like UPPER('%2b(select*from(select(sleep(20)))a)%2b'')))

Response : {"iTotalRecords":3,"aaData":[],"count":{"planning":3},"iTotalDisplayRecords":0,"sEcho":1,"isTableFiltered":true}

Request 2: `sSearch_3='

Response : {"iTotalRecords":0,"aaData":[],"iTotalDisplayRecords":0,"sError":"Db error while fetching data","sEcho":1}

Request 3: `sSearch_3="

Response : {"iTotalRecords":0,"aaData":[],"iTotalDisplayRecords":0,"sEcho":1,"isTableFiltered":true}

In the above requests it didnot fetch any db contents . It is still vulnarable ? How to remove this vulnarables.

Burp tool report SQL Injection.

Parameter appears to be vulnerable to SQL injection attacks. The payload '+(select*from(select(sleep(20)))a)+' was submitted in the sSearch_3 parameter. The application took 20012 milliseconds to respond to the request, compared with 10 milliseconds for the original request, indicating that the injected SQL command caused a time delay

PGS
  • 1,046
  • 2
  • 17
  • 38
  • what is your question ? – Ravi Jan 15 '18 at 03:55
  • My question is how to remove this vulnerable ? – PGS Jan 15 '18 at 03:57
  • use preparedstatement – Ravi Jan 15 '18 at 03:58
  • I am already using prepared statements and constructing the where conditions. eg. `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(?)))` Using Set string to set the values coming from `search_` in the request – PGS Jan 15 '18 at 04:01
  • Please show your java code containing preparedstatement. – krokodilko Jan 15 '18 at 04:04
  • 1
    *I guess the SQL query will be formed as below* That is not how bind parameters work. Show real code. – Elliott Frisch Jan 15 '18 at 04:08
  • @krokodilko added the program in the question – PGS Jan 15 '18 at 04:12
  • @Gopi **Real** code. An `Integer key` would never `==` a `String`. And your indentation is terrible. Why do you have casts there too? – Elliott Frisch Jan 15 '18 at 04:25
  • @ElliottFrisch Actually it was a edit mistake. Modified now. – PGS Jan 15 '18 at 04:30
  • A PreparedStatement does not replace the parameters inside the query. It sends the query and the parameters in separate network calls –  Jan 15 '18 at 07:23
  • @a_horse_with_no_name That is not how MySQL Connector/J does it by default, though. That does escape and inline parameters in the actual query string sent to the server. – Mark Rotteveel Jan 15 '18 at 18:51
  • @MarkRotteveel: really? That defeats the whole purpose of a PreparedStatement in my opinion (but then... it's MySQL) –  Jan 15 '18 at 19:06
  • @a_horse_with_no_name Not entirely, as they will properly escape things, but yes. You need to explicitly enable server side prepared statements (`useServerPrepStmts=true`), iirc. – Mark Rotteveel Jan 15 '18 at 19:09

0 Answers0