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