javaweb - 实验11 - 综合实验

实验要求：

编写一个学生信息管理系统，实现学生信息的注册、登录（动态验证码，点击验证码可以更新）和增删改查，按以下要求完成实验。

1. 建立数据库，创建学生信息表（ID，NICK，PASSWORD，STUNO，STUNAME，SEX，AGE，PHONE，PIC，INTRODUCE），字段信息分别是id，昵称，密码，学号，姓名，性别，年龄，电话，邮箱，照片，个人介绍，id为数据库自增字段，并设置为记录ID
   1. 手工添加一个管理员信息，其中昵称和密码分别是admin和admin
2. 页面
   1. 主页面提供登录功能，未注册用户可以进行注册
   2. 管理员登录时进入学生列表页面，并能够进行查询和删除
   3. 学生登录时，进入个人信息页面，可编辑和保存
3. 学生信息注册页
   1. 能够上传个人照片
   2. 检查填写字段的合理性并在字段旁动态显示检查结果。
      1. 姓名长度（10）
      2. 密码（有大小写和符号）
      3. 年龄（非负）
      4. 电话格式
      5. 邮箱格式
   3. 检查昵称是否已被注册，冲突时提示昵称已被注册（AJAX）
4. 管理员查询页：提供模糊查询，输入学生姓名，能够显示出和该名称匹配的所有学生信息，默认显示所有学生信息列表，查询提交后动态显示学生信息（AJAX），提供导出到Excel的功能（不需要导出图片）。
5. 系统安全
   1. 测试系统是否可以被SQL注入
   2. 测试系统是否可能有XSS（存储型）漏洞风险
   3. 密码是否已经MD5处理

---

演示

 

 

1、环境

