PreparedStatement and statement

PreparedStatement is one of the APIs used to execute SQL query statements, and Java provides Statement, PreparedStatement, and CallableStatement three ways to execute query statements, where Statement is used for generic queries, PreparedStatement is used to perform parameterized queries, and callablestatement is used for stored procedures. PreparedStatement is also often mentioned in the Java interview, such as the difference between statement and PreparedStatement and how to avoid SQL injection attacks? In this tutorial we will discuss why use PreparedStatement? What are the advantages of using PreparedStatement? How does PreparedStatement avoid SQL injection attacks?

what is PreparedStatement?
PreparedStatement is an interface under the java.sql package. Used to execute SQL statement queries, the Preparedstatment object can be obtained by calling the Connection.preparedstatement (SQL) method. The database system pre-compiles the SQL statement (if supported by the JDBC driver), and the preprocessed statement is pre-compiled, and this precompiled SQL query can be reused in future queries, which makes it faster than the query generated by the statement object. Here's 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 the same query is used with PreparedStatement, even if the parameter value is different, for example: "Standard chated" or "HSBC" as the parameter value, The database system will still call the previous compiler to compile the execution of the statement (System library system for the first time the query statement to do the maximum performance optimization). The default is to return a result set of type type_forward_only (ResultSet), but you can also return different types of result sets using the overloaded methods of Preparedstatment ().

Advantages of preprocessing statements

PreparedStatement offers a number of benefits, and it is highly recommended to use PreparedStatement for SQL queries in enterprise application development, listed below PreparedStatement several advantages:

PreparedStatement can write dynamically parameterized queries

With PreparedStatement you can write SQL query statements with parameters, by using the same SQL statement and different parameter values to make a query than to create a different query statement better, here is a parameterized query:

SELECT interest_rate from loan WHERE loan_type=?
Now you can use any kind of 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's "?" is the placeholder for the parameter.

PreparedStatement faster than Statement

The most important advantage of using PreparedStatement is that it has a better performance advantage, and SQL statements are precompiled in the database system. The execution plan is also cached, which allows the database to make parameterized queries. Using a preprocessing statement is faster than a normal query because it does less work (database parsing of SQL statements, compilation, optimization already done before the first query). In order to reduce the load on the database, you should always use PreparedStatement for the German JDBC code in the production environment. It is important to note that in order to gain a performance advantage, you should use parameterized SQL queries instead of string append methods. With the following two select queries, the first select query has no performance benefits.
SQL Query 1: PreparedStatement of string Append form

String Loantype = Getloantype ();
PreparedStatement prestmt = conn.preparestatement ("Select banks from loan where loan_type=" + loantype);

SQL Query 2: PreparedStatement with parameterized queries

PreparedStatement prestmt = conn.preparestatement ("Select banks from loan where loan_type=?");
Prestmt.setstring (1,loantype);

The second query is the correct use of the PreparedStatement query, which can achieve better performance than SQL1.

PreparedStatement to prevent SQL injection attacks
If you are developing Java Web applications, you must be familiar with the infamous SQL injection attack. Sony suffered a SQL injection attack last year, stealing data from a number of Sony play station (PS) users. In SQL injection attacks, malicious users enter through SQL metadata binding, such as: A Web site's login verification SQL query code:

strSQL = "SELECT * from users WHERE name = '" + userName + "' and pw = '" + PassWord + "';"

Malicious filling in:

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 where conditions are constant, this is equivalent to executing:

strSQL = "SELECT * from Users;"
So you can access the website without an account password. If a malicious user is worse, the user fills in:

strSQL = "SELECT * from Users;"
The SQL statement becomes:

strSQL = "SELECT * from users WHERE name = ' Any_value ' and pw = '; DROP TABLE Users "

This way, the data tables are deleted, although they are not logged in.

However, a parameterized query using PreparedStatement can block most SQL injections. In the case of parameterized queries, the database system (EG:MYSQL) does not treat the contents of the parameters as part of the SQL instruction, but only runs the parameters after the database has completed compiling the SQL instructions, so that even if the parameters contain destructive instructions, they will not be run by the database.

Add: The second way to avoid SQL injection:
When combining the SQL string, first replace the passed-in parameter with the single quote character (2 consecutive single quote characters, because 2 single quote characters in the SQL database are treated as a single quote character in the character, for example:

strsql = "SELECT * from users WHERE name = '" + userName + "';"
incoming string:
username  = "1 ' OR 1=1"
Replace the userName character with the following:
username = "1" OR 1=1 "
The last SQL query statement generated is:

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

The database will then go to the system to find records named "1′ ' OR 1=1" and avoid SQL injection.

The PreparedStatement query is more readable and more secure than a messy string appended to the query.

Limitations of PreparedStatement

1. To prevent SQL injection attacks, PreparedStatement does not allow a placeholder (? There are multiple values, and this problem becomes tricky when you execute a query with a **in** clause. The following SQL query uses PreparedStatement to not return any results

SELECT * from loan WHERE Loan_type in (?)
Preparedsatement.setstring (1, "' Personal loan ', ' home loan ', ' gold loan ');
So how do we solve this problem? Please continue to follow this blog, the next issue to tell you the answer.

not a summary summary
With regard to the PreparedStatement interface, it is important to remember that:
1. PreparedStatement can write parameterized queries, which can achieve better performance than statement.
2. For PreparedStatement, a database can use a compiled and well-defined execution plan, which is faster than a normal query.
3. PreparedStatement can block common SQL injection attacks.
4. PreparedStatement can write dynamic query statements
5. PreparedStatement is associated with the Java.sql.Connection object and cannot be used once you have closed the connection,preparedstatement.
6. "?" is called a placeholder.
7. PreparedStatement query returns FORWARD_ONLY's resultset by default, you can only move the result set cursor in one Direction. Of course you can also set other types of values such as: "Concur_read_only".
8. The JDBC driver for precompiled SQL queries is not supported, and it does not send SQL query statements to the database for preprocessing when calling Connection.preparestatement (SQL). Instead, it waits for the query to be executed (when the ExecuteQuery () method is called) to send the query to a database, which is the same as using statement.
9. The index position of the placeholder starts at 1 instead of 0, and if filled in 0 results in an *java.sql.sqlexception invalid column index* exception. So if PreparedStatement has two placeholders, then the index of the first parameter is 1, and the second parameter is indexed at 2.

These are all the reasons why you should use PreparedStatement, but you can still use the statement object to do the testing. But in the production environment you must consider using PreparedStatement.

PreparedStatement and statement