CODEWITHSUNDEEP
javaprepared-statementsql-injection
PGS
PGS

Reputation: 1194

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.

Upvotes: 4

Views: 4249

Answers (3)

Sabir Khan
Sabir Khan

Reputation: 10142

PreparedStatement SetMethods are applicable only for database table column values ( WHERE clause values for SELECT & DELETE SQL statements and also includes to be updated or inserted column values for UPDATE & INSERT statements).

So this means that following usual SQL parts like - column names , table schema , table name , ORDER BY , GROUP BY , Pagination ( LIMIT , OFFSET , FETCH FIRST etc ) can't be parametrized via question mark symbol ( ? ) of java.sql.PreparedStatement

Directly appending values from user input to query will cause security flaw and only way out is to examine input values from your list of valid values for these sections of SQL.

e.g. what table name you are expecting ? , what schema should be for a particular table ? , what columns are present in a particular table ? , is page number an integer ?, is LIMIT value an integer ? values after column names for ORDER BY clause should only be ASC or DESC & nothing else etc etc.

You can either dynamically populate these valid values or hard code in systems.

If invalid value is passed then either we don't execute query ( & log it as error ) or replace invalid value with useful defaults and execute query.

Upvotes: 3

DJDaveMark
DJDaveMark

Reputation: 2855

You should use a whitelist of possible columns:

String[] cols = {"siteid", "technology", "address", "state", "status"};

then use an index when referring to columns with the user:

int colFromUser = Integer.parseInt(request.getParameter("sortCol"));

and validate the index before appending the column name to the order by list:

if(colFromUser < 0 || colFromUser >= cols.length) {
    throw new IllegalArgumentException("Invalid column");
}
String col = cols[colFromUser];
query.append(col);

Although if it was me, I'd be using something like Hibernate criteria to do this job:

Criteria c2 = session.createCriteria(Supplier.class);
c2.addOrder(Order.desc("name"));

Upvotes: 1

mikeb
mikeb

Reputation: 11287

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

Upvotes: 1

Related Questions