sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
sql注入:
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
package com.linfeng.Demo02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DengLu {
public static void main(String[] args) {
//denglu("zhangsan","123456");//正常登陆
denglu(" 'or'1=1","123456");
}
//登录方法
public static void denglu(String username, String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = Jdbcutils.getConnection();//获取数据库连接
st = conn.createStatement();//获得sql的执行对象
//先sql原型,替换为单引,中间加双引,中间加双加号,中间加变量
String sql = "SELECT * FROM users WHERE `name`='" + username + "' AND `password`='" + password + "'";
//SELECT * FROM users WHERE `name`='zhangsan' AND `password`='123456'
// SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
// SELECT * FROM users WHERE `name`='' or '1#' AND `password`='123456'
System.out.println(sql);
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("==============");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
Jdbcutils.release(conn, st, rs);
}
}
}
导致结果:错误的用户名或者密码可以获取到全部的用户信息
preparedStatem可以防止sql注入并且效率更高
增加操作
package com.linfeng.Demo03;
import com.linfeng.Demo02.utils.Jdbcutils;
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = Jdbcutils.getConnection();
//区别:使用?代替参数
String sql="INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)VALUES (?,?,?,?,?)";
st=conn.prepareStatement(sql);//预编译sql,然后不执行
//手动赋值
st.setInt(1,4);//id赋值
st.setString(2,"ssss");
st.setString(3,"dfsefs");
st.setString(4,"2345235@qq.com");
//sql.date数据库用的
//util.date java用的 new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i =st.executeUpdate();//
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
Jdbcutils.release(conn,st,rs);
}
}
}
注意点: new Date().getTime()获得时间戳
删除操作
import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @ClassName: TestDelete
* @Description: TODO 类描述
* @Author: zyy
* @Date: 2021/07/14 18:19
* @Version: 1.0
*/
public class TestDelete {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "DELETE FROM users WHERE `id`=?";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 5);
int num = st.executeUpdate();
if (num > 0) {
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
查询
import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @ClassName: TestSelect
* @Description: TODO 类描述
* @Author: zyy
* @Date: 2021/07/14 18:19
* @Version: 1.0
*/
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "SELECT * FROM users WHERE id=?";
//预编译SQL,先写SQL,然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 1);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
来看看如何防止sql注入
把上一段代码改为preparedstatement,用?代替参数
import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @ClassName: SQLQuestion
* @Description: TODO 类描述
* @Author: zyy
* @Date: 2021/07/14 18:19
* @Version: 1.0
*/
public class SQLQuestion {
public static void main(String[] args) {
//正常登录
// login("张三","123456");
//sql注入
login("' or '1=1", "123456");
}
/**
* 登录业务
*/
public static void login(String userName, String password) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
// PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符
// 假设其中存在转义字符,比如说'会被直接转义
String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
st = con.prepareStatement(sql);
st.setString(1, userName);
st.setString(2, password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id=" + rs.getInt("id"));
System.out.println("name=" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}