使用Order by的prepared语句防止SQL注入

Question

我对where条件、order by和limit有一个查询。我使用准备好的语句来设置where条件和限制。当前，我使用字符串append作为order，从而导致SQL注入漏洞。

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

Answer 1

这样做并将其串联起来：

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...

你可以做卫生和其他事情，但这应该适用于你的情况