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