MySql之Sql注入的产生与预防

最新推荐文章于 2022-04-19 11:15:34 发布

Hwweic

最新推荐文章于 2022-04-19 11:15:34 发布

阅读量222

点赞数

分类专栏： Java 文章标签： preparedStatement SQL注入

本文链接：https://blog.csdn.net/FloatDreamed/article/details/98517898

版权

Java 专栏收录该内容

72 篇文章 0 订阅

订阅专栏

相信大家对于SQL注入这种东西并不陌生!

仍记得很久以前尝试利用SQL注入登录系统的时候.....

什么是SQL注入呢?

其实就是用户输入的内容作为SQL语句语法的一部分,从而改变程序内原有设定的SQL的真正意义

下面就以案例的形式为大家演示SQL注入的过程:

一.案例展示

1.测试准备

①MySql数据库一枚
②database名为mysqlstudy,且表尾admin
③maven创建的webapp项目工程

2.代码展示:

***pom.xml

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.46</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>javax.servlet.jsp-api</artifactId>
      <version>2.3.1</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.1.2</version>
    </dependency>
  </dependencies>

***index.jsp

<%--
  Created by IntelliJ IDEA.
  User: WHW
  Date: 2019/8/5
  Time: 19:49
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>测试SQL注入</title>
</head>
<body>
    <fieldset>
        <form action="/us" method="post">
            用户名:<input type="text" name="username"/><br/>
            密　码:<input type="text" name="password"/><br/>
            <input type="button" value="Login">
        </form>
    </fieldset>
</body>
</html>

***Admin.java

package com.howie.pojo;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 19:18
 * @Version 1.0
 */
public class Admin {
    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;
    }

    @Override
    public String toString() {
        return "admin{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

***JDBCUtils.java

package com.howie.utils;

import java.sql.*;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 18:11
 * @Version 1.0
 */
public class JDBCUtils {

    //1.数据库驱动信息
    private static String driver = "com.mysql.jdbc.Driver";
    //2.数据库连接信息
    private static String url =
            "jdbc:mysql://localhost:3306/mysqlstudy?characterEncoding=utf8";
    private static String username = "root";
    private static String password = "root";

    //3.静态代码块用于获取MySql驱动
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 4.工具提供获取数据库连接的方法
     * @return
     */
    public static Connection getConnection() throws SQLException {
        //5.获取数据库连接并返回
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 5.释放资源
     * @param rs 结果集
     * @param stat sql执行者
     * @param conn 数据库连接对象
     */
    public static void closeResource(ResultSet rs, Statement stat,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

***UserServlet.java

package com.howie.vertify;

import com.howie.pojo.Admin;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Map;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 19:14
 * @Version 1.0
 */
@WebServlet("/us")
public class UserServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Map<String, String[]> parameterMap = req.getParameterMap();
        Admin admin = new Admin();
        for (String fieldName : parameterMap.keySet()) {
            if ("username".equals(fieldName)){
                admin.setUsername(parameterMap.get(fieldName)[0]);
            }
            if ("password".equals(fieldName)){
                admin.setPassword(parameterMap.get(fieldName)[0]);
            }
        }
        VertifyAuthority va = new VertifyAuthority();
        if (va.vertify(admin)){
            resp.getWriter().write("Login Success!");
        }else {
            resp.getWriter().write("Login Failed!");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}

***VertifyAuthority.java

package com.howie.vertify;

import com.howie.pojo.Admin;
import com.howie.utils.JDBCUtils;

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

/**
 * @Author weihuanwen
 * @Date 2019/8/5 19:25
 * @Version 1.0
 */
public class VertifyAuthority {

    public boolean vertify(Admin admin){
        try {
            Connection conn = JDBCUtils.getConnection();
            Statement stat = conn.createStatement();
            String sql = "SELECT * FROM `admin` WHERE `username`='"+admin.getUsername()+"' and `password`='"+admin.getPassword()+"'";
            ResultSet rs = stat.executeQuery(sql);
            if (rs.next()){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

3.测试:

①启动项目在前端页面中输入数据库中已存在的用户名和密码

②登录结果如下:

③输入数据库中不存在的用户名,且密码输入:[whatever' or 'a'='a]

④登录结果如下:

4.总结:

用户可以通过输入改变程序设计的sql语句的原本含义

二.预防SQL注入

1.代码修改

***VertifyAuthority.java

package com.howie.vertify;

import com.howie.pojo.Admin;
import com.howie.utils.JDBCUtils;

import java.sql.*;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 21:09
 * @Version 1.0
 */
public class VertifyAuthority {

    public boolean vertify(Admin admin){
        try {
            Connection conn = JDBCUtils.getConnection();
            //sql语句中使用占位符
            String sql = "SELECT * FROM `admin` WHERE `username`=? and `password`=?";
            //获取sql语句预处理对象
            PreparedStatement ps = conn.prepareStatement(sql);
            //设置第一个占位符的值
            ps.setObject(1,admin.getUsername());
            //设置第二个占位符的值
            ps.setObject(2,admin.getPassword());
            //执行查询
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

2.测试

①输入数据库中不存在的用户名,且密码输入:[whatever' or 'a'='a]