~chenjiahe/hx_common.git

chenjiahe

2022-01-13 af1eee0b36a1705fad873511401b490da7768e56

提交 \| 用户 \| age
c64e12	1	package com.hx.mybatis.aes.springbean;
C	2
	3	import com.alibaba.druid.sql.SQLUtils;
	4	import com.alibaba.druid.sql.ast.SQLExpr;
	5	import com.alibaba.druid.sql.ast.SQLStatement;
	6	import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
	7	import com.alibaba.druid.sql.ast.expr.SQLInListExpr;
	8	import com.alibaba.druid.sql.ast.statement.*;
	9	import com.alibaba.druid.sql.dialect.mysql.ast.clause.MySqlSelectIntoStatement;
	10	import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
	11	import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
	12	import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
	13	import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
	14	import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
	15	import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
	16	import com.alibaba.druid.stat.TableStat;
	17	import com.alibaba.druid.util.JdbcConstants;
	18	import com.alibaba.druid.util.JdbcUtils;
	19	import com.hx.util.StringUtils;
	20
	21	import java.util.Collection;
	22	import java.util.List;
	23	import java.util.Map;
	24
	25	/**
	26	* sql语句处理工具
	27	* @author CJH 2022-01-12
	28	*/
	29	public class SqlUtils {
	30
	31	/**查询加密数据处理，只对查询做处理，select返回不做处理
	32	* @param sql sql语句
	33	* @param aesKeysTable aes秘钥
	34	* @return
	35	*/
	36	public static String selectSql(String sql,Map<String,Map<String,String>> aesKeysTable){
	37
	38	MySqlStatementParser parser = new MySqlStatementParser(sql);
	39	SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect();
	40	//获取格式化的slq语句
	41	sql = sqlStatement.toString();
	42
	43	//解析select查询
	44	//SQLSelect sqlSelect = sqlStatement.getSelect()
	45	//获取sql查询块
	46	SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
	47	StringBuffer out = new StringBuffer() ;
	48	//创建sql解析的标准化输出
	49	SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ;
	50
	51	//获取表和别名
	52	ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
	53	sqlStatement.accept(visitorTable);
	54	Map<String,String> tableMaps = visitorTable.getTableMap();
	55
	56	//获取所有的字段
	57	MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
	58	sqlStatement.accept(visitor);
	59	//遍历所有字段
	60	Collection<TableStat.Column> columns= visitor.getColumns();
	61
	62	StringBuilder sqlWhere = new StringBuilder();
	63
	64	StringBuilder sqlSelect = new StringBuilder();
	65	String expr = null;
	66	sqlSelect.append("SELECT ");
	67	//解析select返回的数据字段项
	68	for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
	69	if(sqlSelect.length() > 7){
	70	sqlSelect.append(",");
	71	}
	72	expr = sqlSelectItem.getExpr().toString();
	73	if(expr.indexOf("SELECT") == -1){
	74	sqlSelect.append(expr);
	75	if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
	76	sqlSelect.append(" AS "+sqlSelectItem.getAlias());
	77	}
	78	}else{
	79	sqlSelect.append("(");
	80	sqlSelect.append(selectSqlHandle(expr,aesKeysTable,tableMaps,columns));
	81	sqlSelect.append(")");
	82	if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
	83	sqlSelect.append(" AS "+sqlSelectItem.getAlias());
	84	}
	85	}
	86	}
	87
	88	//解析from
	89	out.delete(0, out.length()) ;
	90	sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
	91	sqlWhere.append(" FROM "+out);
	92
	93	//解析where
	94	out.delete(0, out.length()) ;
	95	sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
	96	sqlWhere.append(" WHERE "+out+" ");
	97
	98	if(sqlSelectQuery.getGroupBy() != null){
	99	out.delete(0, out.length()) ;
	100	sqlSelectQuery.getGroupBy().accept(sqlastOutputVisitor) ;
	101	sqlWhere.append(" "+out);
	102	}
	103	if(sqlSelectQuery.getOrderBy() != null){
	104	out.delete(0, out.length()) ;
	105	sqlSelectQuery.getOrderBy().accept(sqlastOutputVisitor) ;
	106	sqlWhere.append(" "+out);
	107	}
	108	if(sqlSelectQuery.getLimit() != null){
	109	out.delete(0, out.length()) ;
	110	sqlSelectQuery.getLimit().accept(sqlastOutputVisitor) ;
	111	sqlWhere.append(" "+out);
	112	}
	113
	114	//处理where需要加密得字段
	115	sql = sqlWhere.toString();
	116	if(!StringUtils.isEmpty(sql)){
	117	Map<String,String> aesKeys = null;
	118	String aeskey = null;
	119	//把剩下的拼接上来
	120	String tableAl = null;
	121	for(TableStat.Column column:columns){
	122	aesKeys= aesKeysTable.get(column.getTable());
	123	if(aesKeys == null){
	124	continue;
	125	}
	126	aeskey = aesKeys.getOrDefault(column.getName(),null);
	127	if(StringUtils.isEmpty(aeskey)){
	128	continue;
	129	}
	130	tableAl = tableMaps.get(column.getTable());
	131	if(!StringUtils.isEmpty(tableAl)){
	132	tableAl = tableAl+"."+column.getName();
	133	}else{
	134	tableAl = column.getName();
	135	}
	136	sql = sql.replaceAll("( \|\\n\|\\()"+tableAl+"( \|\\n\|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
	137	}
	138	}
	139	return sqlSelect.toString()+sql;
	140	}
	141
	142	public static String selectSqlHandle(String sql,Map<String,Map<String,String>> aesKeysTable
	143	,Map<String,String> tableMaps,Collection<TableStat.Column> columns){
	144
	145
	146	MySqlStatementParser parser = new MySqlStatementParser(sql);
	147	SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect();
	148	//获取格式化的slq语句
	149	sql = sqlStatement.toString();
	150
	151	//解析select查询
	152	//SQLSelect sqlSelect = sqlStatement.getSelect() ;
	153	//获取sql查询块
	154	SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
	155	StringBuffer out = new StringBuffer() ;
	156	//创建sql解析的标准化输出
	157	SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ;
	158
	159	StringBuilder sqlWhere = new StringBuilder();
	160
	161	StringBuilder sqlSelect = new StringBuilder();
	162	String expr = null;
	163	sqlSelect.append("SELECT ");
	164	//解析select返回的数据字段项
	165	for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
	166	if(sqlSelect.length() > 7){
	167	sqlSelect.append(",");
	168	}
	169	expr = sqlSelectItem.getExpr().toString();
	170	if(expr.indexOf("SELECT") == -1){
	171	sqlSelect.append(expr);
	172	if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
	173	sqlSelect.append(" AS "+sqlSelectItem.getAlias());
	174	}
	175	}else{
	176	sqlSelect.append("(");
	177	selectSqlHandle(expr,aesKeysTable,tableMaps,columns);
	178	sqlSelect.append(")");
	179	if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
	180	sqlSelect.append(" AS "+sqlSelectItem.getAlias());
	181	}
	182	}
	183	}
	184
	185	//解析from
	186	out.delete(0, out.length()) ;
	187	sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
	188	sqlWhere.append(" FROM "+out);
	189
	190	//解析where
	191	out.delete(0, out.length()) ;
	192	sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
	193	sqlWhere.append(" WHERE "+out+" ");
	194
	195	if(sqlSelectQuery.getGroupBy() != null){
	196	out.delete(0, out.length()) ;
	197	sqlSelectQuery.getGroupBy().accept(sqlastOutputVisitor) ;
	198	sqlWhere.append(" "+out);
	199	}
	200	if(sqlSelectQuery.getOrderBy() != null){
	201	out.delete(0, out.length()) ;
	202	sqlSelectQuery.getOrderBy().accept(sqlastOutputVisitor) ;
	203	sqlWhere.append(" "+out);
	204	}
	205	if(sqlSelectQuery.getLimit() != null){
	206	out.delete(0, out.length()) ;
	207	sqlSelectQuery.getLimit().accept(sqlastOutputVisitor) ;
	208	sqlWhere.append(" "+out);
	209	}
	210
	211	sql = sqlWhere.toString();
	212	if(!StringUtils.isEmpty(sql)){
	213	Map<String,String> aesKeys = null;
	214	String aeskey = null;
	215	//把剩下的拼接上来
	216	String tableAl = null;
	217
	218	for(TableStat.Column column:columns){
	219	aesKeys= aesKeysTable.get(column.getTable());
	220	if(aesKeys == null){
	221	continue;
	222	}
	223	aeskey = aesKeys.getOrDefault(column.getName(),null);
	224	if(StringUtils.isEmpty(aeskey)){
	225	continue;
	226	}
	227	tableAl = tableMaps.get(column.getTable());
	228	if(!StringUtils.isEmpty(tableAl)){
	229	tableAl = tableAl+"."+column.getName();
	230	}else{
	231	tableAl = column.getName();
	232	}
	233	sql = sql.replaceAll("( \|\\n\|\\()"+tableAl+"( \|\\n\|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
	234	}
	235	}
	236	return sqlSelect.toString()+sql;
	237	}
	238
	239	/**新增加密数据处理
	240	* @param sql sql语句
	241	* @param aesKeysTable aes秘钥
	242	* @return
	243	*/
	244	public static String insertSql(String sql,Map<String,Map<String,String>> aesKeysTable){
	245	//装载重写的sql语句
	246	StringBuilder splicingSql = new StringBuilder();
	247
	248	sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
	249	String[] datas = sql.split("VALUES",2);
	250
	251	splicingSql.append(datas[0]+"VALUES ");
	252
	253	//重新拼接SQL语句
	254
	255	//解析sql语句
	256	MySqlStatementParser parser = new MySqlStatementParser(sql);
	257	SQLStatement statement = parser.parseStatement();
	258	MySqlInsertStatement insert = (MySqlInsertStatement)statement;
	259
	260	String insertName = insert.getTableName().getSimpleName();
	261
	262	//根据表名称获取到AES秘钥
	263	Map<String,String> aesKeys= aesKeysTable.get(insertName);
	264	if(aesKeys == null){
	265	return sql;
	266	}
	267
	268	//获取所有的字段
	269	List<SQLExpr> columns = insert.getColumns();
	270
	271	String fildValue = null;
	272	String aeskey = null;
	273	//遍历值
	274	List<SQLInsertStatement.ValuesClause> vcl = insert.getValuesList();
	275	for(int j = 0; j<vcl.size(); j++){
	276	if( j != 0){
	277	splicingSql.append(",");
	278	}
	279	for(int i = 0;i < columns.size();i++){
	280	//查询改字段是否需要加密
	281	aeskey = aesKeys.getOrDefault(columns.get(i).toString(),null);
	282	fildValue = vcl.get(j).getValues().get(i).toString();
	283	if(i == 0){
	284	splicingSql.append("(");
	285	if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
	286	splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
	287	}else{
	288	splicingSql.append(fildValue);
	289	}
	290	}else if(i == columns.size()-1){
	291	splicingSql.append(",");
	292	if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
	293	splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
	294	}else{
	295	splicingSql.append(fildValue);
	296	}
	297	splicingSql.append(")");
	298	}else{
	299	splicingSql.append(",");
	300	if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
	301	splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
	302	}else{
	303	splicingSql.append(fildValue);
	304	}
	305	}
	306	}
	307	}
	308	return splicingSql.toString();
	309	}
	310
	311	/**更新加密数据处理
	312	* @param sql sql语句
	313	* @param aesKeysTable aes秘钥
	314	* @return
	315	*/
	316	public static String updateSql(String sql,Map<String,Map<String,String>> aesKeysTable){
	317	//装载重写的sql语句
	318	StringBuilder splicingSql = new StringBuilder();
	319
	320	//sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
	321	MySqlStatementParser parser = new MySqlStatementParser(sql);
	322	SQLStatement sqlStatement = parser.parseStatement();
	323	//获取格式化的slq语句
	324	sql = sqlStatement.toString();
	325
	326
	327	MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement;
	328
	329	String insertName = updateStatement.getTableName().getSimpleName();
	330
	331	String[] datas = sql.split("WHERE",2);
	332
	333	Map<String,String> aesKeys = aesKeysTable.get(insertName);
	334
	335	splicingSql.append("UPDATE "+insertName+" SET ");
	336
	337	String aeskey = null;
	338	String fildValue = null;
	339	List<SQLUpdateSetItem> items = updateStatement.getItems();
	340	for(int i = 0;i<items.size();i++){
	341	if(i != 0){
	342	splicingSql.append(",");
	343	}
	344
	345	SQLUpdateSetItem item = items.get(i);
	346
	347	//查询改字段是否需要加密
	348	aeskey = aesKeys.getOrDefault(item.getColumn().toString(),null);
	349
	350	fildValue = item.getValue().toString();
	351	if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
	352	splicingSql.append(item.getColumn()+" = HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
	353	}else{
	354	splicingSql.append(item.getColumn()+" = "+fildValue);
	355	}
	356	}
	357
	358	String sqlWhere = " WHERE";
	359	//把剩下的拼接上来
	360	if(datas.length > 1){
	361	for(int i =1;i<datas.length;i++){
	362	sqlWhere = sqlWhere+datas[i];
	363	}
	364
	365	parser = new MySqlStatementParser("SELECT * FROM "+insertName+" "+sqlWhere);
	366	sqlStatement = parser.parseStatement();
	367
	368	ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
	369	sqlStatement.accept(visitorTable);
	370
	371	//获取表和别名
	372	Map<String,String> tableMaps = visitorTable.getTableMap();
	373	tableMaps.put(insertName,null);
	374
	375	//获取所有的字段
	376	MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
	377	sqlStatement.accept(visitor);
	378
	379	String tableAl = null;
	380	//遍历所有字段
	381	Collection<TableStat.Column> columns= visitor.getColumns();
	382	for(TableStat.Column column:columns){
	383
	384	aesKeys= aesKeysTable.get(column.getTable());
	385	if(aesKeys == null){
	386	continue;
	387	}
	388	aeskey = aesKeys.getOrDefault(column.getName(),null);
	389	if(StringUtils.isEmpty(aeskey)){
	390	continue;
	391	}
	392	tableAl = tableMaps.get(column.getTable());
	393	if(!StringUtils.isEmpty(tableAl)){
	394	tableAl = tableAl+"."+column.getName();
	395	}else{
	396	tableAl = column.getName();
	397	}
	398	sqlWhere = sqlWhere.replaceAll("( \|\\n\|\\()"+tableAl+"( \|\\n\|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
	399	}
	400
	401	}
	402	splicingSql.append(sqlWhere.toString());
	403	return splicingSql.toString();
	404	}
	405
	406	/**删除加密数据处理
	407	* @param sql sql语句
	408	* @param aesKeysTable aes秘钥
	409	* @return
	410	*/
	411	public static String deleteSql(String sql,Map<String,Map<String,String>> aesKeysTable){
	412	//装载重写的sql语句
	413	StringBuilder splicingSql = new StringBuilder();
	414
	415	//sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
	416	MySqlStatementParser parser = new MySqlStatementParser(sql);
	417	SQLStatement sqlStatement = parser.parseStatement();
	418	//获取格式化的slq语句
	419	sql = sqlStatement.toString();
	420
	421	MySqlDeleteStatement deleteStatement = (MySqlDeleteStatement)sqlStatement;
	422
	423	String insertName = deleteStatement.getTableName().getSimpleName();
	424
	425	String[] datas = sql.split("WHERE",2);
	426
	427	Map<String,String> aesKeys = aesKeysTable.get(insertName);
	428
	429	splicingSql.append("DELETE FROM "+insertName);
	430
	431	String aeskey = null;
	432
	433	String sqlWhere = " WHERE";
	434	//把剩下的拼接上来
	435	if(datas.length > 1){
	436	for(int i =1;i<datas.length;i++){
	437	sqlWhere = sqlWhere+datas[i];
	438	}
	439
	440	parser = new MySqlStatementParser("SELECT * FROM "+insertName+" "+sqlWhere);
	441	sqlStatement = parser.parseStatement();
	442
	443	ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
	444	sqlStatement.accept(visitorTable);
	445
	446	//获取表和别名
	447	Map<String,String> tableMaps = visitorTable.getTableMap();
	448	tableMaps.put(insertName,null);
	449
	450	//获取所有的字段
	451	MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
	452	sqlStatement.accept(visitor);
	453
	454	String tableAl = null;
	455	//遍历所有字段
	456	Collection<TableStat.Column> columns= visitor.getColumns();
	457	for(TableStat.Column column:columns){
	458
	459	aesKeys= aesKeysTable.get(column.getTable());
	460	if(aesKeys == null){
	461	continue;
	462	}
	463	aeskey = aesKeys.getOrDefault(column.getName(),null);
	464	if(StringUtils.isEmpty(aeskey)){
	465	continue;
	466	}
	467	tableAl = tableMaps.get(column.getTable());
	468	if(!StringUtils.isEmpty(tableAl)){
	469	tableAl = tableAl+"."+column.getName();
	470	}else{
	471	tableAl = column.getName();
	472	}
	473	sqlWhere = sqlWhere.replaceAll("( \|\\n\|\\()"+tableAl+"( \|\\n\|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
	474	}
	475
	476	}
	477	splicingSql.append(sqlWhere.toString());
	478	return splicingSql.toString();
	479	}
	480
	481	}