1.简述
1.1.什么是SQL注入
????????SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
2.SQL注入案例(Jdbc)
2.1.建表语句
| create table user_mybatis(? ??? id????? int Primary key,? ??? username??? varchar(30),? ??? password??? varchar(30)? );? insert into user_table values(1,user -1','12345');? insert into user_table values(2,user-2','12345');? |
2.2.使用Jdbc加载sql文
| package com.me.homesickness.mybatis.test; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement; /** ?* sql注入Jdbc ?* @author Administrator ?* ?*/ public class JdbcSqlInjection { ??? public static void main(String[] args) throws ClassNotFoundException, SQLException { ??????? String userName = "zhangsan"; ??????? String password = "hdhdfbhgs+++jse"; ??????? String sql = "SELECT id,username from user_mybatis WHERE " + "username='" + userName + "' AND " + "password='" + password + "'"; ??????? ??????? // 1.加载驱动 ??????? Class.forName("com.mysql.jdbc.Driver"); ??????? ??????? // 2.创建数据库连接对象 ??????? Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?characterEncoding=utf8", "root", "mnj852123"); ??????? ??????? // 3.指定查询sql ??????? PreparedStatement stat = (PreparedStatement) conn.prepareStatement(sql); ??????? System.out.println(stat.toString()); ??????? ??????? // 4.执行查询，获取结果集 ??????? ResultSet rs = stat.executeQuery(); ??????? ??????? // 5.输出查询内容 ??????? while(rs.next()) { ??????????? String id = rs.getString(1); ??????????? String name = rs.getString(2); ??????????? System.out.println("id:" + id + "??????? name:" + name); ??????? } ??? } } |
2.3.执行结果
2.4.修改username的值
| ??????? // String userName = "zhangsan"; ??? ??? String userName = "' OR 1=1 -- '"; |
解析：
1）“--” 表示SQL注释，因此后面语句忽略；
2）因为1=1恒成立，因此 username='' OR 1=1? 恒成立，因此SQL语句等同于：
| SELECT id,username from user_mybatis WHERE username='' OR 1=1 -- '' AND password='hdhdfbhgs+++jse' |
2.5.执行代码
| package com.me.homesickness.mybatis.test; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement; /** ?* sql注入Jdbc ?* @author Administrator ?* ?*/ public class JdbcSqlInjection { ??? public static void main(String[] args) throws ClassNotFoundException, SQLException { ??????? // String userName = "zhangsan"; ??????? String userName = "' OR 1=1 -- '"; ??????? String password = "hdhdfbhgs+++jse"; ??????? String sql = "SELECT id,username from user_mybatis WHERE " + "username='" + userName + "' AND " + "password='" + password + "'"; ??????? ??????? // 1.加载驱动 ??????? Class.forName("com.mysql.jdbc.Driver"); ??????? ??????? // 2.创建数据库连接对象 ??????? Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?characterEncoding=utf8", "root", "mnj852123"); ??????? ??????? // 3.指定查询sql ??????? PreparedStatement stat = (PreparedStatement) conn.prepareStatement(sql); ??????? System.out.println(stat.toString()); ??????? ??????? // 4.执行查询，获取结果集 ??????? ResultSet rs = stat.executeQuery(); ??????? ??????? // 5.输出查询内容 ??????? while(rs.next()) { ??????????? String id = rs.getString(1); ??????????? String name = rs.getString(2); ??????????? System.out.println("id:" + id + "??????? name:" + name); ??????? } ??? } } |
2.6.解决方法
| ??????? /** ??????? ?* JDBC解决SQL注入 ??????? ?*/ ??????? String userName = "' OR 1=1 -- '"; ??????? String password = "hdhdfbhgs+++jse"; ??????? String sql = "SELECT id,username FROM user_mybatis WHERE username = ? AND password = ?"; ??????? ??????? // 1.加载驱动 ??????? Class.forName("com.mysql.jdbc.Driver"); ??????? ??????? // 2.创建数据库连接对象 ??????? Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?characterEncoding=utf8", "root", "mnj852123"); ??????? ??????? // 3.指定查询sql ??????? PreparedStatement stat = (PreparedStatement) conn.prepareStatement(sql); ??????? stat.setString(1, userName); ??????? stat.setString(2, password); ??????? System.out.println(stat.toString());??????? ??????? ??????? // 4.执行查询，获取结果集 ??????? ResultSet rs = stat.executeQuery(); ??????? ??????? // 5.输出查询内容 ??????? while(rs.next()) { ??????????? String id = rs.getString(1); ??????????? String name = rs.getString(2); ??????????? System.out.println("id:" + id + "???? ???name:" + name); ??????? } |
2.7.执行结果
3.Mybatis中符号#与符号$的区别
????????动态 sql 是 mybatis 的主要特性之一，在 mapper 中定义的参数传到 xml 中之后，在查询之前 mybatis 会对其进行动态解析。mybatis 为我们提供了两种支持动态 sql 的语法：#{} 以及 ${}。
????????在下面的语句中，如果 username 的值为 zhangsan，则两种方式无任何区别：
?????????????? select * from user where name = #{name};
?????????????? select * from user where name = ${name};
????????其解析之后的结果均为
?????????????? select * from user where name = 'zhangsan';
??? 但是 #{} 和 ${} 在预编译中的处理是不一样的。#{} 在预处理时，会把参数部分用一个占位符 ? 代替，变成如下的 sql 语句：
?????????????? select * from user where name = ?;
????????而 ${} 则只是简单的字符串替换，在动态解析阶段，该 sql 语句会被解析成
????????select * from user where name = 'zhangsan';
????????以上，#{} 的参数替换是发生在 DBMS 中，而 ${} 则发生在动态解析过程中。
????????那么，在使用过程中我们应该使用哪种方式呢？
????????答案是，优先使用 #{}。因为 ${} 会导致 sql 注入的问题。看下面的例子：
??? select * from ${tableName} where name = #{name}
????????在这个例子中，如果表名为
?????????user; delete user; --?
则动态解析之后 sql 如下：
?????????????? select * from user; delete user; -- where name = ?;
--之后的语句被注释掉，而原本查询用户的语句变成了查询所有用户信息+删除用户表的语句，会对数据库造成重大损伤，极大可能导致服务器宕机。
????????因为数据库的原因导致表名用参数传递进来的时候，只能使用 ${},所以我们在这种用法中要小心sql注入的问。
4.SQL注入案例（Mybatis）
4.1.建表语句
| create table user_mybatis(? ??? id????? int Primary key,? ??? username??? varchar(30),? ??? password??? varchar(30)? );? insert into user_table values(1,user -1','12345');? insert into user_table values(2,user-2','12345');? |
4.2.创建POJO类
| package com.me.homesickness.mybatis.pojo; public class UserMybatis { ??? ??? private int id; ??? private String userName; ??? private String passWord; ??? ??? public int getId() { ??????? return id; ??? } ??? public void setId(int id) { ??????? this.id = id; ??? } ??? public String getUserName() { ??????? return userName; ??? } ??? public void setUserName(String userName) { ??????? this.userName = userName; ??? } ??? public String getPassWord() { ??????? return passWord; ??? } ??? public void setPassWord(String passWord) { ??????? this.passWord = passWord; ??? } } |
4.3.创建Mapper类
| package com.me.homesickness.mybatis.mapper; import com.me.homesickness.mybatis.pojo.UserMybatis; public interface UserMybatisMapper { ??? public UserMybatis login(UserMybatis userMybatis); ??? } |
4.4.创建Mapper映射XML文件UserMybatisMapper.xml
| <?xml version="1.0" encoding="UTF-8" ?> <!-- 定义文档类型为mybatis的sql映射文件 --> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" ??? "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.me.homesickness.mybatis.mapper.UserMybatisMapper"> ? <!-- SQL注入测试 --> ? <select id="login" parameterType="com.me.homesickness.mybatis.pojo.UserMybatis" ? ????????????? resultType="com.me.homesickness.mybatis.pojo.UserMybatis"> ? ? SELECT id ,username as userName FROM user_mybatis WHERE username='${userName}' AND password='${passWord}' ? </select> </mapper> |
4.5.测试SQL注入
| package com.me.homesickness.mybatis.test; import java.io.IOException; import java.io.Reader; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.me.homesickness.mybatis.pojo.UserMybatis; /** ?* Mybatis的SQL注入 ?* @author Administrator ?* ?*/ public class MybatisSqlInjection { ??? public static void main(String[] args) throws IOException { ??????? String resource = "config/SqlMapConfig.xml"; ??????? String userName = "' OR 1=1 -- '"; ??????? //String userName = "zhangsan"; ??????? String password = "hdhdfbhgs+++jse"; ??????? ??????? // 1.读取配置文件 ??????? Reader reader = Resources.getResourceAsReader(resource); ??????? ??????? // 2.获取会话工厂 ??????? SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); ??????? // 3.从会话工厂里获取SqlSession对象 ??????? SqlSession openSession = sqlSessionFactory.openSession(); ??????? ??????? // 4.指定查询语句 ??????? String sql = "com.me.homesickness.mybatis.mapper.UserMybatisMapper.login"; ??????? ??????? // 5.调用api查询 ??????? UserMybatis userMybatis = new UserMybatis(); ??????? userMybatis.setUserName(userName); ??????? userMybatis.setPassWord(password); ??????? List<UserMybatis> listUserMybatis = openSession.selectList(sql, userMybatis); ??????? listUserMybatis.forEach(user-> { ??????????? System.out.println(user.getUserName()); ??????? }); ??? } } |
4.6.执行结果
4.7.解决sql注入，在mapper.xml文件中将符号${}替换为符号#{}
| <?xml version="1.0" encoding="UTF-8" ?> <!-- 定义文档类型为mybatis的sql映射文件 --> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" ??? "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.me.homesickness.mybatis.mapper.UserMybatisMapper"> ? <!-- SQL注入测试 --> ? <!-- <select id="login" parameterType="com.me.homesickness.mybatis.pojo.UserMybatis" ? ????????????? resultType="com.me.homesickness.mybatis.pojo.UserMybatis"> ? ? SELECT id ,username as userName FROM user_mybatis WHERE username='#{userName}' AND password='#{passWord}' ? </select> --> ? <select id="login" parameterType="com.me.homesickness.mybatis.pojo.UserMybatis" ? ????????????? resultType="com.me.homesickness.mybatis.pojo.UserMybatis"> ? ? SELECT id ,username as userName FROM user_mybatis WHERE username=#{userName} AND password=#{passWord} ? </select> </mapper> |
4.8.执行结果
4.9.总结
1）优先使用 #{}。因为 ${} 会导致 sql 注入的问题
?2）#{}被符号”’”包裹时会报错，使用时需要去掉符号"'"
参照:
https://baike.baidu.com/item/sql%E6%B3%A8%E5%85%A5/150289?fr=aladdin
|