java避免使用orderby,使用Order By的准备好的语句来防止SQL注入Java

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

    推荐阅读