Statement与PreparedStatement的区别理解记录

一、PreparedStatement性能高于Statement（PreparedStatement）属于预编译（缓存），那怎么预编译的呢，源码对比。

Statement创建：只是通过构造方法创建了一个StatementImpl对象，之后通过StatementImpl解析SQL，解析sql根据不同的驱动解析sql，并且需要连接数据库，才能确定表，所以这里一会存在一次网络连接（其实效率主要是这次网路开销），没有使用缓存。

public StatementImpl(MySQLConnection c, String catalog) throws SQLException {
		if ((c == null) || c.isClosed()) {
			throw SQLError.createSQLException(
					Messages.getString("Statement.0"), //$NON-NLS-1$
					SQLError.SQL_STATE_CONNECTION_NOT_OPEN, null); //$NON-NLS-1$ //$NON-NLS-2$
		}

		this.connection = c;
		this.connectionId = this.connection.getId();
		this.exceptionInterceptor = this.connection
				.getExceptionInterceptor();

		this.currentCatalog = catalog;
		this.pedantic = this.connection.getPedantic();
		this.continueBatchOnError = this.connection.getContinueBatchOnError();
		this.useLegacyDatetimeCode = this.connection.getUseLegacyDatetimeCode();
		
		if (!this.connection.getDontTrackOpenResources()) {
			this.connection.registerStatement(this);
		}

		//
		// Adjust, if we know it
		//

		if (this.connection != null) {
			this.maxFieldSize = this.connection.getMaxAllowedPacket();

			int defaultFetchSize = this.connection.getDefaultFetchSize();

			if (defaultFetchSize != 0) {
				setFetchSize(defaultFetchSize);
			}
			
			if (this.connection.getUseUnicode()) {
				this.charEncoding = this.connection.getEncoding();

				this.charConverter = this.connection.getCharsetConverter(this.charEncoding);
			}
			
			

			boolean profiling = this.connection.getProfileSql()
					|| this.connection.getUseUsageAdvisor() || this.connection.getLogSlowQueries();

			if (this.connection.getAutoGenerateTestcaseScript() || profiling) {
				this.statementId = statementCounter++;
			}

			if (profiling) {
				this.pointOfOrigin = LogUtils.findCallingClassAndMethod(new Throwable());
				this.profileSQL = this.connection.getProfileSql();
				this.useUsageAdvisor = this.connection.getUseUsageAdvisor();
				this.eventSink = ProfilerEventHandlerFactory.getInstance(this.connection);
			}

			int maxRowsConn = this.connection.getMaxRows();

			if (maxRowsConn != -1) {
				setMaxRows(maxRowsConn);
			}
			
			this.holdResultsOpenOverClose = this.connection.getHoldResultsOpenOverStatementClose();
		}
		
		version5013OrNewer = this.connection.versionMeetsMinimum(5, 0, 13);
	}

PreparedStatement的创建：判断是否存在缓存，如果不存在缓存则创建，并在创建成功之后关闭流的时候判断PreparedStatement是否存在，如果存在，则放入缓存，从而保证sql的解析过程只有一次。

public java.sql.PreparedStatement prepareStatement(String sql,
			int resultSetType, int resultSetConcurrency) throws SQLException {
		synchronized (getConnectionMutex()) {
			checkClosed();
	
			//
			// FIXME: Create warnings if can't create results of the given
			// type or concurrency
			//
			PreparedStatement pStmt = null;
			
			boolean canServerPrepare = true;
			
			String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
			
			if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
				canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
			}
			
			if (this.useServerPreparedStmts && canServerPrepare) {
                //是否存在缓存，如果存在缓存则取缓存的对象，不存在则创建。
				if (this.getCachePreparedStatements()) {
					synchronized (this.serverSideStatementCache) {
						pStmt = (com.mysql.jdbc.ServerPreparedStatement)this.serverSideStatementCache.remove(sql);
						
						if (pStmt != null) {
							((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false);
							pStmt.clearParameters();
						}
	
						if (pStmt == null) {
							try {
								pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
										this.database, resultSetType, resultSetConcurrency);
								if (sql.length() < getPreparedStatementCacheSqlLimit()) {
									((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
								}
								
								pStmt.setResultSetType(resultSetType);
								pStmt.setResultSetConcurrency(resultSetConcurrency);
							} catch (SQLException sqlEx) {
								// Punt, if necessary
								if (getEmulateUnsupportedPstmts()) {
									pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
									
									if (sql.length() < getPreparedStatementCacheSqlLimit()) {
										this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
									}
								} else {
									throw sqlEx;
								}
							}
						}
					}
				} else {
					try {
						pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
								this.database, resultSetType, resultSetConcurrency);
						
						pStmt.setResultSetType(resultSetType);
						pStmt.setResultSetConcurrency(resultSetConcurrency);
					} catch (SQLException sqlEx) {
						// Punt, if necessary
						if (getEmulateUnsupportedPstmts()) {
							pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
						} else {
							throw sqlEx;
						}
					}
				}
			} else {
				pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
			}
			
			return pStmt;
		}
	}

二、为什么PreparedStatement能防止大部分SQL注入。

原始SQL为：

SELECT * FROM USER WHERE ID = ?;

SELECT * FROM USER WHERE ID = 1;

当用户传入的参数是 1 OR 1=1 时，执行的sql将分别变成，1报错，因为1 OR 1=1转换不了数字，2变成

SELECT * FROM USER WHERE ID = 1 OR 1 = 1;

则查回所有数据信息。

原始SQL为：

SELECT * FROM USER WHERE ID = ?;

SELECT * FROM USER WHERE ID = '1';

当用户传入的参数是 1 OR 1=1 时，执行的sql将分别变成

SELECT * FROM USER WHERE ID = '1 OR 1 = 1';

SELECT * FROM USER WHERE ID = 1 OR 1 = 1;

一个查不回任何数据信息，一个查回所有。

但假如我们对预编译的SQL使用的$,而非#,效果如何

原始SQL为：

SELECT * FROM USER WHERE ID = ?;

SELECT * FROM USER WHERE ID = 1;

结果变成

SELECT * FROM USER WHERE ID = 1 OR 1 = 1;

SELECT * FROM USER WHERE ID = 1 OR 1 = 1;

都会查回所有，这种情况就不能防止SQL注入，所有${}可能带来SQL注入的风险，且不能通过PreparedStatement规避。