使用Order By的准备好的语句来防止SQL注入Java [英] Using prepared statement for Order by to prevent SQL injection java

问题描述

我有一个查询，其中有condition，order by和limit.我正在使用准备好的语句来设置where条件和限制.目前，我正在使用字符串追加命令来导致SQL注入漏洞.

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.

我不能像这样使用设置字符串进行排序order by ? ?如果我这样做，则SQL Order功能将无法正常工作.

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

示例查询:

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

我可以通过其他任何方式来避免SQL注入.

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

推荐答案

执行以下操作并将其连接起来:

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

这篇关于使用Order By的准备好的语句来防止SQL注入Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！