从搜索表单动态构建WHERE子句时，如何防止SQL注入? [英] How to protect against SQL injection when the WHERE clause is built dynamically from search form?

问题描述

我知道保护Java中的SQL查询免于SQL注入的唯一正确方法是使用PreparedStatements.

I know that the only really correct way to protect SQL queries against SQL injection in Java is using PreparedStatements.

但是，这样的语句要求基本结构(选定的属性，联接的表，WHERE条件的结构)不会改变.

However, such a statement requires that the basic structure (selected attributes, joined tables, the structure of the WHERE condition) will not vary.

我在这里有一个JSP应用程序，其中包含带有大约十二个字段的搜索表单.但是用户不必全部填写-只需填写他需要的内容即可.因此，我的WHERE条件每次都不同.

I have here a JSP application that contains a search form with about a dozen fields. But the user does not have to fill in all of them - just the one he needs. Thus my WHERE condition is different every time.

我该怎么做才能仍然防止SQL注入?
转义用户提供的值?写一个包装器类，每次都构造一个PreparedStatement?还是其他?

What should I do to still prevent SQL injection?
Escape the user-supplied values? Write a wrapper class that builds a PreparedStatement each time? Or something else?

数据库是PostgreSQL 8.4，但我希望使用通用解决方案.

The database is PostgreSQL 8.4, but I would prefer a general solution.

非常感谢.

推荐答案

您是否看过JDBC

Have you seen the JDBC NamedParameterJDBCTemplate ?

NamedParameterJdbcTemplate类 增加了对JDBC编程的支持 使用命名参数的语句(如 反对对JDBC语句进行编程 仅使用经典占位符('?') 争论.

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters (as opposed to programming JDBC statements using only classic placeholder ('?') arguments.

您可以执行以下操作:

String sql = "select count(0) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);

并动态构建查询字符串，然后类似地构建SqlParameterSource.

and build your query string dynamically, and then build your SqlParameterSource similarly.

这篇关于从搜索表单动态构建WHERE子句时，如何防止SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！