PreparedStatement

PreparedStatement

參考資料:JDBC為什麼要使用PreparedStatement而不是Statement(http://www.importnew.com/5006.html)

Java 提供了三種API來執行查詢語句

Statement - 通用查詢

PreparedStatement - 參數化查詢

CallableStatement - 存儲過程

PreparedStatement 是什麼?

import java.sql.PreparedStatement;

使用PreparedStatement，DBMS會對Query 進行預編譯處理，將語句的部分儲存起來，以後雖然輸入的參數不同，一樣可以拿出來用。這樣一來，它比Statement 的查詢速度更快。

參數化查詢

SELECT * from table where field = ?

?代表參數的位置，因為可以用不同的參數調用它。

PreparedStatement 比 Statement 更快

PreparedStatement 擁有更佳的性能優勢，SQL語句會在DBMS進行預編譯，並且將執行計畫緩存起來。後續可以用參數化查詢，而不是字串追加。

(X)
String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=" + loanType);

(O)
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);

PreparedStatement 可以防止 SQL注入攻擊

在使用參數化查詢的情況下，DBMS是拿編譯完的語句加上參數去運行。 不會將參數視為SQL Query的一部分。因此就算參數含有破壞性指令，也不會被DBMS所運行。

另外一種防止 SQL注入攻擊 的方式

在組SQL Query的時候，先對使用者傳入的參數，單引號都取代為連續2個單引號，以此去避免SQL 注入。

可讀性

比起凌亂的字串追加，利用參數去提升可讀性。

PreparedStatement的局限性

儘管PreparedStatement非常實用，但是它仍有一定的限制。 1.為了防止SQL注入攻擊，PreparedStatement不允許一個佔位符（？）有多個值，在執行有IN子句查詢的時候這個問題變得棘手起來。下面這個SQL查詢使用PreparedStatement就不會返回任何結果

SELECT * FROM loan WHERE loan_type IN (?)
preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");

部分總結

1. PreparedStatement可以寫參數化查詢，比Statement能獲得更好的性能。

2. 對於PreparedStatement來說，數據庫可以使用已經編譯過及定義好的執行計劃，這種預處理語句查詢比普通的查詢運行速度更快。

3. PreparedStatement可以阻止常見的SQL注入式攻擊。

4. PreparedStatement可以寫動態查詢語句

5. PreparedStatement與java.sql.Connection對像是關聯的，一旦你關閉了connection，PreparedStatement也沒法使用了。

6. “?”叫做佔位符。

7. PreparedStatement查詢默認返回FORWARD_ONLY的ResultSet，你只能往一個方向移動結果集的游標。當然你還可以設定為其他類型的值如：”CONCUR_READ_ONLY”。

   8.不支持預編譯SQL查詢的JDBC驅動，在調用connection.prepareStatement(sql)的時候，它不會把SQL查詢語句發送給數據庫做預處理，而是等到執行查詢動作的時候（調用executeQuery()方法時）才把查詢語句發送個數據庫，這種情況和使用Statement是一樣的。

   9.佔位符的索引位置從1開始而不是0，如果填入0會導致java.sql.SQLException invalid column index異常。所以如果PreparedStatement有兩個佔位符，那麼第一個參數的索引時1，第二個參數的索引是2。

JDBC

目前有提供下列這兩種 Template JdbcTemplate NamedParameterJdbcTemplate 這兩者都是使用 PreparedStatement 去執行查詢，這些都是防止 SQL Injection的措施，在SQLInjection的處理上沒有區別。

NamedParameterJdbcTemplate 的 範例

where .. in

public List<SmsDto> selectDataBySmsId(String... smsId) {
    String sql = " SELECT * FROM " + SmsDto.TABLE_NAME +
            " WHERE sms_id in (:sms_id) ";
    
    //不吃Array，要先轉換成List
    List<String> list = new ArrayList<>(Arrays.asList(smsId));

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("sms_id", list);

    return this.getNamedParameterJdbcTemplate().query(sql, in, new BeanPropertyRowMapper<>(SmsDto.class));
}

where many column

參考資料:(https://stackoverflow.com/questions/4164949/how-to-protect-against-sql-injection-when-the-where-clause-is-built-dynamically)

public List<SmsDto> test1(String smsId,String smsName) {
    String sql = " SELECT * FROM " + SmsDto.TABLE_NAME +
            " WHERE coalesce(:sms_id, sms_id) = sms_id " +
            " and coalesce(:sms_name, sms_name) = sms_name ";

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("sms_id", smsId);
    in.addValue("sms_name", smsName);

    return this.getNamedParameterJdbcTemplate().query(sql, in, new BeanPropertyRowMapper<>(SmsDto.class));
}

注意:參數必須為null才會被 coalesce() 跳過，空字串會被吃進去