1、PreparedStatement是Statement的子类。
2、PreparedStatement(预处理执行语句)相比其父类Statement主要有以下几个优点:
1)可以防止SQL注入.
2)在特定的驱动数据库下相对效率要高(不绝对)
3)不需要频繁编译.因为已经预加载了
这里2和3的优点就不具体分析.这里主要讲解下防止SQL注入这一用途.
3、DBUtils辅助类
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author :黑马白浪
* @description :模板类DBUtils
*/
public final class DBUtils {
// 参数定义
private static String url = "jdbc:mysql://localhost:3308/bbs"; // 数据库地址
private static String username = "root"; // 数据库用户名
private static String password = "test"; // 数据库密码
private DBUtils() {
}
// 加载驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动加载出错!");
}
}
// 获得连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
// 释放连接
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null) {
rs.close(); // 关闭结果集
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null) {
st.close(); // 关闭Statement
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close(); // 关闭连接
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
上边就是简单的users表.然后看下SQL注入演示类
4、SQLInner
package com.test.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInner {
public static void main(String[] args) {
//Read("zhangsan"); // 如果普通查询可以
Read("' or 1 or'");
}
public static void Read(String name) {
Statement st = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = DBUtils.getConnection();
st = conn.createStatement();
String sql = "select * from users where lastname = '" + name + "'"; // 主要注入发生地
System.out.println("sql: " + sql); // 打印SQL语句
rs = st.executeQuery(sql);
System.out.println("age\tlastname\tfirstname\tid");
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2)
+ "\t\t" + rs.getString(3) + "\t\t" + rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.free(rs, st, conn);
}
}
}
5、
package com.test.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement
import java.sql.ResultSet;
import java.sql.SQLException;
public class SqlInner {
public static void main(String[] args) {
Read("' or 1 or'");
}
public static void Read(String name) {
PreparedStatement st = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = DBUtils.getConnection();
String sql = "select * from users where lastname = ?"; // 这里用问号
st = conn.prepareStatement(sql);
st.setString(1,name); // 这里将问号赋值
rs = st.executeQuery();
System.out.println("age\tlastname\tfirstname\tid");
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2)
+ "\t\t" + rs.getString(3) + "\t\t" + rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.free(rs, st, conn);
}
}
}