SQL 注入，用于混合漏洞和非漏洞查询数组

Question

以下持久代码存在SQL漏洞。 strSetStatement[1]、strSetStatement[2] 和 strSetStatement[6] 基于 genSetStatement 方法发送设置参数。这可能是不受信任的数据。我面临着修复这种简单查询和参数化查询的组合。

SQL 注入易受攻击的代码

public class SQLInjection{
public static void main(String[] args) throws SQLException, IOException {
String[] strSetStatement = new String[6];
PreparedStatement m_statement;
String url = "DBURL";
Connection conenction = DriverManager.getConnection(url, "", "");
m_statement = conenction.prepareStatement("SET CHARACTER_SET 'UTF8_FTCS'");
// Setting DB Initial parameters | setting limits for queries 
String strMatch = "100";
String strTime = "100";
String strRank = "2:1";
genSetStatement(strSetStatement, strMatch, strTime, strRank);
boolean logQuery = true;
if (logQuery) {
for (int i = 0; i <= 7; i++)
setlogComment(m_statement, strSetStatement[i]);
}
}
private static void genSetStatement(String[] strSetStatement, String strMatch, String strTime, String strRank) {
strSetStatement[0] = "SET SHOW_MATCHES 'FALSE';";
strSetStatement[1] = "SET MAX_SEARCH_ROWS " + strMatch + ";";
strSetStatement[2] = "SET MAX_EXEC_TIME " + strTime + ";";
strSetStatement[3] = "SET SERVER_REPORT_TIME " + FTSSearchConst.SERVER_REPORT_TIME + ";";
strSetStatement[4] = "SET SEARCH_MEMORY_SIZE " + FTSSearchConst.SEARCH_MEMORY_SIZE + ";";
strSetStatement[5] = "SET THESAURUS_NAME 'FULTEXT';";
strSetStatement[6] = "SET RELEVANCE_METHOD '" + strRank + "';";
}
private static void setlogComment(PreparedStatement stmt, String strSetState) throws SQLException, IOException {

stmt.executeQuery(strSetState); // SQL injected area
}
}

我正在尝试通过在 strSetStatement[] 数组中添加"？"来做下面的事情。 并检查查询在 setlogComment 方法中是否有"？"，但如何识别确切的参数并将变量绑定到它？

strSetStatement[1] = "SET MAX_SEARCH_ROWS " + ? + ";";

private static void setlogComment(PreparedStatement stmt, String strSetState) throws SQLException, IOException {
if(strSetState.contains("?")){
stmt.setString(1, arg1);            
}
stmt.executeQuery(strSetState); 
}

Answer 1

注意：我不确定您要为哪个SQL版本/方言运行这些查询，这应该是一些全文搜索扩展。

以下代码的目的是消除代码片段中检测到的问题，但是，您可能需要适当地更新它，以防出现任何错误。

1. 代码已被反应以摆脱多余的方法/变量。
2. 索引 1、2、6 处的查询已更新，包括外部参数的?。
3. matchRows类型和execTime参数已更改为int
设置
4. PreparedStatement的参数，并在主循环中执行查询。

public class SQLInjection {
public static void main(String[] args) throws SQLException, IOException {
String[] strSetStatement = {
"SET SHOW_MATCHES 'FALSE';",
"SET MAX_SEARCH_ROWS ?;",
"SET MAX_EXEC_TIME ?;",
"SET SERVER_REPORT_TIME " + FTSSearchConst.SERVER_REPORT_TIME + ";",
"SET SEARCH_MEMORY_SIZE " + FTSSearchConst.SEARCH_MEMORY_SIZE + ";",
"SET THESAURUS_NAME 'FULTEXT';",
"SET RELEVANCE_METHOD ?;"
};
Connection connection = DriverManager.getConnection("DBURL", "", "");
PreparedStatement m_statement = connection.prepareStatement("SET CHARACTER_SET 'UTF8_FTCS'");
m_statement.executeQuery();
// Setting DB Initial parameters | setting limits for queries 
int matchRows   = 100;
int execTimeSec = 100;
String strRank  = "2:1";
boolean logQuery = true;
if (logQuery) {
for (int i = 0; i < strSetStatement.length; i++) {
PreparedStatement stmt = connection.prepareStatement(strSetStatement[i]);
if (i == 1) {
stmt.setInt(1, matchRows);
} else if (i == 2) {
stmt.setInt(1, execTimeSec);
} else if (i == 6) {
stmt.setString(1, strRank);
}
stmt.executeQuery();
}
}
}
}