SQL注入的问题

SQL存在漏洞，会被攻击导致数据泄露。

SQL注入测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//SQL注入
public class SQLInjection {
    public static void main(String[] args) {
        //login("ZHANGSAN","123456");//正常登录
        login("‘or‘1=1","‘or‘1=1");//SQL注入（不用输入正确用户密码也能得到数据
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "SELECT * FROM users WHERE `NAME` = ‘"+username+"‘ AND `PASSWORD` = ‘"+password+"‘";
            rs = st.executeQuery(sql);//返回的结果集
            while(rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("pwd="+rs.getString("PASSWORD"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birth="+rs.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

工具类代码：

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

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static{
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

PreparedStatement对象

PreparedStatement可以防止SQL注入，并且效率更高

PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符

假设其中存在转义字符比如说‘会被直接转义

增加测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"zhaoliu");
            st.setString(3,"123456");
            st.setString(4,"zhaoliu@163.com");
            //注意点： sql.Date是数据库用的 util.Date是Java用的
            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,null);
        }
    }
}

删除测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "DELETE FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

修改测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数
            String sql = "UPDATE users SET `NAME` = ? WHERE id = ?";

            st = conn.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行

            //手动给参数赋值
            st.setString(1,"wangwu");
            st.setInt(2,3);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

查询测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            String sql = "SELECT * FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);

            st.setInt(1,1);//传递参数

            rs = st.executeQuery();//执行

            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入测试类：

import com.qsy.lesson02.utils.JdbcUtils;

import java.sql.*;

//SQL注入
public class SQLInjection {
    public static void main(String[] args) {
        //login("ZHANGSAN","123456");//正常登录
        login("‘‘ or 1=1","123456");//SQL注入
    }
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();//获取数据库连接

            String sql = "SELECT * FROM users WHERE `NAME` = ? AND `PASSWORD` = ?";

            st = conn.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"));
                System.out.println("pwd="+rs.getString("PASSWORD"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birth="+rs.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入的问题及解决方法