java 如何避免“安全性 - 从非常量字符串生成准备好的语句” FindBugs 警告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10500305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid "Security - A prepared statement is generated from a nonconstant String" FindBugs Warning
提问by ederribeiro
I am working on a project that has a piece of code like the one below:
我正在开发一个项目，它有一段如下所示的代码：
String sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
PreparedStatement ps = connection.prepareStatement(sql);
Is there any way that I can change this code so that FindBugs stop giving me a "Security - A prepared statement is generated from a nonconstant String" warning ?
有什么方法可以更改此代码，以便 FindBugs 停止向我发出“安全性 - 从非常量字符串生成准备好的语句”警告？
Please assume that this code is safe regarding SQL INJECTION since I can control elsewhere in the code the possible values for "tableName" and "columnName" (they do not come come directly from user input).
请假设此代码对于 SQL 注入是安全的，因为我可以在代码的其他地方控制“tableName”和“columnName”的可能值（它们不直接来自用户输入）。
采纳答案by Kai
Do not concatenate the sqlString by +. You can use
不要sql通过连接字符串+。您可以使用
String sql = String.format("SELECT MAX(%s) FROM %s ", columnName, tableName);
This is slower than concatenating a String so you should initialize this staticthen this is not a problem.
这比连接字符串慢，所以你应该初始化它，static那么这不是问题。
I think using a StringBuilderwill also fix this warning.
我认为使用 aStringBuilder也可以解决这个警告。
Another way you can avoid this warning is to add @SuppressWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")above that string (or the method/or the class).
避免此警告的另一种方法是@SuppressWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")在该字符串（或方法/或类）上方添加。
You could also use a Filter Fileto define rules which should be excluded.
您还可以使用过滤器文件来定义应排除的规则。
回答by Ashish Dadhore
private static final String SQL = "SELECT MAX(?) FROM ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.preparedStatement.setInt(1,columnName);
ps.preparedStatement.setString(2,tableName);
if you are using prepared statement, then in parameter should be a final string and parameters should be added later using setInt, setString methods.
如果您使用的是准备好的语句，则 in 参数应该是最终字符串，并且稍后应该使用 setInt、setString 方法添加参数。
this will resolve the findbug warning.
这将解决 findbug 警告。
回答by Jesse Webb
Try using the following...
尝试使用以下...
private static final String SQL = "SELECT MAX(%s) FROM %s";
And then using a String.format() call when you use it...
然后在使用它时使用 String.format() 调用......
PreparedStatement ps = connection.prepareStatement(String.format(sql,columnName,tableName));
If that doesn't solve the problem, you can always ignore that check; turn it off in your FindBugs configuration.
如果这不能解决问题，您可以随时忽略该检查；在您的 FindBugs 配置中将其关闭。
If that doesn't work (or isn't an option), some IDEs (like IntelliJ) will also let you suprress warnings with either specially formatted comments or annotations.
如果这不起作用（或不是一个选项），一些 IDE（如 IntelliJ）也将允许您使用特殊格式的注释或注释来抑制警告。
回答by nickolaysus
Neither String.format nor StringBuilder (or StringBuffer) helped me.
String.format 和 StringBuilder（或 StringBuffer）都没有帮助我。
Solution was "prepareStatement" isolation:
解决方案是“prepareStatement”隔离：
private PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {
return conn.prepareStatement(sql);
}
回答by Anonymous Coward
It is possible to use concatenation to create your String. Doing so does not cause the Security warning. And is preferrable for clarity's sake when dealing with long SQL statements which would be better written split across many lines
可以使用连接来创建您的字符串。这样做不会导致安全警告。为了清楚起见，最好在处理长 SQL 语句时最好将其拆分成多行
Using variables to construct the String is what causes the security warning.
使用变量构造字符串是导致安全警告的原因。
This will cause the warning :
这将导致警告：
String columnName = getName();
String tableName = getTableName();
final String sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
PreparedStatement ps = connection.prepareStatement(sql);
This will not not work :
这行不通：
String columnName = getName();
String tableName = getTableName();
final String sql = "SELECT MAX(" + "?" + ")" +
"FROM " + "?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, columnName);
ps.setString(2, tableName);
It does not work because prepared statements only allow parameters to be bound for "values" bits of the SQL statement.
它不起作用，因为准备好的语句只允许为 SQL 语句的“值”位绑定参数。
This is the solution that works :
这是有效的解决方案：
private static final boolean USE_TEST_TABLE = true;
private static final boolean USE_RESTRICTED_COL = true;
private static final String TEST_TABLE = "CLIENT_TEST";
private static final String PROD_TABLE = "CLIENT";
private static final String RESTRICTED_COL ="AGE_COLLATED";
private static final String UNRESTRICTED_COL ="AGE";
....................
final String sql = "SELECT MAX(" +
( USE_RESTRICTED_COL ? RESTRICTED_COL : UNRESTRICTED_COL ) + ")" +
"FROM " +
( USE_TEST_TABLE ? TEST_TABLE : PROD_TABLE );
PreparedStatement ps = connectComun.prepareStatement(sql);
But it only works if you have to chose between two tables whose names are known at compile time. You could use compounded ternary operators for more than 2 cases but then it becomes unreadable.
但它仅适用于您必须在两个名称在编译时已知的表之间进行选择的情况。您可以在 2 种以上的情况下使用复合三元运算符，但随后它变得不可读。
The 1st case might be a security issue if getName() or getTableName() gets the name from untrusted sources.
如果 getName() 或 getTableName() 从不受信任的来源获取名称，则第一种情况可能是安全问题。
It is quite possible to construct a secure SQL statement using variables if those variables have been previously verified. This is your case, but FindBugs can't figure it out. Findbugs is not able to know what sources are trusted or not.
如果先前已验证这些变量，则很可能使用变量来构造安全的 SQL 语句。这是您的情况，但 FindBugs 无法弄清楚。Findbugs 无法知道哪些来源可信或不可信。
But if you must use a column or table name from user or untrusted input then there is no way around it. You have to verify yourself such string and ignore the Findbugs warning with any of the methods proposed in other answers.
但是，如果您必须使用来自用户或不受信任的输入的列名或表名，则没有办法绕过它。您必须验证自己这样的字符串，并使用其他答案中提出的任何方法忽略 Findbugs 警告。
Conclusion: There is no perfect solution for the general case of this question.
结论：对于这个问题的一般情况，没有完美的解决方案。
回答by Praveen Agrawal
StringBuilder sql = new StringBuilder();
sql.append("SELECT MAX(")
.append(columnName)
.append(") FROM ")
.append(tableName);
PreparedStatement ps = connection.prepareStatement(sql);
ps.execute();
回答by Manuel Romeiro
If you make sure that is no possibility of SQL injection, use the SuppressFBWarningsannotation on the method:
如果您确定没有 SQL 注入的可能性，请在方法上使用SuppressFBWarnings注释：
@edu.umd.cs.findbugs.annotations.SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
