java避免使用orderby,使用Order By的准备好的语句来防止SQL注入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.

解决方案

Do something like this and concatenate it:

List 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