实验二 电子传输系统安全-进展2
项目：电子公文传输系统
上周任务
- 将数据库从SqlServer迁移到Mysql
- 调试Mysql驱动
- 学习Bouncy Castle
上周任务完成情况
| 学号姓名 |
任务 |
完成情况 |
| 20191304商苏赫 |
学习Bouncy Castle |
100％ |
| 20191323王予涵 |
将数据库从SqlServer迁移到Mysql |
100％ |
| 20191314徐汇仁 |
调试Mysql驱动 |
100% |
| 20191325施羿 |
调试Mysql驱动 |
100% |
| 20191330雷清逸 |
调试Mysql驱动 |
100% |
任务片段
Mysql表设计
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50555
Source Host : localhost:3306
Source Database : gwlzxt
Target Server Type : MYSQL
Target Server Version : 50555
File Encoding : 65001
Date: 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);
}
}
