JDBC中的PreparedStatement-防止SQL注入攻击

在JDBC对数据库进行操作的时候，SQL注入是一种常见的针对数据库的注入攻击方式。如下面的代码所演示，在我们的提交字段中掺入了SQL语句，会使得程序的登录校验失效：

package org.lyk.main; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbcp2.BasicDataSource; import org.omg.CORBA.PUBLIC_MEMBER; import com.mysql.jdbc.DatabaseMetaData; public class Main { public static String DBDRIVER = "com.mysql.jdbc.Driver"; public static String DB_URL = "jdbc:mysql://localhost:3306/mldn"; public static String USERNAME = "root"; public static String PASSWORD = "admin"; public static BasicDataSource bds = null; public static void main(String[] args) { dbPoolInit(); System.out.println(verify("LIU YAN' -- ", "XXX")); System.out.println("///Done~~~"); } public static void dbPoolInit() { bds = new BasicDataSource(); bds.setDriverClassName(DBDRIVER); bds.setUrl(DB_URL); bds.setUsername(USERNAME); bds.setPassword(PASSWORD); } public static boolean verify(String name, String password) { Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "SELECT COUNT(*) FROM user WHERE name='"+name+"' and password='"+password+"';"; System.out.println(sql); boolean retVal = false; try { conn = bds.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if(rs.next() && rs.getInt(1) == 1) { retVal = true; } //System.out.println(rs.getInt(1)); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if(conn != null ) conn.close(); if(stmt != null) stmt.close(); if(rs != null) rs.close(); } catch(Exception exception) { //ignore all exceptions when closing... } } return retVal; } }

为了解决这个问题，可以使用PreparedStatement来防止该类型的攻击。

其演示代码如下:

package org.lyk.main; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.dbcp2.BasicDataSource; public class Main { public static String DBDRIVER = "com.mysql.jdbc.Driver"; public static String DB_URL = "jdbc:mysql://localhost:3306/mldn"; public static String USERNAME = "root"; public static String PASSWORD = "admin"; public static BasicDataSource bds = null; public static void main(String[] args) { dbPoolInit(); System.out.println(verify("LIU YAN' -- ", "XXX")); System.out.println("///Done~~~"); } public static void dbPoolInit() { bds = new BasicDataSource(); bds.setDriverClassName(DBDRIVER); bds.setUrl(DB_URL); bds.setUsername(USERNAME); bds.setPassword(PASSWORD); } public static boolean verify(String name, String password) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; String sql = "SELECT COUNT(*) FROM user WHERE name=? and password=? ;"; System.out.println(sql); boolean retVal = false; try { conn = bds.getConnection(); stmt = conn.prepareStatement(sql); stmt.setString(1, name); stmt.setString(2, password); rs = stmt.executeQuery(); if(rs.next() && rs.getInt(1) == 1) { retVal = true; } //System.out.println(rs.getInt(1)); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if(conn != null ) conn.close(); if(stmt != null) stmt.close(); if(rs != null) rs.close(); } catch(Exception exception) { //ignore all exceptions when closing... } } return retVal; } }