java - 连接查询字符串时出现 SQL 语法错误

Question

try(Connection dbConnection = DBConnectionManager.getIntakeConnection();
PreparedStatement preparedStmtSetMaxStrikeId = dbConnection.prepareStatement(
    "SELECT MAX(strike_id) FROM strike WHERE 'SELECT p.party_type_id,"
    + "p.csa_score,p.party_tn,p.rec_create_date,"
    + "s.strike_id, s.strike_date, s.strike_level, s.strike_status,
       s.appealable,s.appeal_status,s.rec_change_date,s.event_id,
       s.is_email_processed,s.policy_id"
    + "FROM strike s "
    + "INNER JOIN parties p"
    + "ON p.party_id = s.party_id"
    + "WHERE p.account ='"+appealStatus.getSubscriberId()
    +"'AND strike_status = '"+OCIRISConstants.STRIKE_STATUS_ACTIVE+"' ");)

错误如下。
错误中的整数是订阅者 ID。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0957936101205'AND strike_status = 'ACTIVE'' at line 1

Answer 1

错误消息显示:

near '0957936101205'AND strike_status = 'ACTIVE'' at line 1

它实际上标识了 ' 后面的 0，它结束了从第一行开始的文本文字，因为这是错误的 SQL:

SELECT MAX(strike_id) FROM strike WHERE '...'0957936101205'AND strike_status = 'ACTIVE'
--                                          ^^ BAD

下面是带有注释的代码:

try(Connection dbConnection = DBConnectionManager.getIntakeConnection();
PreparedStatement preparedStmtSetMaxStrikeId = dbConnection.prepareStatement(
      "SELECT MAX(strike_id) FROM strike WHERE 'SELECT p.party_type_id,"
//                                             ^ What is this? Even without ' it makes no sense
//                                             ^ But it STARTS A TEXT LITERAL
    + "p.csa_score,p.party_tn,p.rec_create_date,"
    + "s.strike_id, s.strike_date, s.strike_level, s.strike_status, s.appealable,s.appeal_status,s.rec_change_date,s.event_id,s.is_email_processed,s.policy_id"
    + "FROM strike s "
//     ^ Missing space, but it's in a text literal so doesn't matter
    + "INNER JOIN parties p"
    + "ON p.party_id = s.party_id"
//     ^ Missing space, but it's in a text literal so doesn't matter
    + "WHERE p.account ='"+appealStatus.getSubscriberId()
//     ^ Missing space, but it's in a text literal so doesn't matter
//                      ^ END TEXT LITERAL from first line
//                         ^ error complains about inserted value 0957936101205
    +"'AND strike_status = '"+OCIRISConstants.STRIKE_STATUS_ACTIVE+"' ");)
//    ^ Starts a new text literal
//     ^ Missing space, but it's in a text literal so doesn't matter
//                         ^ end text literal
//                            ^ would complain about inserted value ACTIVE
//                                                                  ^ Dangling '

此外，您不应该使用字符串连接来构建 SQL，因为它会导致语法错误并使您容易受到 SQL Injection 的影响。攻击，允许黑客窃取您的数据并删除您的表。

假设最初的 SELECT MAX( ... WHERE ' 存在错误，这里是一个清理后的版本，为了清晰起见，进行了格式化:

String sql = "SELECT p.party_type_id, p.csa_score, p.party_tn, p.rec_create_date" +
                  ", s.strike_id, s.strike_date, s.strike_level, s.strike_status" +
                  ", s.appealable, s.appeal_status, s.rec_change_date, s.event_id" +
                  ", s.is_email_processed, s.policy_id" +
              " FROM strike s" +
             " INNER JOIN parties p ON p.party_id = s.party_id" +
             " WHERE p.account = ?" +
               " AND strike_status = ?";
try (Connection conn = DBConnectionManager.getIntakeConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, appealStatus.getSubscriberId());
    stmt.setString(2, OCIRISConstants.STRIKE_STATUS_ACTIVE);