实验二 电子传输系统安全-进展2

上周任务完成情况（代码链接，所写文档等）

• 重新调通电子公文传输系统
• 部署gmssl
• 学习生成SM2证书
• 学习gmssl中的CTLS实现
• 将数据库从SqlServer迁移到Mysql
• 调试Mysql驱动
• 学习Bouncy Castle

代码链接

Mysql表设计

/*
Navicat MySQL Data TransferSource Server         : localhost
Source Server Version : 50555
Source Host           : localhost:3306
Source Database       : gwlzxtTarget Server Type    : MYSQL
Target Server Version : 50555
File Encoding         : 65001Date: 2022-05-29 17:13:08
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`pwd` varchar(50) DEFAULT NULL,`type` varchar(50) DEFAULT NULL,`status` varchar(50) DEFAULT NULL,`enpwd` varchar(64) DEFAULT NULL,`enabled` varchar(255) DEFAULT '0',PRIMARY KEY (`id`),UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of admin
-- ----------------------------
INSERT INTO `admin` VALUES ('1', 'admi', '123', '1', '1', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('2', 'tutu', '123', '2', '1', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1005', 'lhui', '123', '3', '1', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1006', 'niuna', '123', '2', '拟稿人', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1007', 'zhang', '123', '3', '1', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1009', 'ww', '123', '3', '2', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1012', 'yld', '123', '3', '8', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1014', 'lili', '123', '3', '2', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');
INSERT INTO `admin` VALUES ('1015', 'lucy', '123123', '2', '拟稿人', 'c68ac63173fcfc537bf22f19a425977029d7dd35ddc5d76b36e58af222dfda39', '1');
INSERT INTO `admin` VALUES ('1016', 'sys', 'sys', '1', '管理员', '87cbeedaba31cfc108bff03a619bff198321a46235d4d687790605e505c7fd48', '1');
INSERT INTO `admin` VALUES ('1017', 'test', 'test', '3', '8', '55e12e91650d2fec56ec74e1d3e4ddbfce2ef3a65890c2a19ecf88a307e76a23', '0');
INSERT INTO `admin` VALUES ('1023', 'QQ', '123', '3', '8', '6e0f9e14344c5406a0cf5a3b4dfb665f87f4a771a31f7edbb5c72874a32b2957', '1');-- ----------------------------
-- Table structure for dep
-- ----------------------------
DROP TABLE IF EXISTS `dep`;
CREATE TABLE `dep` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of dep
-- ----------------------------
INSERT INTO `dep` VALUES ('14', '1');
INSERT INTO `dep` VALUES ('13', 'admi');
INSERT INTO `dep` VALUES ('1', '信息部');
INSERT INTO `dep` VALUES ('2', '外语学院');
INSERT INTO `dep` VALUES ('8', '院领导');-- ----------------------------
-- Table structure for document
-- ----------------------------
DROP TABLE IF EXISTS `document`;
CREATE TABLE `document` (`id` int(11) NOT NULL AUTO_INCREMENT,`admin` varchar(50) DEFAULT NULL,`title` varchar(200) DEFAULT NULL,`content` text,`mode` varchar(50) DEFAULT NULL,`dep` varchar(50) DEFAULT NULL,`dates` datetime DEFAULT NULL,`lingdao` varchar(50) DEFAULT NULL,`taohong` varchar(50) DEFAULT NULL,`enkey` varchar(50) DEFAULT NULL,`encontent` text,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of document
-- ----------------------------
INSERT INTO `document` VALUES ('10', '2', '通知', '', '普通审批', '2', '2022-05-29 00:00:00', '1', '0', 'ZkhSDHN31KjEd68S', '6c7eee9e5aba6e3f95eaf45983d181147e24ecfee4c0388ad7a7c4c452c04cc8');
INSERT INTO `document` VALUES ('11', '2', '测试', '', '会审审批', '1', '2022-05-29 00:00:00', '1', '0', 'QUKuTFodLJYMIxxS', '2b37c6b64609f102f9bd4ab8a9420284');-- ----------------------------
-- Table structure for examine
-- ----------------------------
DROP TABLE IF EXISTS `examine`;
CREATE TABLE `examine` (`id` int(11) NOT NULL AUTO_INCREMENT,`document` int(11) DEFAULT NULL,`dep` int(11) DEFAULT NULL,`state` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of examine
-- ----------------------------
INSERT INTO `examine` VALUES ('10', '10', '8', '1');
INSERT INTO `examine` VALUES ('11', '11', '8', '1');

Mysql驱动调试

import java.sql.*;
public class SQLConnection {static String dbDriver="com.mysql.jdbc.Driver";		static{try{//Class.forName(dbDriver).newInstance(); 
//        	Class.forName("com.mysql.jdbc.Driver"); //      	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(Exception ex){System.out.print("---------------------om.microsoft.sqlserver.jdbc.SQLServerDri");ex.printStackTrace();}}public static Connection getConn(){try{Class.forName(dbDriver).newInstance(); Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/gwlzxt?user=root&password=root&useUnicode=true&characterEncoding=GBK");
//        	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/northwind","root","123");//       	Connection conn=DriverManager.getConnection("jdbc:odbc:twtweb");return conn;}catch(Exception ex){ex.printStackTrace();return null;}}

学习Bouncy Castle

package helper;import org.bouncycastle.jce.provider.BouncyCastleProvider;import cn.hutool.crypto.symmetric.SymmetricCrypto;import javax.crypto.BadPaddingException;
import javax.crypto.Cipher;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import java.io.UnsupportedEncodingException;
import java.security.*;
import java.util.Random;public class FourJiaMi {static {Security.addProvider(new BouncyCastleProvider());}// key必须是16字节，即128位final static String key = "sm4demo123456789";// 指明加密算法和秘钥// 加密为16进制，也可以加密成base64/字节数组public static String encryptSm4(String keys, String plaintext) {SymmetricCrypto sm4 = new SymmetricCrypto("SM4/ECB/PKCS5Padding", keys.getBytes());return sm4.encryptHex(plaintext);}// 解密public static String decryptSm4(String keys, String ciphertext) {SymmetricCrypto sm4 = new SymmetricCrypto("SM4/ECB/PKCS5Padding", keys.getBytes());return sm4.decryptStr(ciphertext);}public static String getRandomString(int length) {String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";Random random = new Random();StringBuffer sb = new StringBuffer();for (int i = 0; i < length; i++) {int number = random.nextInt(62);sb.append(str.charAt(number));}return sb.toString();}public static void main(String[] args) {String content = "hello sm4";String plain = encryptSm4(key,content);String cipher = decryptSm4(key,plain);System.out.println(plain + "\n" + cipher);}
}