javaprepared-statementsql-injection

Using prepared statement for Order by to prevent SQL injection java

I have a query with where conditions , order by and limit. I am using prepared statements to set the where conditions and limit. Currently i am using string append for order by which causing SQL injection vulnerability.

I cannot use set string to order by like this order by ? ? SQL Order functionality not working if i do like this.

Example query:

SELECT siteid, technology, address, state, status FROM archive  LEFT OUTER 
JOIN mappings ON siteid = child_site_id order by siteid asc limit ? offset ?


SELECT siteid, technology, address, state, status FROM archive  LEFT OUTER 
JOIN mappings ON siteid = child_site_id order by siteid asc limit 10 offset 0

Any other way i can do this to avoid SQL injection.

Solution

  • Do something like this and concatenate it:

    List<String> allowedSortableColumns = Arrays.asList(new String[]{"siteid", "technology", "address"})
if(! allowedSortableColumns.contains(sortByColumn)){
   throw new RuntimeException("Cannot sort by: " + sortByColumn);
}
// Continue here and it's safe to concatenate sortByColumn...

    You can do sanitization and other stuff, but this should work in your case