PreparedStatement and Statement, preparedstatement

PreparedStatement and Statement, preparedstatement

PreparedStatement is one of the APIs used to execute SQL query statements. Java provides Statement, PreparedStatement, and CallableStatement to execute query statements. Statement is used for general queries and PreparedStatement is used to execute parameterized queries, callableStatement is used for stored procedures. At the same time, PreparedStatement is often mentioned during Java interviews. For example, the difference between Statement and PreparedStatement and how to avoid SQL injection attacks? In this tutorial, we will discuss why PreparedStatement is used? What are the advantages of PreparedStatement? How does PreparedStatement avoid SQL injection attacks?

What is PreparedStatement?
PreparedStatement is an interface in the java. SQL package to execute SQL statement queries. You can obtain the PreparedStatment object by calling the connection. preparedStatement (SQL) method. The database system will pre-compile the SQL statements (if the JDBC driver supports this), and the pre-compiled statements will be prepared in advance, this pre-compiled SQL query Statement can be reused in future queries. As a result, it is faster than the query generated by the Statement object. The following is an example:

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

In this example, if PreparedStatement is used for the same query, even if the parameter values are different, for example, "Standard Chated" or "HSBC" as the parameter values, the database system will still call the previously compiled execution statements of the compiler (the system database system will optimize the query statement for the first time ). By default, "TYPE_FORWARD_ONLY" type result set (ResultSet) is returned. Of course, you can also use the preparedstatment () overload method to return different types of result sets.

Advantages of preprocessing statements

PreparedStatement provides many benefits. In enterprise application development, PreparedStatement is strongly recommended for SQL queries. The following lists the advantages of PreparedStatement:

PreparedStatement allows you to write dynamic parameterized queries.

With PreparedStatement, you can write SQL query statements with parameters. It is better to use the same SQL statement and different parameter values for query than to create a different query statement. Below is a parameterized query:

SELECT interest_rate FROM loan WHERE loan_type =?
Now you can use any loan type such as "personal loan", "home loan", or "gold loan" to query. This example is called parameterized query, because it can be called with different parameters, here "?" Is the placeholder of the parameter.

PreparedStatement is faster than Statement

One of the most important advantages of using PreparedStatement is that it has better performance advantages. SQL statements are pre-compiled in the database system. The execution plan is also cached, which allows the database to perform parameterized queries. The use of pre-processing statements is faster than normal queries because it does less work (the database has completed the analysis, compilation, and optimization of SQL statements before the first query ). To reduce database load, you should always use PreparedStatement for the JDBC code in the production environment. It is worth noting that in order to gain performance advantages, parameterized SQL queries should be used instead of string appending. In the following two SELECT queries, the first SELECT query has no performance advantages.
SQL Query 1: PreparedStatement in string appending form

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

SQL Query 2: PreparedStatement with parameterized Query

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

The second query is to use the PreparedStatement query correctly, which provides better performance than sql1.

PreparedStatement can prevent SQL injection attacks
If you are developing Java web applications, you must be familiar with the notorious SQL injection attacks. Last year, Sony suffered an SQL injection attack and stolen data from some Sony play station (PS) users. In the SQL Injection Attack, malicious users bind input via SQL metadata. For example, the SQL query code for Logon verification of a website is:

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

Malicious filling:

UserName = "1 'OR '1' = '1 ";
PassWord = "1 'OR '1' = '1 ";
Then the final SQL statement becomes:

StrSQL = "SELECT * FROM users WHERE name = '1' OR '1' = '1' and pw = '1' OR '1' = '1 ';"
Because the WHERE condition is always true, this is equivalent to executing:

StrSQL = "SELECT * FROM users ;"
Therefore, you can log on to the website without an account or password. If a malicious user is worse, enter the following information:

StrSQL = "SELECT * FROM users ;"
The SQL statement is changed:

StrSQL = "SELECT * FROM users WHERE name = 'any _ value' and pw =''; drop table users"

In this way, data tables are deleted even if they are not logged on.

However, parameterized queries using PreparedStatement can prevent most SQL injections. When parameterized query is used, the database system (eg: MySQL) does not regard the parameter content as part of the SQL command, but after the database completes the compilation of the SQL command, therefore, even if the parameters contain destructive commands, they will not be run by the database.

Supplement: the second method to avoid SQL injection:
When combining SQL strings, replace the passed parameters with characters (replace single quotes with two single quotes in a row, because two single quotes in a row are considered as one single quotation mark character in the SQL database, for example:

StrSQL = "SELECT * FROM users WHERE name = '" + userName + "';"
Input string:
UserName = "1 'OR 1 = 1"
Replace userName with the following characters:
UserName = "1'' OR 1 = 1"
The final SQL query statement is:

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

In this way, the database will go to the system to find records with the name "1'' OR 1 = 1 ", avoiding SQL injection.

PreparedStatement queries are more readable and safer than messy string append queries.

Limitations of PreparedStatement

Although PreparedStatement is very practical, it still has certain restrictions.
1. To prevent SQL injection attacks, PreparedStatement does not allow a placeholder (?) When there are multiple values, this problem becomes tricky when the ** IN ** clause query is executed. If PreparedStatement is used in the following SQL query, no results will be returned.

SELECT * FROM loan WHERE loan_type IN (?)
PreparedSatement. setString (1, "'Personal' loan', 'home loan', 'Gold loan '");
So how can we solve this problem? Please continue to follow this blog and tell you the answer in the next issue.

Summary
For the PreparedStatement interface, note the following:
1. PreparedStatement can be used to write parameterized queries, providing better performance than Statement.
2. For PreparedStatement, the database can use the compiled and defined execution plan, which is faster than normal queries.
3. PreparedStatement can prevent common SQL injection attacks.
4. PreparedStatement can be used to write dynamic query statements.
5. PreparedStatement is associated with the java. SQL. Connection object. Once you close the connection, PreparedStatement cannot be used.
6. "?" It is called a placeholder.
7. The PreparedStatement query returns the FORWARD_ONLY ResultSet by default. You can only move the result set cursor in one direction. Of course, you can also set it to another type value, for example, "CONCUR_READ_ONLY ".
8. the JDBC driver for pre-compiled SQL queries is not supported. during prepareStatement (SQL), it does not send SQL query statements to the database for preprocessing, but waits until the query action is executed (when the executeQuery () method is called) the query Statement is sent to a database, which is the same as Statement.
9. The index position of the placeholder starts from 1 rather than 0. If it is filled with 0, * java. SQL. SQLException invalid column index * is abnormal. Therefore, if PreparedStatement has two placeholders, the index of the first parameter is 1 and that of the second parameter is 2.

The above are all the reasons for using PreparedStatement, but you can still use the Statement object for testing. However, in the production environment, you must consider using PreparedStatement.