How does preprocessing preparestatement prevent SQL injection vulnerabilities?

, it is a common practice to use Preparestatement to pre-compile and then populate data based on wildcard characters before operating the database, with the benefit of improving execution efficiency and ensuring that SQL injection vulnerabilities are excluded.

Preparestatement pre-compile and prevent SQL injection function

As we all know, in the Java JDBC, there is a preprocessing function, this function is a major advantage is to improve execution speed, especially the operation of the database multiple times, another advantage is to prevent SQL injection, strictly speaking, should be to prevent the vast majority of SQL injection.

The usage is shown on the following side:

String sql= "Update cz_zj_directpayment DP" + "Set Dp.projectid =?" where dp.payid=? " ; Try {    = conn.preparestatement (sql);    Pset_f.setstring (1, inds[j]);    Pset_f.setstring (2, id);    Pset_f.executeupdate (sql_update);} Catch (Exception e) {    //e.printstacktrace ();     Logger.error (E.message ());}

Why is it so handled to prevent SQL injection from improving security? In fact, because the SQL statement has been pre-compiled before the program is run, the SQL statement has been parsed, compiled and optimized by the database, and the corresponding execution plan is cached and allowed to be queried in the form of a parameterized database before the program is run for the first time. When the runtime dynamically passes parameters to preprarestatement, even if the parameter has a sensitive word such as or ' 1=1 ', the database will be treated as a property value of a field and not as a SQL instruction, so that SQL injection works!

Two the difference between statement and PreparedStatement

First of all, what is statement:statement in Java is an important way for Java to perform database operations and to send SQL statements to the database on the basis of a database connection already established. Specific steps:

1. First import java.sql.*; this package.

2. Then load the driver, create the connection, get the implementation object of the connection interface, such as the object name is called Conn.

3. Then use the Conn object to create an instance of the Statement, by: Statement stmt = conn.creatstatement ("SQL statement string");

The Statement object is used to send SQL statements to the database. There are actually three Statement objects, all of which act as an containment for executing SQL statements on a given connection: Statement, PreparedStatement (which inherits from Statement), and CallableStatement (it PreparedStatement inherited). They are all dedicated to sending a specific type of SQL statement: The Statement object is used to execute a simple SQL statement without parameters; The PreparedStatement object is used to execute a precompiled SQL statement with or without parameters; CallableStatement object is used to perform a call to a stored procedure on a database.

In summary, summarized as follows: statement each time the SQL statement executes, the database executes the compilation of the SQL statement, preferably for a situation where only one query is executed and the result is returned, and the efficiency is higher than preparedstatement. There is a risk of SQL injection. PreparedStatement is a precompiled execution. PreparedStatement is more efficient than statement when executing a variable-parameter SQL, because a DBMS that compiles a SQL is certainly more efficient than compiling a single SQL multiple times. Security is better, effectively preventing SQL injection problems. For statements that are repeated repeatedly, use the prepared

Statement efficiency will be a little higher. The Execute SQL statement is available with parameters and supports bulk execution of SQL. Because the cache mechanism is used, the precompiled statement is placed in the cache, and the next time the same SQL statement is executed, it can be removed directly from the cache.

PreparedStatement pstmt  =  con.preparestatement ("UPDATE EMPLOYEES  SET name=?") WHERE ID =? " );p stmt.setstring (1, "John Doe");p Stmt.setint (2, 1);p stmt. Executeupdate ();

Then CallableStatement expands the PreparedStatement interface to invoke the stored procedure, which provides support for input and output parameters, and CallableStatement interface to PreparedStatement interface provides support for SQL queries for input parameters.

PreparedStatement: The database compiles the SQL statements, and the next time the same SQL statement is executed, the database side is no longer precompiled, and the database's buffers are used directly to improve the efficiency of data access (but use it as much as possible). parameter), if the SQL statement executes only once and is no longer reused. From the security point of view, PreparedStatement is to pass the parameter, avoid the SQL injection problem, so security is better.

In development, it is recommended to use PreparedStatement

Iii. what is SQL injection and how to prevent SQL injection?

SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server. Specifically, it is the ability to inject (malicious) SQL commands into the background database engine execution using existing applications, which can be obtained by entering (malicious) SQL statements in a Web form to a database on a Web site that has a security vulnerability, rather than executing the SQL statement as the designer intended.

How to prevent SQL injection, use stored procedures to execute all queries, check the legitimacy of user input, and encrypt data such as the user's login, password, and so on.

Iv. How are the transactions in spring configured? What is the implementation of AOP in spring based on what is the principle?

To answer the second question first, it is a developer who knows that AOP in spring is about aspect-oriented programming. So, to go deeper, the implementation of AOP is actually a dynamic proxy for Java.

The second question is, first of all, about the transactions in the database, and then how spring handles the transactions in the database. In layman's words, a database transaction is a one-time commit when you need to insert 1000 data and then modify 5 of them to delete 3 of the operations. These changes are not actually written to the database until they are committed. At the same time, if there is a mistake in the inside, all the changes made within this transaction will be undone and rolled back. If transactions are not used, the number of database operations is actually 100+5+3 times for the above operations. So, what does spring do with the business? Spring encapsulates transactions and can provide transaction management in a stated manner.

Five: thread pool have you ever used it? What are the two ways to implement multithreading?

How does preprocessing preparestatement prevent SQL injection vulnerabilities?