background
Recently, due to job adjustments, we have been dealing with databases, which has increased many opportunities for close contact with JDBC. In fact, we are using Mybatis.Knowing it, knowing it, is what our engineers and children's shoes should pursue, to help you better understand this technology, and to be more comfortable when facing problems.So, JDBC has recently been studied in business time, with some small gains, so make a note here.
We all know that there are many databases on the market, such as Oracle, Sqlserver, Mysql, etc. Because Mysql is open and customizable, developers working in schools or the Internet should be exposed to Mysql the most. The following explanations in this paper are mainly focused on the implementation of JDBC in the Mysql driver.
outline
This paper gives a brief introduction to the origin of JDBC, introduces the driver loading code in the process of using JDBC, introduces several commonly used interfaces, and focuses on the analysis of the usage of Statement and Prerement and their differences on SQL injection.Finally, the differences of anti-SQL injection and specific execution before and after opening precompilation for PrepareStatement are analyzed.
Why JDBC is required
As we all know, each database has a different implementation. If developers need to program their specific implementation every time they come into contact with a new database, I guess the real code hasn't started yet, and they're dying at the bottom level of development, which doesn't fit into the Java Interface-oriented programming feature.So there's JDBC.
JDBC (Java Data Base Connectivity,java Database Connectivity) is a Java API used to execute SQL statements and provides unified access to multiple relational databases. It is used by a set of Java Language Written class and interface composition.
If represented by a diagram, as shown in the diagram above, developers do not have to rush for the differences in each data communication protocol. Instead, they need to program the interface provided by JDBC to operate the corresponding DB by the corresponding driver at run time.
Sample Code
Say nothing, give a simple example code, mainly completed the process of getting database connection, executing SQL statement, printing the returned results, and releasing the connection.
package jdbc; import java.sql.*; /** * @author cenkailun * @Date 17/5/20 * @Time 5:09 p.m. */ public class Main { private static final String url = "jdbc:mysql://127.0.0.1:3306/demo"; private static final String user = "root"; private static final String password = "123456"; static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) throws SQLException { Connection connection = DriverManager.getConnection(url, user, password); System.out.println("Statement Statement Result: "); Statement statement = connection.createStatement(); statement.execute("SELECT * FROM SU_City limit 3"); ResultSet resultSet = statement.getResultSet(); printResultSet(resultSet); resultSet.close(); statement.close(); System.out.println(); System.out.println("PreparedStatement Statement Result: "); PreparedStatement preparedStatement = connection .prepareStatement("SELECT * FROM SU_City WHERE city_en_name = ? limit 3"); preparedStatement.setString(1, "beijing"); preparedStatement.execute(); resultSet = preparedStatement.getResultSet(); printResultSet(resultSet); resultSet.close(); preparedStatement.close(); connection.close(); } /** * Processing Return Result Set */ private static void printResultSet(ResultSet rs) { try { ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); StringBuffer b = new StringBuffer(); while (rs.next()) { for (int i = 1; i <= cols; i++) { b.append(meta.getColumnName(i) + "="); b.append(rs.getString(i) + "\t"); } b.append("\n"); } System.out.print(b.toString()); } catch (Exception e) { e.printStackTrace(); } } }
Main interface:
DriverManager: Manages drivers, primarily used to invoke drivers to obtain connections from databases.
Connection: Represents a database connection.
Statement: Holds the Sql statement, executes it, and returns the result after execution.
ResulSet: Sql execution completed, returned record Holdings
code analysis
Next, we analyze the sample code to illustrate the relevant knowledge points, which are implemented for
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.42</version> </dependency>
Drive Loading
In the static block of the sample code, we executed the
Class.forName("com.mysql.jdbc.Driver");
Class.forName initializes a class through reflection.At com.mysql.jdbc.Driver, this is visually a concrete implementation of MySQL for the Driver interface in JDBC, where, in its static code block, it registers itself with DriverManager.
public class Driver extends NonRegisteringDriver implements java.sql.Driver { // // Register ourselves with the DriverManager // static { try { java.sql.DriverManager.registerDriver(new Driver()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!"); } } /** * Construct a new driver and register it with DriverManager * * @throws SQLException * if a database error occurs. */ public Driver() throws SQLException { // Required for Class.forName().newInstance() } }
There is a CopyOnWriterArrayList in DriverManger, which saves the registration driver and can be introduced later. It is a copy that is copied out and written while it is being written, and then copied back.
private final static CopyOnWriteArrayList<DriverInfo> registeredDrivers = new CopyOnWriteArrayList<DriverInfo>();
After registering the drivers, we can get the Connection via DriverManager. Here is a question. What if more than one driver is registered?JDBC also responds to this by calling the accepts URL of each driver implementation when choosing which driver to use to determine if the driver meets the criteria.
public static Driver getDriver(String url) throws SQLException { Class<?> callerClass = Reflection.getCallerClass(); for (DriverInfo aDriver : registeredDrivers) { if(isDriverAllowed(aDriver.driver, callerClass)) { try { if(aDriver.driver.acceptsURL(url)) { return (aDriver.driver); } ..............................................
If there are multiple qualified drivers, first come first served ~
Next, build the Sql statement.Statement has three specific implementation classes:
PreparedStatement: When PreparedStatement is created, it passes an sql statement in the past. If it starts precompiling, it returns the statement ID. The next time it passes the statement ID and parameters in the past, there will be less compilation.
Statement: Statement uses Connection to get an empty executor and passes the spelled SQL to it at execution time. Because it is the entire SQL, the probability of a perfect match is low and the compilation needs to be resolved each time.
CallableStatement is used to execute stored procedures and has not been encountered yet.
Statement and reparedStatement are discussed below.
Premise: mysql executes the script as follows: prepare -> optimize -> exec (physical execution), where prepare is what we call compilation.As mentioned earlier, for the same sql template, if you can cache the results of prepare, then execute the same template with different sql parameters in the future, you can save the preparation step and the cost of sql execution.
Statement
Statement can be understood as transferring SQL statements to Mysql completely every time, which has been criticized for its difficulty in preventing the simplest Sql injection.
2017-05-20T10:07:20.439856Z 15 Query SET NAMES latin1 2017-05-20T10:07:20.440138Z 15 Query SET character_set_results = NULL 2017-05-20T10:07:20.440733Z 15 Query SET autocommit=1 2017-05-20T10:07:20.445518Z 15 Query SELECT * FROM SU_City limit 3
We made the appropriate changes to the statement statement, city_en_name =''beijing'OR 1 = 1', and completed the SQL injection because the ordinary statement would not do anything with the SQL, in which the OR after the single quotation marks took effect and pulled out all the data.
2017-05-20T10:10:02.739761Z 17 Query SELECT * FROM SU_City WHERE city_en_name = 'beijing' OR 1 = 1 limit 3
PreparedStatement
For PreparedStatement, it was previously understood that because it was used, it would be precompiled, so it prevented SQL injection, so why it prevented it is unclear.Let's see the effect first.
2017-05-20T10:14:16.841835Z 19 Query SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3
Same code, single quotes are escaped, so they are not injected by SQL.
But I want you to notice that here we have not turned on precompilation.It is incorrect to prevent SQL injection because precompilation is turned on.
Looking at the code below, you can see that when precompilation is not turned on and when setString is used, a mysql-driven PreparedStatement is used, in which the parameters are processed.
publicvoidsetString(intparameterIndex, String x)throwsSQLException {
It's roughly here.
for (int i = 0; i < stringLength; ++i) { char c = x.charAt(i); switch (c) { case 0: /* Must be escaped for 'mysql' */ buf.append('\\'); buf.append('0'); break; case '\n': /* Must be escaped for logs */ buf.append('\\'); buf.append('n'); break; case '\r': buf.append('\\'); buf.append('r'); break; case '\\': buf.append('\\'); buf.append('\\'); break; case '\'': buf.append('\\'); buf.append('\''); break;
So it's wrong to turn on precompilation to prevent SQL injection, and of course, it can do so after you turn on precompilation.
Mysql actually supports precompilation.You need to specify it in JDBCURL to turn on precompilation successfully.
"jdbc:mysql://127.0.0.1:3306/demo?useServerPrepStmts=true"
At the same time, we can prove that after turning on server-side precompilation, the parameters are escaped on the Mysql side.Below are the specific log scenarios after the precompiled server is turned on.If you turn on wireshark, you can see that parameters are not escaped when passed, so Mysql on the server side can also escape individual characters.
2017-05-20T10:27:53.618269Z 20 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:27:53.619532Z 20 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3
A little further, if you open a new PrepareStatement, you will see that if you want to precompile it twice, the precompilation will have no meaning, equal to one more network transmission each time.
2017-05-20T10:33:26.206977Z 23 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:33:26.208019Z 23 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3 2017-05-20T10:33:26.208829Z 23 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:33:26.209098Z 23 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3
After querying the data, I find that I also need to turn on a parameter to let the JVM side cache, which is at the Connection level.Then see the effect.
"jdbc:mysql://127.0.0.1:3306/demo?useServerPrepStmts=true&cachePrepStmts=true";
Looking at the log, I found it was still two times, I have.
2017-05-20T10:34:51.540301Z 25 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:34:51.541307Z 25 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3 2017-05-20T10:34:51.542025Z 25 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:34:51.542278Z 25 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3
When you click on the close method of PrepareStatement, you will see the following code, and you will suddenly realize that the cache will not take effect until you close it.
public void close() throws SQLException { MySQLConnection locallyScopedConn = this.connection; if (locallyScopedConn == null) { return; // already closed } synchronized (locallyScopedConn.getConnectionMutex()) { if (this.isCached && isPoolable() && !this.isClosed) { clearParameters(); this.isClosed = true; this.connection.recachePreparedStatement(this); return; } realClose(true, true); } }
Instead of pretending to close the statement, the statement is stuffed into the cache.And then we'll see the effect again, perfect.
2017-05-20T10:39:39.410584Z 26 Prepare SELECT * FROM SU_City WHERE city_en_name = ? limit 3 2017-05-20T10:39:39.411715Z 26 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3 2017-05-20T10:39:39.412388Z 26 Execute SELECT * FROM SU_City WHERE city_en_name = '\'beijing\' OR 1 = 1 ' limit 3
conclusion
JDBC is a great thing.
Statement does not have the ability to prevent SQL injection.
PrepareStatement parameterizes SQL locally, transfers individual characters when precompilation is not turned on, and transfers to mysql when precompilation is turned on.
PrepareStatement is recommended because it can also be handled locally to prevent SQL injection by transferring a complete SQL like a statement.
If you turn on the useServerPrepStmts=true feature of PrepareStatement, also turn on cachePrepStmts=true, otherwise the same SQL template will compile once at a time, execute once, and the network overhead will double, affecting efficiency.
To learn more, you can follow my WeChat Public Number