当前位置：首页 > news >正文

java如何预防sql注入

news 来源：原创 2024/7/5 0:02:59

1 sql注入

1.1 使用字符串拼接导致数据异常 sql语句拼接

        // 构建SQL查询语句，注意这里存在SQL注入风险String sql = "select name,age from user where name='" + username + "' and password ='" + password + "'";System.out.println("sql语句为:" + sql);

1.2 若使用超级用户名登录则造成数据泄露

    public static void main(String[] args) {// 测试用例1：正常用户名// String username = "张三";// 测试用例2：恶意构造的用户名，用于SQL注入攻击String username1 = "'or 1=1 or' '='";boolean flag = login(username1, "111111111111");System.out.println(flag);}

输出结果

1.3 非法登录绕过验证

1.4 先通过用户名验证再验证密码

          // 如果查询结果有记录，说明用户名存在if (resultSet.next()) {// 2. 继续验证取出的密码String pass = resultSet.getString("password");// 3. 继续判断数据库中的密码与用户传入的密码是否匹配if (pass != null && pass.equals(password)) {// 密码匹配成功return true;} else {// 密码匹配失败return false;}}

2 预处理

执行sql语句有 Statement PreparedStatement(防注入，特殊字符处理，效率高)

预处理使用占位符,之后再赋值

 // 使用预编译的SQL插入语句String sql1 = "insert into user(password,name,phone)" + "values(?,?,?)";// 使用预编译的SQL语句PreparedStatement ps = null;try {// 预编译SQL语句ps = conn.prepareStatement(sql1);
// 为预编译语句中的占位符赋值ps.setString(1, "999999");ps.setString(2, "测试ps");ps.setString(3, "138457856523");
// 执行预编译的SQL语句并获取影响的行数int row = ps.executeUpdate();System.out.println("-----" + row);

3 加载配置文件

加载配置文件

• 在文件中配置连接属性

import com.yw.utils.ConnectionUtil;

import java.sql.Connection;

public class test {public static void main(String[] args) {/*** 配置文件读取成功*/Connection conn = ConnectionUtil.getConn();System.out.println(conn);}
}

• 在连接工具类中使用读取属性文件的方式获取连接参数

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/*** 创建连接*/
public class ConnectionUtil {/*** io流*/private static String driver;private static  String url;private static String username;private static String password;
static {InputStream is = ConnectionUtil.class.getResourceAsStream("/db.properties");Properties p = new Properties();try {p.load(is);driver = p.getProperty("driver");url = p.getProperty("url");username = p.getProperty("username");password = p.getProperty("password");
//  password = p.getProperty("password");} catch (IOException e) {throw new RuntimeException(e);}}
/*** 建立连接方法* @return*/public static Connection getConn(){Connection conn = null;try {Class.forName(driver);//加载驱动conn = DriverManager.getConnection(url,username,password);//给了三个参} catch (Exception e) {throw new RuntimeException(e);}return conn;}
public static void close(Connection connection,Statement statement,ResultSet resultSet){if(resultSet != null){try {resultSet.close();} catch (SQLException e) {throw new RuntimeException(e);}}
if(statement != null){try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}
if(connection != null){try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}
}
}

• db.properties 直接使用文本文件进行编辑

username = root
password = root
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/java_demo1

4 元数据集

import com.sun.xml.internal.ws.addressing.WsaActionUtil;
import com.yw.utils.ConnectionUtil;

import java.sql.*;

public class test1 {public static void main(String[] args) {String sql = "select * from user";query(sql);}
public static void query(String sql){Connection conn = ConnectionUtil.getConn();PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);rs = ps.executeQuery();/*ResultSetMetaData metaData = rs.getMetaData();//元数据int count = metaData.getColumnCount();//获取列数System.out.println(count);for (int i = 0; i < count; i++) {String catalogName = metaData.getColumnName(i+1);int columnType = metaData.getColumnType(i + 1);System.out.println(catalogName+"==="+columnType);}*/
ResultSetMetaData rsd = rs.getMetaData();//获取元数据String columnClassName = rsd.getColumnClassName(1);//返回字段类型int columnType = rsd.getColumnType(2);String columnName = rsd.getColumnName(1);String columnName1 = rsd.getColumnName(2);System.out.println(columnClassName+"====="+columnType+"=========="+columnName+"========"+columnName1);

} catch (Exception e) {throw new RuntimeException(e);}finally {ConnectionUtil.close(conn,ps,rs);}}

}

5 封装BaseDao

BaseDao

import com.yw.utils.ConnectionUtil;

import java.sql.*;

public class BaseDao {
private String username ="root";private String password ="123456";private String driver ="com.mysql.cj.jdbc.Driver";private String url = "jdbc:mysql://localhost:3306/java_demo1";
private Connection connection;//连接private PreparedStatement ps;//执行sql语句private ResultSet rs;//返回结果集
/*** 连接方法* @return*/public Connection getConnection(){try {if(connection == null || connection.isClosed()){ // 或者已经关闭Class.forName(driver);//加载驱动connection = DriverManager.getConnection(url,username,password);}return connection;} catch (Exception e) {throw new RuntimeException(e);}}
/*  public Connection getConnection(){try {if(connection == null || connection.isClosed()) {Class.forName(driver);connection = DriverManager.getConnection(url,username,password);}return connection;} catch (Exception e) {throw new RuntimeException(e);}}*/
/*  public int update(String sql,Object ... params){ //sql以参数的形式传入connection = getConnection();System.out.println(sql);try {ps = connection.prepareStatement(sql);*//*int i = ps.executeUpdate();return i;*//*for (int i = 0; i < params.length; i++) {ps.setObject(i+1,params[i]);}int i = ps.executeUpdate();return i;}catch (Exception e){e.printStackTrace();}return -1;}*/
/*** 更新方法* @param sql* @param params 增加 加一  删除 少一个  修改 where id= ? 返回影响行* @return*/public int update(String sql,Object...params){connection = getConnection();//获得连接System.out.println(sql);try {ps = connection.prepareStatement(sql);//使用预编译 占位符for (int i = 0; i < params.length; i++) {ps.setObject(i+1,params[i]);}int i = ps.executeUpdate();//返回影响行数return i;} catch (SQLException e) {throw new RuntimeException(e);}}

/* public ResultSet query(String sql,Object... params){System.out.println(sql);connection = getConnection();//连接try {ps = connection.prepareStatement(sql);//预编译for (int i = 0; i < params.length; i++) {ps.setObject(i+1,params[i]);}rs = ps.executeQuery();return rs;} catch (Exception e) {throw new RuntimeException(e);}}*/
/**** @param sql* @param params 查询* @return*/
public ResultSet Query(String sql,Object...params){connection = getConnection();try {ps = connection.prepareStatement(sql);for (int i = 1; i < params.length; i++) {ps.setObject(i,params[i]);}rs = ps.executeQuery();return rs;} catch (SQLException e) {throw new RuntimeException(e);}}

public void close(){try {if(rs != null){rs.close();rs = null;}if(ps != null){ps.close();ps = null;}if(connection !=  null){connection.close();connection = null;}}catch (Exception e){e.printStackTrace();}}

}

TestBase

import java.sql.ResultSet;
import java.sql.SQLException;

public class TestBase {public static void main(String[] args) throws SQLException {/* String sql = "insert into user(password,name,age,phone)values(?,?,?,?)";Object[] params = {"654321","basedao",12,"1547895132"};BaseDao baseDao = new BaseDao();int i = baseDao.update(sql,params);*/
/*** 测试更新方法*//* String sql = "insert into user(password,name,age,phone)values(?,?,?,?)";BaseDao baseDao = new BaseDao();Object[] params = {"898989","测试params",15,"189878564"};baseDao.update(sql,params);*/
/*String sql = "select password,name,phone from user";BaseDao baseDao = new BaseDao();ResultSet query = baseDao.query(sql);//不能关闭try{while (query.next()){String password = query.getString("password");String name = query.getString("name");String phone = query.getString("phone");System.out.println(password+name+password);}}catch (Exception e){e.printStackTrace();}*/
String sql1 = "select password,name, phone from user";BaseDao baseDao = new BaseDao();ResultSet query = baseDao.Query(sql1);while (query.next()){String pass = query.getString("password");String name = query.getString("name");String phone = query.getString("phone");System.out.println("pass="+pass+"name="+name+"phone="+phone);}}
}