Sql注入 回顾

一、有可能被sql注入示例

1、正常查询：

public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        String username = " ' or 1=1 -- '"; //sql注入导致出错
        String username = "2";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sun_blog?characterEncoding=utf8&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root","123456");
        String sql = "select id,username from persons p  where username ='"+username+"'  and password = '"+password+ "'";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        System.out.println(preparedStatement.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String id = resultSet.getString(1);
            String usename = resultSet.getString(2);
            System.out.println(id+"-----"+usename);
        }

    }

 

查询结果：

执行sql:

select id,username from persons p  where username ='2'  and password = '123456'

2、被注入之后查询:

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String username = " ' or 1=1 -- '"; //sql注入导致出错
//        String username = "2";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sun_blog?characterEncoding=utf8&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root","123456");
        String sql = "select id,username from persons p  where username ='"+username+"'  and password = '"+password+ "'";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        System.out.println(preparedStatement.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String id = resultSet.getString(1);
            String usename = resultSet.getString(2);
            System.out.println(id+"-----"+usename);
        }


    }

查询结果：

执行sql:

select id,username from persons p  where username =' ' or 1=1 -- ''  and password = '123456'

二、不会被sql注入示例

1、正常查询：

   public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        String username = " ' or 1=1 -- '"; //sql注入导致出错
        String username = "2";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sun_blog?characterEncoding=utf8&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root","123456");
        String sql = "select id,username from persons p  where username =?  and password = ?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        System.out.println(preparedStatement.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String id = resultSet.getString(1);
            String usename = resultSet.getString(2);
            System.out.println(id+"-----"+usename);
        }
    }

查询结果：

执行sql:

select id,username from persons p  where username ='2'  and password = '123456'

 

2、被注入之后查询:

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String username = " ' or 1=1 -- '"; //sql注入导致出错
//        String username = "2";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sun_blog?characterEncoding=utf8&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root","123456");
        String sql = "select id,username from persons p  where username =?  and password = ?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        System.out.println(preparedStatement.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String id = resultSet.getString(1);
            String usename = resultSet.getString(2);
            System.out.println(id+"-----"+usename);
        }
    }

查询结果：

执行sql:

select id,username from persons p  where username =' '' or 1=1 -- '''  and password = '123456'

 

拓展：

$和#的区别

$容易进行sql注入

#可以防止sql注入

在mapper文件中用${字段名}执行的sql如下：

select id,create_datetime,email,phone,sex,username,zone from persons where username= '' or 1=1 and phone=111 

用#{字段名}执行的sql如下：

select id,create_datetime,email,phone,sex,username,zone from persons where username=? and phone=? 

传的参数均为：

String username = " '' or 1=1";
        String phone = "111";

所以使用$容易被sql注入，请使用#