MySQL-15-SQL注入和PrepareStatement对象
SQL注入
sql存在漏洞,会被攻击导致数据泄露(sql会被拼接主要由于sql语句中or运算符的存在)
本例工具类,接上篇文章
package com.cmy.lesson2;
import com.cmy.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @Author Void
* @Description //TODO 测试sql注入
* @Date 12:04 2021/3/10
* @Param
* @return
**/
public class SqlInjection {
public static void main(String[] args) {
login("'or '1=1","'or '1=1");
}
public static void login(String username,String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//select * from `users` where `NAME` = ''or '1=1' AND `PASSWORD` = ''or '1=1'(拼接后查出全部)
String sql =
"select * from `users` where `NAME` = '" +username+ "' AND `PASSWORD` = '"+password+"'";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("name = "+rs.getString("NAME"));
System.out.println("password = "+rs.getObject("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
Statement并不安全,由此引入PrepareStatement
PrepareStatement对象
可以防止sql注入,且效率更高,写sql语句更加友好
模板
import com.cmy.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;//查询一般放回结果集,
try {
conn = JdbcUtils.getConnection();
//与statement 的区别
//先搭建sql框架(预编译sql),并使用?充当占位符,
String sql = "";
pst = conn.prepareStatement(sql);//预编译sql
//利用占位符传递参数 (第几个参数,值)
pst.setInt(1,value);
//...
//执行 这里不再传参(预编译过了)
int i = pst.executeUpdate();
if (i>0){
System.out.println("xx成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
插入,删除,修改
......
try {
conn = JdbcUtils.getConnection();
//与statement 的区别
//先搭建sql框架(预编译sql),并使用?充当占位符,
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
pst = conn.prepareStatement(sql);//预编译sql
//利用占位符传递参数 (第几个参数,值)
pst.setInt(1,4);
pst.setString(2,"Void");
pst.setString(3,"123456");
pst.setString(4,"2603135842@qq.com");
// 注意 java.sql.Date 数据库用
// util.Date Java new Date().getTime()时间戳
pst.setDate(5,new java.sql.Date(new Date().getTime()));
//执行 这里不在传参(预编译过了)
int i = pst.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
}
......
......
try {
conn = JdbcUtils.getConnection();
//与statement 的区别
//先搭建sql框架(预编译sql),并使用?充当占位符,
String sql = "delete from `users` where id = ?";
pst = conn.prepareStatement(sql);//预编译sql
//利用占位符传递参数 (第几个参数,值)
pst.setInt(1,4);
//执行 这里不在传参(预编译过了)
int i = pst.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
}
......
......
try {
conn = JdbcUtils.getConnection();
//与statement 的区别
//先搭建sql框架(预编译sql),并使用?充当占位符,
String sql = "UPDATE `users` SET `NAME` = ? WHERE `NAME` = ?";
pst = conn.prepareStatement(sql);//预编译sql
//利用占位符传递参数 (第几个参数,值)
pst.setString(1,"王五");
pst.setString(2,"wangwu");
//执行 这里不再传参(预编译过了)
int i = pst.executeUpdate();
if (i>0){
System.out.println("修改成功");
}
}
......
查询
......
try {
conn = JdbcUtils.getConnection();
//与statement 的区别
//先搭建sql框架(预编译sql),并使用?充当占位符,
String sql = "select * from `users` where id = ?";
pst = conn.prepareStatement(sql);//预编译sql
//利用占位符传递参数 (第几个参数,值)
pst.setInt(1,1);
//执行 这里不再传参(预编译过了)
rs = pst.executeQuery();
while(rs.next()){
System.out.println("name = "+rs.getString("NAME"));
System.out.println("password = "+rs.getString("PASSWORD"));
}
}
......
防SQL注入原理
package com.cmy.lesson03;
import com.cmy.lesson2.utils.JdbcUtils;
import java.sql.*;
/**
* @Author Void
* @Description //TODO 测试prepareStatement sql注入
* @Date 13:50 2021/3/10
* @Param
* @return
**/
public class SqlInjection {
public static void main(String[] args) {
// login("zhansan","123456");
login("'' or 1=1 ","'' or 1=1 ");//无效
//原因是,prepareStatement将所有传入的参数转为字符,若其中存在转义字符,直接忽略:比如`会被直接转义
}
public static void login(String username,String password) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//select * from `users` where `NAME` = ''or '1=1' AND `PASSWORD` = ''or '1=1'(拼接后查出全部)
String sql = "select * from `users` where `NAME` = ? AND `PASSWORD` = ?";
pst = conn.prepareStatement(sql);
pst.setString(1,username);
pst.setString(2,password);
rs = pst.executeQuery();
while(rs.next()){
System.out.println("name = "+rs.getString("NAME"));
System.out.println("password = "+rs.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}