JDBC
JDBC存在的问题：
1. 数据库连接创建、释放频繁造成系统资源浪费，影响性能
2. 3 5 7中均存在硬编码，修改sql语句还要修改代码，不易维护
1 import java.sql.*; 2 3 public class JDBC { 4 public static void main(String[] args) throws Exception { 5 Connection conn = null; 6 PreparedStatement stmt = null; 7 ResultSet rs = null; 8 9 try { 10 // 1.加载数据库驱动 11 Class.forName("com.mysql.jdbc.Driver"); 12 // 2.通过驱动管理类获取数据库连接 13 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "abc"); 14 // 3.定义sql语句，?表示占位符 15 String sql = "select * from user where username=?"; 16 // 4.获取预处理stmt 17 stmt = conn.prepareStatement(sql); 18 // 5.设置参数(即把sql中的第一个问号设为sakura) 19 stmt.setString(1, "sakura"); 20 // 6.向数据库发出sql执行查询得到结果集rs 21 rs = stmt.executeQuery(); 22 // 7.遍历查询结果集 23 while (rs.next()) { 24 System.out.println(rs.getString("id") + " " 25 + rs.getString("username")); 26 } 27 } catch (Exception e) { 28 e.printStackTrace(); 29 30 } finally { 31 // 8.释放资源 32 if (rs != null) { 33 try { 34 rs.close(); 35 } catch (SQLException e) { 36 e.printStackTrace(); 37 } 38 } 39 40 if (stmt != null) { 41 try { 42 stmt.close(); 43 } catch (SQLException e) { 44 e.printStackTrace(); 45 } 46 } 47 48 if (conn != null) { 49 try { 50 conn.close(); 51 } catch (SQLException e) { 52 e.printStackTrace(); 53 } 54 } 55 } 56 } 57 }
PreparedStatement防止sql注入原理：
SQL注入：简单来说就是用户在前端web页面输入恶意的sql语句用来欺骗后端服务器去执行恶意的sql代码，从而导致数据库数据泄露或者遭受攻击
传入参数为sakura，则执行的sql为select * from user where username='sakura'，但是如果传入的参数变为sakura' or 1='1，则执行的sql语句就变成了select * from user where username='sakura' or 1='1'。所以仅仅通过对sql预编译简单地把字符串参数两边加上引号的方式仍然很容易被sql注入攻击
mysql-jdbc连接源码中PreparedStatement类的实现：
/** * Set a parameter to a Java String value. The driver converts this to a SQL * VARCHAR or LONGVARCHAR value (depending on the arguments size relative to * the driver's limits on VARCHARs) when it sends it to the database. * * @param parameterIndex * the first parameter is 1... * @param x * the parameter value * * @exception SQLException * if a database access error occurs */ public void setString(int parameterIndex, String x) throws SQLException { synchronized (checkClosed().getConnectionMutex()) { // if the passed string is null, then set this column to null if (x == null) { setNull(parameterIndex, Types.CHAR); } else { checkClosed(); int stringLength = x.length(); if (this.connection.isNoBackslashEscapesSet()) { // Scan for any nasty chars boolean needsHexEscape = isEscapeNeededForString(x, stringLength); if (!needsHexEscape) { byte[] parameterAsBytes = null; StringBuilder quotedString = new StringBuilder(x.length() + 2); quotedString.append('\''); quotedString.append(x); quotedString.append('\''); if (!this.isLoadDataQuery) { parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor()); } else { // Send with platform character encoding parameterAsBytes = StringUtils.getBytes(quotedString.toString()); } setInternal(parameterIndex, parameterAsBytes); } else { byte[] parameterAsBytes = null; if (!this.isLoadDataQuery) { parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor()); } else { // Send with platform character encoding parameterAsBytes = StringUtils.getBytes(x); } setBytes(parameterIndex, parameterAsBytes); } return; } String parameterAsString = x; boolean needsQuoted = true; if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) { needsQuoted = false; // saves an allocation later StringBuilder buf = new StringBuilder((int) (x.length() * 1.1)); buf.append('\''); // // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure... // 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; case '"': /* Better safe than sorry */ if (this.usingAnsiMode) { buf.append('\\'); } buf.append('"'); break; case '\032': /* This gives problems on Win32 */ buf.append('\\'); buf.append('Z'); break; case '\u00a5': case '\u20a9': // escape characters interpreted as backslash by mysql if (this.charsetEncoder != null) { CharBuffer cbuf = CharBuffer.allocate(1); ByteBuffer bbuf = ByteBuffer.allocate(1); cbuf.put(c); cbuf.position(0); this.charsetEncoder.encode(cbuf, bbuf, true); if (bbuf.get(0) == '\\') { buf.append('\\'); } } buf.append(c); break; default: buf.append(c); } } buf.append('\''); parameterAsString = buf.toString(); } byte[] parameterAsBytes = null; if (!this.isLoadDataQuery) { if (needsQuoted) { parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor()); } else { parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor()); } } else { // Send with platform character encoding parameterAsBytes = StringUtils.getBytes(parameterAsString); } setInternal(parameterIndex, parameterAsBytes); this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR; } } }
源码中通过一个for循环对字符串参数每一位上的char字符进行遍历，并通过switch()...case条件语句进行判断，当出现换行符、引号、斜杠等特殊字符时，会对这些特殊字符进行转义。即用PreparedStatement进行传参时，若传入参数为sakura' or 1='1，经过处理后实际执行的sql语句是select * from user where username= 'sakura\' or 1 = \'1'，显然这样查询出来的结果为空