1.1 数据库创建

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nick` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `stuno` int(11) NULL DEFAULT 1,
  `stuname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'name',
  `age` int(11) NULL DEFAULT 18,
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `introduce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1.2 依赖pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>javaweb</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>


  <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>

    <!--Easyexcel 所需的三个依赖-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.1.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>


    <!-- 单元测试 -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>

    <!-- servlet -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
      <scope>provided</scope>
    </dependency>

    <!-- jsp -->
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.1.3-b06</version>
      <scope>provided</scope>
    </dependency>

    <!-- mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.5</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.23</version>
    </dependency>

    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>1.4</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.4</version>
    </dependency>



    <!-- jstl -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>

    <!-- jackson -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.0.1</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.0.0</version>
    </dependency>

    <!-- mysql驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.23</version>
    </dependency>

    <!-- 日志 -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

    <!-- 工具包 -->
    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.7.13</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.8</version>
    </dependency>

  </dependencies>

  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory><!--所在的目录-->
        <includes><!--包括目录下的.properties,.xml文件都会扫描到-->
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
      <resource>
        <directory>src/main/webapp</directory><!--所在的目录-->
        <includes><!--包括目录下的.properties,.xml文件都会扫描到-->
          <include>**/*.css</include>
          <include>**/*.js</include>
        </includes>
        <filtering>false</filtering>
      </resource>
    </resources>
  </build>
</project>

1.3 Student实体类

package whd.entities;

import com.alibaba.excel.annotation.ExcelProperty;

public class Student {
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer id;
    @ExcelProperty(value = "学生昵称",index = 1)
    private String nick;
    @ExcelProperty(value = "密码",index = 2)
    private String password;
    @ExcelProperty(value = "学号",index = 3)
    private Integer stuno;
    @ExcelProperty(value = "学生姓名",index = 4)
    private String stuname;
    @ExcelProperty(value = "学生性别",index = 5)
    private String sex;
    @ExcelProperty(value = "学生年龄",index = 6)
    private Integer age;
    @ExcelProperty(value = "学生电话",index = 7)
    private String phone;
    @ExcelProperty(value = "学生头像",index = 8)
    private String pic;
    @ExcelProperty(value = "学生简介",index = 9)
    private String introduce;
    @ExcelProperty(value = "学生邮箱",index = 10)
    private String email;

   get/set ......
}

2、验证码的实现

validate.jsp

<%@ page import="java.util.Random" %>
<%@ page import="java.awt.*" %>
<%@ page import="javax.imageio.ImageIO" %>
<%@ page import="java.awt.image.BufferedImage" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";

    int width = 60;
    int height = 20;

    BufferedImage image = new BufferedImage(width,height,BufferedImage.TYPE_INT_RGB);
    Graphics g = image.getGraphics();
    g.setColor(new Color(200,200,200));
    g.fillRect(0,0,width,height);

    //随机数
    Random random = new Random();
    int randNum = random.nextInt(8999)+1000;
    String randStr = String.valueOf(randNum);
    session.setAttribute("randStr",randStr);

    g.setColor(Color.black);
    g.setFont(new Font("",Font.PLAIN,20));
    g.drawString(randStr,10,17);

    //干扰点
    for (int i = 0; i < 100; i++) {
        int x = random.nextInt(width);
        int y = random.nextInt(height);
        g.drawOval(x,y,1,1);
    }

    //输出图像到页面
    ImageIO.write(image,"JPEG",response.getOutputStream());
    out.clear();
    out = pageContext.pushBody();
%>
<html>
<head>
    <base href="<%=basePath%>">
    <title>验证码</title>
</head>
<body>

</body>
</html>

登录页面调用验证码：

index.jsp

<%@ page import="java.util.List" %>
<%@ page import="java.awt.image.BufferedImage" %>
<%@ page import="java.awt.*" %>
<%@ page import="java.util.Random" %>
<%@ page import="javax.imageio.ImageIO" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<html>
<head>
    <base href="<%=basePath%>">
    <title>学生信息管理系统</title>
    <script src="jquery/jquery-3.5.1.min.js"></script>
</head>
<body>
<h1>学生信息管理系统</h1>
<div style="color: red">${msg}</div>
<form action="/login" method="post" name="loginForm">
    <table>
        <tr>
            <td>用户名</td>
            <td><input type="text" name="username" id="username"></td>
        </tr>
        <tr>
            <td>密码</td>
            <td><input type="text" name="password" id="password"></td>
        </tr>
        <tr>
            <td>验证码：</td>
            <td><input type="text" name="code"></td>
            <td><img name="validate" src="validate.jsp" onclick="refresh()"></td>
        </tr>
        <tr>
            <td><button type="submit">登录</button></td>
            <td><a href="reg.jsp">去注册</a></td>
        </tr>
    </table>

</form>

<script>
    function refresh(){
        loginForm.validate.src = "validate.jsp?id="+Math.random();
    }
</script>

</body>
</html>

3.MD5加密如何实现？

用了Hutool的工具类

在注册的地方加密成MD5保存至数据库

public class RegistServlet extends HttpServlet {

    private RegisterService registerService = new RegisterServiceImpl();

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");

        UserVo vo = new UserVo();
        vo.setNick(username);
        //MD5加密
        String passwordByMD5 = DigestUtil.md5Hex(password);
        vo.setPassword(passwordByMD5);
        boolean regist_success = registerService.regist(vo);
        if (regist_success) {
            System.out.println("插入成功");
            req.getRequestDispatcher("index.jsp").forward(req,resp);
        }else{
            req.getRequestDispatcher("reg.jsp").forward(req,resp);
        }
    }
}

将前端的密码用MD5加密后与数据库查询出来的数据进行比较即可

            //md5加密
            String passwordByMD5 = DigestUtil.md5Hex(password);
            vo.setPassword(passwordByMD5);
            Student stu = loginService.login(vo);

去service层验证和数据库查询出来的密码是否一致。

4.怎么实现导出到excel？

使用阿里的easyexcel，默认导出到D盘下的write.xlsx文件，如果需要让用户自己选择，可以自行修改。

public class OutPutExcelServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("OutPutExcelServlet.doGet");
        String filename = "D:\\write.xlsx";
        EasyExcel.write(filename, Student.class).sheet("学生列表").doWrite(getData());
        resp.setContentType("text/html; charset=UTF-8");
        resp.getWriter().write("导出成功");
    }

    //创建方法返回list集合
    private static List<Student> getData() {
        UserService userService = new UserServiceImpl();
        List<Student> studentList = userService.getStudentList();
        return studentList;
    }
}

5.管理员的页面怎么写？

可以用 c:foreach 标签，也可以自己拼接字符串。都行，这里ajax请求过来我直接拼串了。

<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";

%>
<html>
<head>
    <base href="<%=basePath%>">
    <title>管理员页面</title>
    <script src="jquery/jquery-3.5.1.min.js"></script>
</head>
<body>
<div>
    模糊查询：<input id="searchName" type="text" placeholder="请输入学生姓名">
    <button id="search_button" type="button">搜索</button>
    <button id="output_excel" type="button">导出excel</button>
    <div style="color: green" id="msgDiv">

    </div>
    <div>
        <table id="list" border="1px">
            <tr>
                <td>id</td>
                <td>nick</td>
                <td>password</td>
                <td>stuno</td>
                <td>stuname</td>
                <td>sex</td>
                <td>age</td>
                <td>phone</td>
                <td>pic</td>
                <td>introduce</td>
                <td>email</td>
            </tr>

        </table>
    </div>
</div>
<script>
    $(function () {
        let htmlHead = '';
        htmlHead += "<tr>";
        htmlHead += "    <td>id</td>";
        htmlHead += "    <td>nick</td>";
        htmlHead += "    <td>password</td>";
        htmlHead += "    <td>stuno</td>";
        htmlHead += "    <td>stuname</td>";
        htmlHead += "    <td>sex</td>";
        htmlHead += "    <td>age</td>";
        htmlHead += "    <td>phone</td>";
        htmlHead += "    <td>pic</td>";
        htmlHead += "    <td>introduce</td>";
        htmlHead += "    <td>email</td>";
        htmlHead += "</tr>";
        $("#list").html(htmlHead);

        $.get("userListServlet", function (data) {
            let parse = data;
            let html = '';
            for (let i = 0; i < parse.length; i++) {
                html += "<tr>";
                html += "    <td>" + parse[i].id + "</td>";
                html += "    <td>" + parse[i].nick + "</td>";
                html += "    <td>" + parse[i].password + "</td>";
                html += "    <td>" + parse[i].stuno + "</td>";
                html += "    <td>" + parse[i].stuname + "</td>";
                html += "    <td>" + parse[i].sex + "</td>";
                html += "    <td>" + parse[i].age + "</td>";
                html += "    <td>" + parse[i].phone + "</td>";
                html += "    <td>" + parse[i].pic + "</td>";
                html += "    <td>" + parse[i].introduce + "</td>";
                html += "    <td>" + parse[i].email + "</td>";
                html += "</tr>";
            }
            $("#list").append(html);
        }, 'json');

        $("#search_button").click(function () {
            $.get("searchStudentServlet", {stuName: $("#searchName").val()}, function (data) {
                let parse = data;
                let html = '';

                if (parse != null) {
                    for (let i = 0; i < parse.length; i++) {
                        html += "<tr>";
                        html += "    <td>" + parse[i].id + "</td>";
                        html += "    <td>" + parse[i].nick + "</td>";
                        html += "    <td>" + parse[i].password + "</td>";
                        html += "    <td>" + parse[i].stuno + "</td>";
                        html += "    <td>" + parse[i].stuname + "</td>";
                        html += "    <td>" + parse[i].sex + "</td>";
                        html += "    <td>" + parse[i].age + "</td>";
                        html += "    <td>" + parse[i].phone + "</td>";
                        html += "    <td>" + parse[i].pic + "</td>";
                        html += "    <td>" + parse[i].introduce + "</td>";
                        html += "    <td>" + parse[i].email + "</td>";
                        html += "</tr>";
                    }
                    $("#list").html(htmlHead + html);
                }
            }, 'json')
        })

        $("#output_excel").click(function () {
            console.log(111)
            $.get("outPutExcelServlet", function (data) {
                $("#msgDiv").text(data)
            });
        })
    })
</script>
</body>
</html>

6.用户个人信息页面的各种验证怎么写？

如果需要，这些代码可以完善一下前端，只粗略写完了基本校验。个人认为前端校验并不安全，后端校验是也是必要的。可以自行了解一下JSR 303 规范，由于是javaweb作业，不可用spring框架就暂时搁置。

<%@ page import="java.util.List" %>
<%@ page import="whd.entities.Student" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
//    List<String> nickList = (List<String>) request.getAttribute("nickList");
    List<String> nickList = (List<String>) request.getSession().getAttribute("nickList");
    String nickListToString = "";
    for (int i = 0; i < nickList.size(); i++) {
        nickListToString += nickList.get(i);
        nickListToString += ",";
    }
    request.setAttribute("nickListToString",nickListToString);

    Student stu = (Student) request.getSession().getAttribute("stu");
    String stuId = String.valueOf(stu.getId());
%>
<html>
<head>
    <base href="<%=basePath%>">
    <title>学生个人信息页面</title>
    <script src="jquery/jquery-3.5.1.min.js"></script>
</head>
<body>
<h1>学生个人信息页面</h1>
<%--隐藏域，保存用户昵称列表--%>
<input type="hidden" value="${nickListToString}" id="nickList_hidden">
<div style="color: green">${msg}</div>


<form action="/upload" method="post" enctype="multipart/form-data">
    <tr>
        <td>个人照片：</td>
        <td><input type="file" name="pic"></td>
        <td><div id="picInfo" ></div></td>
    </tr>
    <tr>
        <td><button type="submit">上传图片</button></td>
    </tr>
</form>

<form action="/userInfoServlet" method="post">
    <input type="hidden" value="<%=stuId%>" name="stuId">
    <input type="hidden" value="${pic}" name="pic">
    <table>
        <tr>
            <td>姓名</td>
            <td><input type="text" name="nick" id="nick_input"></td>
            <td><div id="nickInfo" style="color:gray;">请输入姓名，长度不超过10位</div></td>
        </tr>
        <tr>
            <td>密码</td>
            <td><input type="password" name="password" id="password_input"></td>
            <td><div id="passwordInfo" style="color:gray;">请输入密码，包含大小写和符号</div></td>
        </tr>
        <tr>
            <td>年龄</td>
            <td><input type="text" name="age" id="age_input"></td>
            <td><div id="ageInfo" style="color:gray;">请输入年龄，非负</div></td>
        </tr>
        <tr>
            <td>电话</td>
            <td><input type="text" name="phone" id="phone_input"></td>
            <td><div id="phoneInfo" style="color:gray;">请输入正确的电话格式</div></td>
        </tr>
        <tr>
            <td>邮箱</td>
            <td><input type="text" name="email" id="email_input"></td>
            <td><div id="emailInfo" style="color: gray">请输入正确的邮箱格式</div></td>
        </tr>
        <tr>
            <td><button type="submit">保存</button></td>
        </tr>
    </table>
</form>


<br>
<script>
    $(function () {
        //姓名限制长度为10
        $("#nick_input").blur(function () {
            let nick = $("#nick_input").val();
            let userNickList = getUserNickList();
            let nickSplit = userNickList.split(',');
            for (let nickFromSplit of nickSplit) {
                if(nickFromSplit.length!=0 || nickFromSplit !== ''){
                    if(nickFromSplit === nick){
                        $("#nickInfo").text("姓名已存在");
                        $("#nickInfo").css("color","red");
                        return;
                    }
                }
            }
            if(nick.length > 10 || nick.length === 0){
                $("#nickInfo").text("姓名的长度限制1到10位");
                $("#nickInfo").css("color","red");
            } else{
                $("#nickInfo").text("姓名合法");
                $("#nickInfo").css("color","green");
            }
        });

        //密码校验
        $("#password_input").blur(function (){
            let password = $("#password_input").val();
            const pattern = /^(?=.*\d)(?=.*[a-zA-Z])(?=.*[~!@#$%^&*()_\-+=<>?:"{}|,.\/;'\\[\]·~！@#￥%……&*（）——\-+={}|《》？：“”【】、；‘’，。、])[\da-zA-Z~!@#$%^&*()_\-+=<>?:"{}|,.\/;'\\[\]·~！@#￥%……&*（）——\-+={}|《》？：“”【】、；‘’，。、]{8,16}$/;
            console.log(pattern.test(password));
            if(pattern.test(password)){
                $("#passwordInfo").text("密码合法");
                $("#passwordInfo").css("color","green");
            } else{
                $("#passwordInfo").text("密码必须包含大小写字符、数字、特殊符号，并且长度在8到16位");
                $("#passwordInfo").css("color","red");
            }
        });

        //年龄校验
        $("#age_input").blur(function (){
            let age = $("#age_input").val();
            if(age < 0 || age.length === 0){
                $("#ageInfo").text("年龄不能为负数");
                $("#ageInfo").css("color","red");
            } else{
                $("#ageInfo").text("年龄合法");
                $("#ageInfo").css("color","green");
            }
        });

        //电话校验
        $("#phone_input").blur(function (){
            let phone = $("#phone_input").val();
            const pattern = /^[1][3,4,5,7,8][0-9]{9}$/;
            if(!pattern.test(phone)){
                $("#phoneInfo").text("电话不合法");
                $("#phoneInfo").css("color","red");
            } else{
                $("#phoneInfo").text("电话合法");
                $("#phoneInfo").css("color","green");
            }
        });

        //邮箱校验
        $("#email_input").blur(function (){
            let email = $("#email_input").val();
            const pattern = /^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$/;
            if(!pattern.test(email)){
                $("#emailInfo").text("邮箱不合法");
                $("#emailInfo").css("color","red");
            } else{
                $("#emailInfo").text("邮箱合法");
                $("#emailInfo").css("color","green");
            }
        });

        //输入框失去焦点
        $("#nick_input").focus(function (){
            $("#nickInfo").text("请输入姓名");
            $("#nickInfo").css("color","grey");
        })
        $("#password_input").focus(function (){
            $("#passwordInfo").text("请输入密码");
            $("#passwordInfo").css("color","grey");
        })

        function getUserNickList(){
            let nickList = $("#nickList_hidden").val();
            return nickList;
        }

    });


</script>
</body>
</html>