CVE-2023-52285: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: ExamSys 9150244 allows SQL Injection via the /Support/action/Pages.php s_score2 parameter.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 7.5
Published: 2024-01-17T08:15Z
Repository: https://github.com/lrx0014/ExamSys
Language: Markdown
Files changed: README.md, k8s-all.yaml
Commit date: 2021-11-28 18:32:19 +0800
Commit message: update README

Diff: diff --git a/README.md b/README.md
index 723673e..f9f7204 100644
--- a/README.md
+++ b/README.md
@@ -8,16 +8,27 @@ ExamSys在线考试系统(WEB)
   docker-compose up -d
 ``` 
 
 即可一键部署ExamSys考试系统, 浏览器访问以下地址即可进入ExamSys主页(数据库初始化需要一段时间)：
 ```shell
-http://localhost:80
+http://localhost
 ```
 
-<hr>
+在k8s上部署：
+```shell
+kubectl -n <your_ns> create -f k8s-all.yaml
+```
+
+**给想抄作业的同学们**
 
-ExamSys is an online exam system based on Web; the front end is constructed by HTML+JS+CSS. The back end is realized by PHP-MySL with the application of BootStrap and JQuery framework and AJAX JSON technologies; the project constructs a simple and easy user interface, which is divided into two parts of student and teacher terminal; after logging in, students can choose freely random question bank for exercise, and they can check their performance; teacher accounts can make corresponding management over question bank, details, and performance; the system supports all kinds of question types (multiple choice, blank filling and judging) and can mark automatically the objective questions. 
+这个项目已经年代久远，基本不会再维护了
+
+它延伸自某次课设任务，几乎没有使用那些高级php框架、实现简单粗暴，适合本科入门的同学用作参考
+
+ps. 作业还是要自己写哦 ;)
+
+<hr>
 
 ExamSys是一个基于Web的在线考试系统，前端使用HTML+JS+CSS构建，后端使用PHP+MySQL实现，应用了BootStrap和JQuery框架以及AJAX、JSON等技术；程序构建了一个清爽简单的用户界面，分为学生端和教师端两部分，学生登录系统后可以自由选择任意题库进行答题练习，也可随时查看自己的答题情况，教师账户可对题库、题目细节、学生成绩等进行相应的管理操作。系统支持各种主流题型(单选多选、填空、判断)，对客观题可以进行自动判分 。设计还有许多不如意的地方, 以后会慢慢改进, 慢慢完善这个系统, 使其可以满足大部分在线测试的需求. <br>
 
   Author  | Email
   ------------- | -------------
diff --git a/k8s-all.yaml b/k8s-all.yaml
new file mode 100644
index 0000000..7ebbd46
--- /dev/null
+++ b/k8s-all.yaml
@@ -0,0 +1,521 @@
+kind: ConfigMap
+apiVersion: v1
+metadata:
+  name: examsys-initdb-sql
+data:
+  initdb.sql: >
+    CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
+
+    GRANT All privileges ON *.* TO 'admin'@'%';
+
+
+    SET FOREIGN_KEY_CHECKS=0;
+
+    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
+
+    SET AUTOCOMMIT = 0;
+
+    START TRANSACTION;
+
+    SET time_zone = "+00:00";
+
+
+    DROP DATABASE IF EXISTS examdb;  
+
+    CREATE DATABASE examdb;  
+
+    USE examdb;  
+
+
+    --
+
+    -- Database: `examdb`
+
+    --
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 替换视图以便查看 `gradeview`
+
+    -- (See below for the actual view)
+
+    --
+
+    DROP VIEW IF EXISTS `gradeview`;
+
+    CREATE TABLE IF NOT EXISTS `gradeview` (
+
+    `StuId` int(11)
+
+    ,`StuName` varchar(20)
+
+    ,`lastTime` datetime
+
+    ,`total` decimal(32,0)
+
+    );
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 替换视图以便查看 `lasttimeview`
+
+    -- (See below for the actual view)
+
+    --
+
+    DROP VIEW IF EXISTS `lasttimeview`;
+
+    CREATE TABLE IF NOT EXISTS `lasttimeview` (
+
+    `stuid` int(11)
+
+    ,`lastTime` datetime
+
+    );
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `loginhistory`
+
+    --
+
+
+    DROP TABLE IF EXISTS `loginhistory`;
+
+    CREATE TABLE IF NOT EXISTS `loginhistory` (
+      `stuid` int(11) DEFAULT NULL,
+      `isTeacher` tinyint(1) DEFAULT '0',
+      `LoginTime` datetime DEFAULT NULL
+    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 替换视图以便查看 `nextquestion`
+
+    -- (See below for the actual view)
+
+    --
+
+    DROP VIEW IF EXISTS `nextquestion`;
+
+    CREATE TABLE IF NOT EXISTS `nextquestion` (
+
+    `Qid` int(11)
+
+    );
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `question`
+
+    --
+
+
+    DROP TABLE IF EXISTS `question`;
+
+    CREATE TABLE IF NOT EXISTS `question` (
+      `Qid` int(11) NOT NULL AUTO_INCREMENT,
+      `Qcontent` text CHARACTER SET utf8,
+      `QAnswer` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
+      `QScore` int(11) DEFAULT NULL,
+      `TeacherId` int(11) DEFAULT NULL,
+      `CreateTime` datetime DEFAULT NULL,
+      `QChoice` varchar(255) CHARACTER SET utf8 NOT NULL,
+      PRIMARY KEY (`Qid`),
+      KEY `TeacherId` (`TeacherId`)
+    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `question_sets`
+
+    --
+
+
+    DROP TABLE IF EXISTS `question_sets`;
+
+    CREATE TABLE IF NOT EXISTS `question_sets` (
+      `QsetId` int(11) NOT NULL AUTO_INCREMENT,
+      `QsetName` varchar(255) NOT NULL,
+      `Qinclude` varchar(255) NOT NULL,
+      `CreateTime` datetime DEFAULT NULL,
+      `Author` varchar(255) DEFAULT NULL,
+      PRIMARY KEY (`QsetId`)
+    ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `student`
+
+    --
+
+
+    DROP TABLE IF EXISTS `student`;
+
+    CREATE TABLE IF NOT EXISTS `student` (
+      `StuId` int(11) NOT NULL,
+      `StuName` varchar(20) DEFAULT NULL,
+      `StuPassword` varchar(255) DEFAULT NULL,
+      PRIMARY KEY (`StuId`)
+    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `stugrade`
+
+    --
+
+
+    DROP TABLE IF EXISTS `stugrade`;
+
+    CREATE TABLE IF NOT EXISTS `stugrade` (
+      `Stuid` int(11) NOT NULL,
+      `SetId` int(11) NOT NULL,
+      `RightCNT` int(11) NOT NULL,
+      `WrongCNT` int(11) NOT NULL,
+      `Total` int(11) NOT NULL,
+      `StartTime` datetime DEFAULT NULL,
+      `FinishTime` datetime DEFAULT NULL
+    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 替换视图以便查看 `stuview`
+
+    -- (See below for the actual view)
+
+    --
+
+    DROP VIEW IF EXISTS `stuview`;
+
+    CREATE TABLE IF NOT EXISTS `stuview` (
+
+    `StuId` int(11)
+
+    ,`StuName` varchar(20)
+
+    ,`lastTime` datetime
+
+    );
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `teacher`
+
+    --
+
+
+    DROP TABLE IF EXISTS `teacher`;
+
+    CREATE TABLE IF NOT EXISTS `teacher` (
+      `TeacherId` int(11) NOT NULL,
+      `TeacherName` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
+      `TeacherPassword` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
+      PRIMARY KEY (`TeacherId`)
+    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 表的结构 `testhistory`
+
+    --
+
+
+    DROP TABLE IF EXISTS `testhistory`;
+
+    CREATE TABLE IF NOT EXISTS `testhistory` (
+      `StuId` int(11) DEFAULT NULL,
+      `Qset` int(11) NOT NULL,
+      `Qid` int(11) DEFAULT NULL,
+      `StuChoise` varchar(20) DEFAULT NULL,
+      `StuScore` int(11) DEFAULT NULL,
+      `TestTime` datetime DEFAULT NULL,
+      KEY `StuId` (`StuId`),
+      KEY `Qid` (`Qid`)
+    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 替换视图以便查看 `totalscore`
+
+    -- (See below for the actual view)
+
+    --
+
+    DROP VIEW IF EXISTS `totalscore`;
+
+    CREATE TABLE IF NOT EXISTS `totalscore` (
+
+    `StuId` int(11)
+
+    ,`total` decimal(32,0)
+
+    );
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 视图结构 `gradeview`
+
+    --
+
+    DROP TABLE IF EXISTS `gradeview`;
+
+
+    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `gradeview`  AS  select
+    `stuview`.`StuId` AS `StuId`,`stuview`.`StuName` AS
+    `StuName`,`stuview`.`lastTime` AS `lastTime`,`totalscore`.`total` AS `total`
+    from (`stuview` join `totalscore`) where (`stuview`.`StuId` =
+    `totalscore`.`StuId`) ;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 视图结构 `lasttimeview`
+
+    --
+
+    DROP TABLE IF EXISTS `lasttimeview`;
+
+
+    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `lasttimeview`  AS 
+    select `loginhistory`.`stuid` AS `stuid`,max(`loginhistory`.`LoginTime`) AS
+    `lastTime` from `loginhistory` where (`loginhistory`.`isTeacher` = 0) group
+    by `loginhistory`.`stuid` ;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 视图结构 `nextquestion`
+
+    --
+
+    DROP TABLE IF EXISTS `nextquestion`;
+
+
+    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `nextquestion`  AS 
+    select `question`.`Qid` AS `Qid` from (`question` left join (select
+    `testhistory`.`Qid` AS `i` from `testhistory`) `t1` on((`question`.`Qid` =
+    `t1`.`i`))) where isnull(`t1`.`i`) ;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 视图结构 `stuview`
+
+    --
+
+    DROP TABLE IF EXISTS `stuview`;
+
+
+    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `stuview`  AS  select
+    `student`.`StuId` AS `StuId`,`student`.`StuName` AS
+    `StuName`,`lasttimeview`.`lastTime` AS `lastTime` from (`student` join
+    `lasttimeview`) where (`student`.`StuId` = `lasttimeview`.`stuid`) ;
+
+
+    -- --------------------------------------------------------
+
+
+    --
+
+    -- 视图结构 `totalscore`
+
+    --
+
+    DROP TABLE IF EXISTS `totalscore`;
+
+
+    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `totalscore`  AS 
+    select `testhistory`.`StuId` AS `StuId`,sum(`testhistory`.`StuScore`) AS
+    `total` from `testhistory` group by `testhistory`.`StuId` ;
+
+
+    --
+
+    -- 限制导出的表
+
+    --
+
+
+    --
+
+    -- 限制表 `question`
+
+    --
+
+    ALTER TABLE `question`
+      ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`TeacherId`) REFERENCES `teacher` (`TeacherId`);
+
+    --
+
+    -- 限制表 `testhistory`
+
+    --
+
+    ALTER TABLE `testhistory`
+      ADD CONSTRAINT `testhistory_ibfk_1` FOREIGN KEY (`StuId`) REFERENCES `student` (`StuId`),
+      ADD CONSTRAINT `testhistory_ibfk_2` FOREIGN KEY (`Qid`) REFERENCES `question` (`Qid`);
+    SET FOREIGN_KEY_CHECKS=1;
+
+    COMMIT;
+---
+kind: Deployment
+apiVersion: apps/v1
+metadata:
+  name: mysql
+  labels:
+    k8s-app: mysql
+spec:
+  replicas: 1
+  selector:
+    matchLabels:
+      k8s-app: mysql
+  template:
+    metadata:
+      name: mysql
+      labels:
+        k8s-app: mysql
+    spec:
+      containers:
+        - name: mysql
+          image: mysql/mysql-server:5.7
+          imagePullPolicy: IfNotPresent
+          securityContext:
+            privileged: false
+          volumeMounts:
+            - name: mysql-initdb
+              mountPath: /docker-entrypoint-initdb.d
+      volumes:
+        - name: mysql-initdb
+          configMap:
+            name: examsys-initdb-sql
+      restartPolicy: Always
+      terminationGracePeriodSeconds: 30
+      dnsPolicy: ClusterFirst
+      securityContext: {}
+---
+kind: Deployment
+apiVersion: apps/v1
+metadata:
+  name: examsys
+  labels:
+    k8s-app: examsys
+spec:
+  replicas: 1
+  selector:
+    matchLabels:
+      k8s-app: examsys
+  template:
+    metadata:
+      name: examsys
+      labels:
+        k8s-app: examsys
+    spec:
+      containers:
+        - name: examsys
+          image: lrx0014/examsys:php56
+          env:
+            - name: DB_ADDR
+              value: mysql:3306
+            - name: DB_USER
+              value: admin
+            - name: DB_PWD
+              value: admin
+          imagePullPolicy: IfNotPresent
+          securityContext:
+            privileged: false
+      restartPolicy: Always
+---
+kind: Service
+apiVersion: v1
+metadata:
+  name: mysql
+  labels:
+    k8s-app: mysql
+spec:
+  ports:
+    - name: tcp-3306
+      protocol: TCP
+      port: 3306
+      targetPort: 3306
+  selector:
+    k8s-app: mysql
+  type: NodePort
+---
+kind: Service
+apiVersion: v1
+metadata:
+  name: examsys
+  labels:
+    k8s-app: examsys
+spec:
+  ports:
+    - name: tcp-80
+      protocol: TCP
+      port: 80
+      targetPort: 80
+  selector:
+    k8s-app: examsys
+  type: NodePort
+

Vulnerable code: http://localhost:80
<hr>
ExamSys is an online exam system based on Web; the front end is constructed by HTML+JS+CSS. The back end is realized by PHP-MySL with the application of BootStrap and JQuery framework and AJAX JSON technologies; the project constructs a simple and easy user interface, which is divided into two parts of student and teacher terminal; after logging in, students can choose freely random question bank for exercise, and they can check their performance; teacher accounts can make corresponding management over question bank, details, and performance; the system supports all kinds of question types (multiple choice, blank filling and judging) and can mark automatically the objective questions. 
Fixed code: http://localhost
在k8s上部署：
```shell
kubectl -n <your_ns> create -f k8s-all.yaml
```

**给想抄作业的同学们**
这个项目已经年代久远，基本不会再维护了

它延伸自某次课设任务，几乎没有使用那些高级php框架、实现简单粗暴，适合本科入门的同学用作参考

ps. 作业还是要自己写哦 ;)

<hr>
kind: ConfigMap
apiVersion: v1
metadata:
  name: examsys-initdb-sql
data:
  initdb.sql: >
    CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';

    GRANT All privileges ON *.* TO 'admin'@'%';


    SET FOREIGN_KEY_CHECKS=0;

    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

    SET AUTOCOMMIT = 0;

    START TRANSACTION;

    SET time_zone = "+00:00";


    DROP DATABASE IF EXISTS examdb;  

    CREATE DATABASE examdb;  

    USE examdb;  


    --

    -- Database: `examdb`

    --


    -- --------------------------------------------------------


    --

    -- 替换视图以便查看 `gradeview`

    -- (See below for the actual view)

    --

    DROP VIEW IF EXISTS `gradeview`;

    CREATE TABLE IF NOT EXISTS `gradeview` (

    `StuId` int(11)

    ,`StuName` varchar(20)

    ,`lastTime` datetime

    ,`total` decimal(32,0)

    );


    -- --------------------------------------------------------


    --

    -- 替换视图以便查看 `lasttimeview`

    -- (See below for the actual view)

    --

    DROP VIEW IF EXISTS `lasttimeview`;

    CREATE TABLE IF NOT EXISTS `lasttimeview` (

    `stuid` int(11)

    ,`lastTime` datetime

    );


    -- --------------------------------------------------------


    --

    -- 表的结构 `loginhistory`

    --


    DROP TABLE IF EXISTS `loginhistory`;

    CREATE TABLE IF NOT EXISTS `loginhistory` (
      `stuid` int(11) DEFAULT NULL,
      `isTeacher` tinyint(1) DEFAULT '0',
      `LoginTime` datetime DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 替换视图以便查看 `nextquestion`

    -- (See below for the actual view)

    --

    DROP VIEW IF EXISTS `nextquestion`;

    CREATE TABLE IF NOT EXISTS `nextquestion` (

    `Qid` int(11)

    );


    -- --------------------------------------------------------


    --

    -- 表的结构 `question`

    --


    DROP TABLE IF EXISTS `question`;

    CREATE TABLE IF NOT EXISTS `question` (
      `Qid` int(11) NOT NULL AUTO_INCREMENT,
      `Qcontent` text CHARACTER SET utf8,
      `QAnswer` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      `QScore` int(11) DEFAULT NULL,
      `TeacherId` int(11) DEFAULT NULL,
      `CreateTime` datetime DEFAULT NULL,
      `QChoice` varchar(255) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (`Qid`),
      KEY `TeacherId` (`TeacherId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 表的结构 `question_sets`

    --


    DROP TABLE IF EXISTS `question_sets`;

    CREATE TABLE IF NOT EXISTS `question_sets` (
      `QsetId` int(11) NOT NULL AUTO_INCREMENT,
      `QsetName` varchar(255) NOT NULL,
      `Qinclude` varchar(255) NOT NULL,
      `CreateTime` datetime DEFAULT NULL,
      `Author` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`QsetId`)
    ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 表的结构 `student`

    --


    DROP TABLE IF EXISTS `student`;

    CREATE TABLE IF NOT EXISTS `student` (
      `StuId` int(11) NOT NULL,
      `StuName` varchar(20) DEFAULT NULL,
      `StuPassword` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`StuId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 表的结构 `stugrade`

    --


    DROP TABLE IF EXISTS `stugrade`;

    CREATE TABLE IF NOT EXISTS `stugrade` (
      `Stuid` int(11) NOT NULL,
      `SetId` int(11) NOT NULL,
      `RightCNT` int(11) NOT NULL,
      `WrongCNT` int(11) NOT NULL,
      `Total` int(11) NOT NULL,
      `StartTime` datetime DEFAULT NULL,
      `FinishTime` datetime DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 替换视图以便查看 `stuview`

    -- (See below for the actual view)

    --

    DROP VIEW IF EXISTS `stuview`;

    CREATE TABLE IF NOT EXISTS `stuview` (

    `StuId` int(11)

    ,`StuName` varchar(20)

    ,`lastTime` datetime

    );


    -- --------------------------------------------------------


    --

    -- 表的结构 `teacher`

    --


    DROP TABLE IF EXISTS `teacher`;

    CREATE TABLE IF NOT EXISTS `teacher` (
      `TeacherId` int(11) NOT NULL,
      `TeacherName` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      `TeacherPassword` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
      PRIMARY KEY (`TeacherId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 表的结构 `testhistory`

    --


    DROP TABLE IF EXISTS `testhistory`;

    CREATE TABLE IF NOT EXISTS `testhistory` (
      `StuId` int(11) DEFAULT NULL,
      `Qset` int(11) NOT NULL,
      `Qid` int(11) DEFAULT NULL,
      `StuChoise` varchar(20) DEFAULT NULL,
      `StuScore` int(11) DEFAULT NULL,
      `TestTime` datetime DEFAULT NULL,
      KEY `StuId` (`StuId`),
      KEY `Qid` (`Qid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


    -- --------------------------------------------------------


    --

    -- 替换视图以便查看 `totalscore`

    -- (See below for the actual view)

    --

    DROP VIEW IF EXISTS `totalscore`;

    CREATE TABLE IF NOT EXISTS `totalscore` (

    `StuId` int(11)

    ,`total` decimal(32,0)

    );


    -- --------------------------------------------------------


    --

    -- 视图结构 `gradeview`

    --

    DROP TABLE IF EXISTS `gradeview`;


    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `gradeview`  AS  select
    `stuview`.`StuId` AS `StuId`,`stuview`.`StuName` AS
    `StuName`,`stuview`.`lastTime` AS `lastTime`,`totalscore`.`total` AS `total`
    from (`stuview` join `totalscore`) where (`stuview`.`StuId` =
    `totalscore`.`StuId`) ;


    -- --------------------------------------------------------


    --

    -- 视图结构 `lasttimeview`

    --

    DROP TABLE IF EXISTS `lasttimeview`;


    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `lasttimeview`  AS 
    select `loginhistory`.`stuid` AS `stuid`,max(`loginhistory`.`LoginTime`) AS
    `lastTime` from `loginhistory` where (`loginhistory`.`isTeacher` = 0) group
    by `loginhistory`.`stuid` ;


    -- --------------------------------------------------------


    --

    -- 视图结构 `nextquestion`

    --

    DROP TABLE IF EXISTS `nextquestion`;


    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `nextquestion`  AS 
    select `question`.`Qid` AS `Qid` from (`question` left join (select
    `testhistory`.`Qid` AS `i` from `testhistory`) `t1` on((`question`.`Qid` =
    `t1`.`i`))) where isnull(`t1`.`i`) ;


    -- --------------------------------------------------------


    --

    -- 视图结构 `stuview`

    --

    DROP TABLE IF EXISTS `stuview`;


    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `stuview`  AS  select
    `student`.`StuId` AS `StuId`,`student`.`StuName` AS
    `StuName`,`lasttimeview`.`lastTime` AS `lastTime` from (`student` join
    `lasttimeview`) where (`student`.`StuId` = `lasttimeview`.`stuid`) ;


    -- --------------------------------------------------------


    --

    -- 视图结构 `totalscore`

    --

    DROP TABLE IF EXISTS `totalscore`;


    CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `totalscore`  AS 
    select `testhistory`.`StuId` AS `StuId`,sum(`testhistory`.`StuScore`) AS
    `total` from `testhistory` group by `testhistory`.`StuId` ;


    --

    -- 限制导出的表

    --


    --

    -- 限制表 `question`

    --

    ALTER TABLE `question`
      ADD CONSTRAINT `question_ibfk_1` FOREIGN KEY (`TeacherId`) REFERENCES `teacher` (`TeacherId`);

    --

    -- 限制表 `testhistory`

    --

    ALTER TABLE `testhistory`
      ADD CONSTRAINT `testhistory_ibfk_1` FOREIGN KEY (`StuId`) REFERENCES `student` (`StuId`),
      ADD CONSTRAINT `testhistory_ibfk_2` FOREIGN KEY (`Qid`) REFERENCES `question` (`Qid`);
    SET FOREIGN_KEY_CHECKS=1;

    COMMIT;
---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: mysql
  labels:
    k8s-app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      k8s-app: mysql
  template:
    metadata:
      name: mysql
      labels:
        k8s-app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql/mysql-server:5.7
          imagePullPolicy: IfNotPresent
          securityContext:
            privileged: false
          volumeMounts:
            - name: mysql-initdb
              mountPath: /docker-entrypoint-initdb.d
      volumes:
        - name: mysql-initdb
          configMap:
            name: examsys-initdb-sql
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      securityContext: {}
---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: examsys
  labels:
    k8s-app: examsys
spec:
  replicas: 1
  selector:
    matchLabels:
      k8s-app: examsys
  template:
    metadata:
      name: examsys
      labels:
        k8s-app: examsys
    spec:
      containers:
        - name: examsys
          image: lrx0014/examsys:php56
          env:
            - name: DB_ADDR
              value: mysql:3306
            - name: DB_USER
              value: admin
            - name: DB_PWD
              value: admin
          imagePullPolicy: IfNotPresent
          securityContext:
            privileged: false
      restartPolicy: Always
---
kind: Service
apiVersion: v1
metadata:
  name: mysql
  labels:
    k8s-app: mysql
spec:
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
  selector:
    k8s-app: mysql
  type: NodePort
---
kind: Service
apiVersion: v1
metadata:
  name: examsys
  labels:
    k8s-app: examsys
spec:
  ports:
    - name: tcp-80
      protocol: TCP
      port: 80
      targetPort: 80
  selector:
    k8s-app: examsys
  type: NodePort