SQL injection, Placeholder stitching

First, what is SQL injection

Official:

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.

Personal:

After the user enters the SQL command in the Web page input box, the background receives no recognition or type conversion, and then runs it directly. Running directly, it is a SQL command that can manipulate the database directly, rather than the normal parameters that the background expects from the SQL command.

Remember a SQL injection combat http://blog.jobbole.com/105586/

Placeholders and splices in MyBatis

1. Placeholder

(1) #{} represents a placeholder symbol that, through #{}, sets the value of the ParameterType pass through PreparedStatement to the placeholder, automates Java type and JDBC type conversion, and #{} can effectively prevent SQL injection.

(2) #{} can receive either a simple type value or a Pojo property value. If ParameterType transmits a single simple type value, #{} can be a value or other name in parentheses.

For example (this is written in JDBC, we don't see PreparedStatement in MyBatis, as long as the placeholder #{}, it automatically implements this process):

Conn

Ps.setstring (1, "Jack"); Placeholder Order starting from 1

Ps.setstring (2, "123456"); You can also use SetObject

Ps.executequery ();

2. Stitching characters

${} is a concatenation of SQL strings, through ${} can be directly splicing parametertype incoming content in SQL and no JDBC type conversion, ${} can receive simple type values or Pojo property values, if ParameterType transport a single simple type value, ${} Only value can be in parentheses.

Third, why preparedstatement effective to prevent SQL injection?

1, PreparedStatement Introduction

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.

2. Introduction to General SQL injection

For example, a website's login verification SQL query code is:

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.

3. Parameterized queries 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.

That is, the SQL statement has been pre-compiled before the program is run, and 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 parameter of a field's property value and not as an SQL instruction.

Added: PreparedStatement faster than Statement

One of the most important benefits 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);

It is obvious that the parameterized query used in MyBatis

Reference: http://www.importnew.com/5006.html

SQL injection, Placeholder stitching