PreparedStatement與Statement,preparedstatement

來源:互聯網
上載者:User

PreparedStatement與Statement,preparedstatement

PreparedStatement是用來執行SQL查詢語句的API之一,Java提供了 Statement、PreparedStatement 和 CallableStatement三種方式來執行查詢語句,其中 Statement 用於通用查詢, PreparedStatement 用於執行參數化查詢,而 CallableStatement則是用於預存程序。同時PreparedStatement還經常會在Java面試被提及,譬如:Statement與PreparedStatement的區別以及如何避免SQL注入式攻擊?這篇教程中我們會討論為什麼要用PreparedStatement?使用PreparedStatement有什麼樣的優勢?PreparedStatement又是如何避免SQL注入攻擊的?

PreparedStatement是什嗎?
PreparedStatement是java.sql包下面的一個介面,用來執行SQL語句查詢,通過調用connection.preparedStatement(sql)方法可以獲得PreparedStatment對象。資料庫系統會對sql語句進行先行編譯處理(如果JDBC驅動支援的話),預先處理語句將被預先編譯好,這條先行編譯的sql查詢語句能在將來的查詢中重用,這樣一來,它比Statement對象產生的查詢速度更快。下面是一個例子:

public class PreparedStmtExample {    public static void main(String args[]) throws SQLException {        Connection conn = DriverManager.getConnection("mysql:\\localhost:1520", "root", "root");        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");        preStatement.setString(1, "Citibank");        ResultSet result = preStatement.executeQuery();        while(result.next()){            System.out.println("Loan Type: " + result.getString("loan_type"));        }           }} Output:Loan Type: Personal LoanLoan Type: Auto LoanLoan Type: Home LoanLoan Type: Gold Loan
這個例子中,如果還是用 PreparedStatement 做同樣的查詢,哪怕參數值不一樣,比如:”Standard Chated” 或者”HSBC”作為參數值,資料庫系統還是會去調用之前編譯器編譯好的執行語句(系統庫系統初次會對查詢語句做最大的效能最佳化)。預設會返回”TYPE_FORWARD_ONLY”類型的結果集( ResultSet ),當然你也可以使用preparedstatment()的重載方法返回不同類型的結果集。

預先處理語句的優勢

PreparedStatement提供了諸多好處,企業級應用開發中強烈推薦使用PreparedStatement來做SQL查詢,下面列出PreparedStatement幾點優勢:

PreparedStatement可以寫動態參數化的查詢

用PreparedStatement你可以寫帶參數的sql查詢語句,通過使用相同的sql語句和不同的參數值來做查詢比建立一個不同的查詢語句要好,下面是一個參數化查詢:

SELECT interest_rate FROM loan WHERE loan_type=?
現在你可以使用任何一種loan類型如:”personal loan”,”home loan” 或者”gold loan”來查詢,這個例子叫做參數化查詢,因為它可以用不同的參數調用它,這裡的”?”就是參數的預留位置。

PreparedStatement比 Statement 更快

使用 PreparedStatement 最重要的一點好處是它擁有更佳的效能優勢,SQL語句會先行編譯在資料庫系統中。執行計畫同樣會被緩衝起來,它允許資料庫做參數化查詢。使用預先處理語句比普通的查詢更快,因為它做的工作更少(資料庫對SQL語句的分析,編譯,最佳化已經在第一次查詢前完成了)。為了減少資料庫的負載,生產環境中德JDBC代碼你應該總是使用PreparedStatement 。值得注意的一點是:為了獲得效能上的優勢,應該使用參數化sql查詢而不是字串追加的方式。下面兩個SELECT 查詢,第一個SELECT查詢就沒有任何效能優勢。
SQL Query 1:字串追加形式的PreparedStatement

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

SQL Query 2:使用參數化查詢的PreparedStatement

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

第二個查詢就是正確使用PreparedStatement的查詢,它比SQL1能獲得更好的效能。

PreparedStatement可以防止SQL注入式攻擊
如果你是做Java web應用開發的,那麼必須熟悉那聲名狼藉的SQL注入式攻擊。去年Sony就遭受了SQL注入攻擊,被盜用了一些Sony play station(PS機)使用者的資料。在SQL注入攻擊裡,惡意使用者通過SQL中繼資料綁定輸入,比如:某個網站的登入驗證SQL查詢代碼為:

strSQL = "SELECT * FROM users WHERE name = '" + userName + "' and pw = '"+ passWord +"';"

惡意填入:

userName = "1' OR '1'='1";
passWord = "1' OR '1'='1";
那麼最終SQL語句變成了:

strSQL = "SELECT * FROM users WHERE name = '1' OR '1'='1' and pw = '1' OR '1'='1';"
因為WHERE條件恒為真,這就相當於執行:

strSQL = "SELECT * FROM users;"
因此可以達到無帳號密碼亦可登入網站。如果惡意使用者要是更壞一點,使用者填入:

strSQL = "SELECT * FROM users;"
SQL語句變成了:

strSQL = "SELECT * FROM users WHERE name = 'any_value' and pw = ''; DROP TABLE users"

這樣一來,雖然沒有登入,但是資料表都被刪除了。

然而使用PreparedStatement的參數化的查詢可以阻止大部分的SQL注入。在使用參數化查詢的情況下,資料庫系統(eg:MySQL)不會將參數的內容視為SQL指令的一部分來處理,而是在資料庫完成SQL指令的編譯後,才套用參數運行,因此就算參數中含有破壞性的指令,也不會被資料庫所運行。

補充:避免SQL注入的第二種方式:
在組合SQL字串的時候,先對所傳入的參數做字元取代(將單引號字元取代為連續2個單引號字元,因為連續2個單引號字元在SQL資料庫中會視為字元中的一個單引號字元,譬如:

strSQL = "SELECT * FROM users WHERE name = '" + userName + "';"
傳入字串:
userName  = " 1' OR 1=1 "
把userName做字元替換後變成:
userName = " 1'' OR 1=1"
最後產生的SQL查詢語句為:

strSQL = "SELECT * FROM users WHERE name = '1'' OR 1=1'

這樣資料庫就會去系統尋找name為“1′ ‘ OR 1=1”的記錄,而避免了SQL注入。

比起淩亂的字串追加似的查詢,PreparedStatement查詢可讀性更好、更安全。

PreparedStatement的局限性

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

SELECT * FROM loan WHERE loan_type IN (?)
preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");
那如何解決這個問題呢?請你繼續關注本部落格,下期告訴你答案。

不算總結的總結
關於PreparedStatement介面,需要重點記住的是:
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.

以上就是為什麼要使用PreparedStatement的全部理由,不過你仍然可以使用Statement對象用來做做測試。但是在生產環境下你一定要考慮使用 PreparedStatement 。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.