package com.basic.security.utils;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.HashSet;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
public class SqlSplit {
|
static char[] startEndQuotes = new char[]{'\'', '"', '`'};
|
|
static Set<String> tableNameList = new HashSet<>();
|
|
static {
|
tableNameList.addAll(Arrays.asList(new String[]{
|
"base_setting",
|
"business",
|
"business_apply_device",
|
"business_person",
|
"cluster",
|
"cluster_node",
|
"cluster_setting",
|
"confirm",
|
"device",
|
"guest",
|
"hint_door_access",
|
"hint_recognize_message",
|
"hint_sign_up",
|
"id_card",
|
"identity",
|
"Log",
|
"OfficeDevice",
|
"org",
|
"outdoor",
|
"person",
|
"person_identity",
|
"temporary_person",
|
"time",
|
"user",
|
"dbtablepersons",
|
"dbTables",
|
"visit"
|
}));
|
}
|
|
public static boolean containsTable(String sql) {
|
String tableName = getTableName(sql).trim();
|
if (tableNameList.contains(tableName)) {
|
return true;
|
}
|
return false;
|
}
|
|
public static String getTableName(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("insert")) {
|
return getTableNameFromInsertSql(sql);
|
}
|
if (sql.startsWith("delete")) {
|
return getTableNameFromDeleteSql(sql);
|
}
|
if (sql.startsWith("update")) {
|
return getTableNameFromUpdateSql(sql);
|
}
|
return sql;
|
}
|
|
public static boolean isInsert(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("insert")) {
|
return true;
|
}
|
return false;
|
}
|
|
public static boolean isDelete(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("delete")) {
|
return true;
|
}
|
return false;
|
}
|
|
public static Map<String, String> getRowDataMap(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("insert")) {
|
return insertToMap(sql);
|
}
|
if (sql.startsWith("delete")) {
|
return deleteToMap(sql);
|
}
|
if (sql.startsWith("update")) {
|
return updateToMap(sql);
|
}
|
return new HashMap<>();
|
}
|
|
public static String getRowSqlOperateType(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("insert")) {
|
return "insert";
|
}
|
if (sql.startsWith("delete")) {
|
return "delete";
|
}
|
if (sql.startsWith("update")) {
|
return "update";
|
}
|
return "";
|
}
|
|
public static boolean isUpdate(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.trim();
|
if (sql.startsWith("update")) {
|
return true;
|
}
|
return false;
|
}
|
|
public static String getTableNameFromDeleteSql(String sql) {
|
String tableName = "";
|
sql = sql.replace("\n", " ");
|
sql = sql.replace("\r\n", " ");
|
sql = sql.replace(" ", " ")
|
.replace(" ", " ").replace(" ", " ").replace(" ", " ").replace(" ", " ")
|
;
|
sql = sql.substring(0, sql.toLowerCase().indexOf(" where ")).trim().split(" ")[2];
|
tableName = sql.substring(sql.indexOf(" ")).trim();
|
return tableName;
|
}
|
|
public static String getTableNameFromUpdateSql(String sql) {
|
String tableName = "";
|
sql = sql.replace("\n", " ");
|
sql = sql.replace("\r\n", " ");
|
sql = sql.replace(" ", " ")
|
.replace(" ", " ").replace(" ", " ").replace(" ", " ").replace(" ", " ")
|
;
|
sql = sql.substring(0, sql.toLowerCase().indexOf(" set ")).trim().split(" ")[1];
|
int index = sql.indexOf(" ");
|
if (index != -1) {
|
tableName = sql.substring(index);
|
}
|
tableName = tableName.trim();
|
return tableName;
|
}
|
|
public static String getTableNameFromInsertSql(String sql) {
|
sql = sql.toLowerCase();
|
sql = sql.replace("insert or replace into", "insert into");
|
String tableName = "";
|
sql = sql.replace("\n", " ");
|
sql = sql.replace("\r\n", " ");
|
sql = sql.replace(" ", " ");
|
sql = sql.substring(sql.toLowerCase().indexOf("into ") + "into ".length()).trim();
|
tableName = sql.substring(0, sql.indexOf("(")).trim();
|
return tableName;
|
}
|
|
public static Map<String, String> deleteToMap(String sql) {
|
Map<String, String> columnMap = new HashMap<>();
|
sql = sql.replace("\r\n", " ").replace("\n", " ");
|
sql = sql.replace(" = ", " ")
|
.replace("= ", " ")
|
.replace("= ", " ")
|
.replace("= ", " ")
|
.replace(" =", " ")
|
.replace(" =", " ")
|
.replace(" =", " ")
|
;
|
String[] sqlParts = sql.split(" ");
|
for (String sqlPart : sqlParts) {
|
if (sqlPart.contains("=")) {
|
sqlPart = sqlPart.trim();
|
columnMap.put(sqlPart.split("=")[0], sqlPart.split("=")[1]);
|
}
|
}
|
System1.out.println(columnMap);
|
return columnMap;
|
}
|
|
public static Map<String, String> updateToMap(String sql) {
|
Map<String, String> columnMap = new HashMap<>();
|
sql = sql.replace("\r\n", " ").replace("\n", " ");
|
sql = sql.replace(" = ", " ")
|
.replace("= ", " ")
|
.replace("= ", " ")
|
.replace("= ", " ")
|
.replace(" =", " ")
|
.replace(" =", " ")
|
.replace(" =", " ")
|
;
|
String[] sqlParts = sql.split(" ");
|
for (String sqlPart : sqlParts) {
|
if (sqlPart.contains("=")) {
|
sqlPart = sqlPart.trim();
|
columnMap.put(sqlPart.split("=")[0], sqlPart.split("=")[1]);
|
}
|
}
|
// System1.out.println(columnMap);
|
return columnMap;
|
}
|
|
public static Map<String, String> insertToMap(String sql) {
|
Map<String, String> columnMap = new HashMap<>();
|
String columnNameSql = sql;
|
columnNameSql = sql.substring(sql.indexOf("("));
|
columnNameSql = columnNameSql.substring(0, columnNameSql.indexOf(")"));
|
columnNameSql = columnNameSql.replace("(", "");
|
columnNameSql = columnNameSql.replace(")", "");
|
String[] columnNames = columnNameSql.split(",");
|
for (int i = 0; i < columnNames.length; i++) {
|
columnNames[i] = columnNames[i].trim();
|
}
|
String valueSql = sql.substring(sql.toLowerCase().indexOf("values")).trim();
|
if (valueSql.toLowerCase().startsWith("values")) {
|
valueSql = valueSql.substring("values".length());
|
valueSql = valueSql.trim();
|
}
|
if (valueSql.charAt(0) == '(') {
|
valueSql = valueSql.substring(1);
|
}
|
if (valueSql.charAt(valueSql.length() - 1) == ';') {
|
valueSql = valueSql.substring(0, valueSql.length() - 1);
|
}
|
if (valueSql.charAt(valueSql.length() - 1) == ')') {
|
valueSql = valueSql.substring(0, valueSql.length() - 1);
|
}
|
List<String> valueList = parseValues(valueSql, columnNames.length);
|
// System1.out.println(columnNames.length + ", " + valueList.size());
|
if (valueList.size() == columnNames.length) {
|
for (int i = 0; i < columnNames.length; i++) {
|
// System1.out.println(columnNames[i] + "=" + valueList.get(i));
|
Character startEndQuote = startEndQuote(columnNames[i].charAt(0));
|
if (startEndQuote != null) {
|
columnNames[i] = columnNames[i].substring(1);
|
columnNames[i] = columnNames[i].substring(0, columnNames[i].length() - 1);
|
}
|
columnMap.put(columnNames[i], valueList.get(i));
|
}
|
}
|
return columnMap;
|
}
|
|
public static Character startEndQuote(char startChar) {
|
for (char startEndQuote : startEndQuotes) {
|
if (startEndQuote == startChar) {
|
return startChar;
|
}
|
}
|
return null;
|
}
|
|
private static List<String> parseValues(String valueSql, int columnNamesLength) {
|
List<String> valueList = new ArrayList<>();
|
while (true) {
|
if (valueSql.trim().length() == 0) {
|
break;
|
}
|
char quoteBegin = valueSql.charAt(0);
|
Character startEndQuote = startEndQuote(quoteBegin);
|
if (startEndQuote == null) {
|
if (valueList.size() == columnNamesLength - 1) {
|
String value = valueSql.trim();
|
valueList.add(value);
|
} else {
|
int commaIndex = valueSql.indexOf(",");
|
String value = valueSql.substring(0, commaIndex);
|
valueSql = valueSql.substring(commaIndex + 1).trim();
|
valueList.add(value);
|
}
|
} else {
|
valueSql = valueSql.substring(1);
|
valueSql = valueSql.replace(startEndQuote + " " + ",", startEndQuote + ",");
|
valueSql = valueSql.replace(startEndQuote + " " + ",", startEndQuote + ",");
|
valueSql = valueSql.replace(startEndQuote + " " + ",", startEndQuote + ",");
|
valueSql = valueSql.trim();
|
int endIndex = valueSql.indexOf(startEndQuote + ",");
|
if (valueList.size() == columnNamesLength - 1) {
|
endIndex = valueSql.indexOf(startEndQuote);
|
}
|
if (endIndex == -1) {
|
break;
|
}
|
String value = valueSql.substring(0, endIndex);
|
valueSql = valueSql.substring(endIndex + 1).trim();
|
if (valueSql.length() > 0 && valueSql.charAt(0) == ',') {
|
valueSql = valueSql.substring(1).trim();
|
}
|
valueList.add(value);
|
}
|
}
|
return valueList;
|
}
|
|
public static void main(String[] args) {
|
tableNameList.contains("person");
|
// String sql = "INSERT INTO person(\"camera_image_feature_path\", \"checked_time_rule_id\", \"sign_up_rule\", \"id\", \"name\", \"camera_image_path\", \"camera_image_feature\", \"camera_image_fastdfs_path\", \"id_card_number\", \"id_card_image_path\", \"id_card_image_feature\", \"type\", \"phone\", \"sign_up_time\", \"sign_up_method\", \"sign_up_status\", \"verify_status\", \"auto_init\", \"update_time\", \"del_flag\", \"all_device\", \"company_id\", \"table\", \"device_id\") VALUES ('', '', '注册通行', 'PSPAD010120190716172753922', '', '/sdcard/security_attachment/person_camera_image_path_PSPAD010120190716172753922', 'kQKvjWNp89euRzjBJGVNRUSXWI7F7yvh1VTBngS8JPI8G3oEw1i5AVG2ZeWBAOkABZbA4tGXyVf2h5vPoe0auBcZhXEbmenD2duANhmHLyCqpXxkq7Iak57lVzV5N5mx6BtUUrhtMhAth4S8FYe87qstbLoQrz+7DEt+d1SFJKDjqag6C2myZhgIVLNnDHAO22Gnn5Msfmt5r8Awl6S6nORL+vsad3yk9gzt/KCtbJwSH74B9sS95z+HvbchaT5wRL+9z+DePHOoL74Vhm89FCQHPciH9bs65Qw79VsQPo/LtDzb8Jq+BO8dvk8vgrtjVF++2D6SvjSwS71ERl+8cH8YPP/c5D1r3oQ9hcIiPOISmr2rj7q9ExEYvlgooD3Q/dK93pYdPm5jgr2liO69M6CuvcoN8L1fVEq9MLzovancBr4y6C6+EdUTPu9eUby+Lbu9rBH+vefvtj3R4WK9LacZPlJO2ryb5iI+qBBcvjW8MrzfZLM7dy/PPRlGDz5Zjl09DkEDPq5ywb0jKrC9A5H9vAF1eD2WFke9IAADvCaUcbytZ1s8TmH3vXHZx7wvUMO9IiuRPX0cnD1lEh69TgMovGIEPj1RjCU96Ac6PeWOwTy3PwI+S4WoPXxNa70eFLw9oxifvG1zsb2QIOk9aYaoPUtY2rwF93+9eXMqvcbqPr0YZsQ8DRsQvbIQhrydACQ9Kq4RPcBtqrvx3ho9lzMQvZof6DwrP5o8ttYwPW+elL1cr/Q8YO1LPETaGLsCgS47LoWEPXWfRD125z08qNzTPKdoAj2aS/Y72crBvOSwgDtdrq27whnLvJ24cLza5Jm8c449O76rcrxE0A885CxEPMoamry5n1s8pKGcvHXNObxCEr08cORhPKk/Uzx29QO9eHTDO59UIr2Baaw74XW9u2CHfTzv8gE7WnAPvfHHwLuJKgE9Ws41vOBqAzyISMc8xd6QPCbdiTwOSAc8TpHUvL4MrTvpbHe8/yKDvNzXKbzXFJc8h58PvEkR17tRCw48KcbMvA8FNDw1TyA8t0c5PJEuijz2odg8XdsxPHgqIT0mySu8o91MOOWU7LqlHT48XM1PulOuF716FBm8QpN+vA4LFrxB9UK7P42Tu2zjDT1WOw08L9Zuu0Q9ibwGZbS7h/9VPG/UF7s4klw6ak4zvHpX/DvOHoO8ikXcPEz2iDzkfoI8fXhLvBzfgbzMQcU8xbddvHUQ1jowN/s70OvBPEhXMLr4YdM8Zzk0vLnjQDwkd5S8qxCMOzEHNjziTTI8gmekvCfDFLyDAlG7dtDDvJ4PCDzRtfQ75zKZPJG7ATtJSwi6Khbwu8ex4bzp1FC8uMstPAb5M7w4vAs8XNaZPBbQ1LqZNrQ7VZ4NvE+QYzku9NC7tMnXO5GOc7oX/O28nBc4vAwtMLoN47i8br+ivCi32DtirdQ7w0InurrNULxAO/A7ZDIJOGz4mDs3PoY8LMUCPTU+hLzlNks83yO1vOGX6bv5S3W7Nb8nPK3N/bnpdWK6uPQkvGi8sbxmpoc7UbReulmMUDsOEjm6nYJ3uu/tbrvLlVG7uooGvHUxvDoQZNO6Mf4lPLEEXTsk95q7D2LEvHof7Lq97/c7F2YrvA0rGbwN5ZU7QHgUu6gxZ7nXePS8OBE+uztWFDtvXVQ51tAkPFM0aro7HJO8R4QlO1dBIjsTe0m79d3Yu3hzEDvwvxw7XmqMvNi21jsb1Js7utLbO/h/AjzcNyM78EgkPMMxSDtEA/Q6daekO/QF7zvfYwQ6QphqvLi+BTwf+6O7RbiOO+OYZbzDtNi6omNFPDznAzrqiwc8YzCJu0lArjuHAce8EHULOnbA3jughSg8ZxGCu9GGTzyDi4M8jSi3O6o4C7xexlG8qUAqPJLv5jmku2I8/CuZu7y0fTwxLjI8kgXuO6tZGrwnJaI7By2tO0SfBbvf3D88K+o7u8/3iLpii6M75jzKO6XnCbxZYWk7yqCEPL+QrDuimiS8AG0SvHrQ/Ds7nzs8HrMAPNVQajuL4hg8zVxHvMxAvDtQmq67IvoPvPPEm7shjZS7Pe6OuxTfeTsEvNO7DKefu8uPz7uuzfu6DS1SO+alV7sbpbo7QqTfu4AoaztzddK6PjeUuoCyB7sH+Cq7cqUguuRB27t70iA7LfeZOyMslzvv2ms6mIaLO7FbWzzYcew6l69TO2zRgbs7fLy6uRxkOleadDrDJWu7h6Q0urBq5blfUCU77vfwOcGLZbtnFJO7EeLyulNOsLs3kdU7rc82OU6h6LoCR0m7KhinO/+qDTvpHaY7QSlGOvzwvTmDvBU5jKkyuwe/prvc44u3cLJtu6bjWDpzi8a6xBxyujiJ0boP7AE85TXnObdjiDvh1Xq7FFOQu8PSBbtcLFY7yVSHO3uFbLvjOs47VKhzOVRfWzupKA668wuruqxrorq2lXE7zCOrOy+DY7uMJyS73L1ruUYJBDuJSSG75JYUOghPMbpB7hE7GuMLutfMNjrEzxg752MmuVrDAjugqDg79OuAOwOj7Dpv8Re7b54yOpxnzDrozbU6iDsuOT2i7zipG5O7QYueusmyvLq+y145UPwjO1S5pzrHLmu7GZEMuzqbBrumcXI7OHMHu/3pUDvVzTe7sFR/uz1ICruGsle6t2LCOrusrjmPqQw7ApH6OiPf/boIFfc5Y2nwuX3LubpWoFi6AfJbum6iMrkUesE6fClAuM8lBbplNbI63ZebutlDkbqG8Ku64fJrusVHWTorVYK64s0AO4ZggrsTndG54zn+t5wCkToZfZc5DN32uH0fjjpGwyE7+UQtu89oHTpJCwU6KLsCOoR1HLnGmpu3yaaRuvb5RrqpDKu6SbI6Oln3FTp3g9k6XNoMOnpB8rpgPzi6PcgRO92zkzlGGp24jpfrOmWPGTpK4CY6j2ybOmfJ1TpR3NW6m6ZbujKmEjryUIm6ZuJzjmsGWsQdTaAbDUWLbYkmkAXNq5AUzYj62nodpXLtCuGrKLzEwOknu62anHpq8Ulv3nwJp7SVW8jgmJ6T9U73rrqNPKYb+tPo4uE6perTg+kuI2MrXz6llMfQ3r72ZjXZYxSr4HndEHU0BzIHMq3HCAD7QqP0HVZo6Zwj4IQy0+AX/kUUSVb55WKa6HQh4BOQ0CBI9ls7FzAYV9wCmS69Ehno2tVPfLGnebBrhEBWlxDgdPmY+U0iLqo7kHbaaFZxvCeD/KGzA2+K3orK3h6Dv+6S8CTZvk1vln7LabW+A7s1XvylnOZ71baZSMnHdN/FzC4D2wdO3q0CE6hpStoIA5nbLlVHoI6t+nYOQQrAFEtkg8+ao7mTNeiEW4WzTUtgiDVnyrRtLZI/xjk3VwV/651GQqIu+mlAO3pRbR5rbUPporz60xZZLiscZ092Nw==', '', '', '', '', '', '', '1563269273923', '人工注册', '已注册', '不需要验证身份证', '', '', '0', '0', '', 'person', '');\n";
|
// System1.out.println("SqlSplit.main " + getTableNameFromInsertSql(sql));
|
// insertToMap(query);
|
// System1.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(Long.parseLong("1573901869122"))));
|
// if (1 == 1) {
|
// return;
|
// }
|
// Pattern pattern = Pattern.compile("\\((.*?)\\)", Pattern.DOTALL);
|
// Matcher matcher = pattern.matcher(query);
|
// List<String[]> tokens = new ArrayList<String[]>();
|
// while (matcher.find()) {
|
// tokens.add(matcher.group().replace("(", "").replace(")", "").split(","));
|
// }
|
// Map<String, String> map = new HashMap<String, String>();
|
// if (tokens.size() == 2 && tokens.get(0).length == tokens.get(1).length) {
|
// for (int x = 0; x < tokens.get(0).length; x++) {
|
// map.put(tokens.get(0)[x], tokens.get(1)[x].replace("'", ""));
|
// }
|
// }
|
// for (Map.Entry<String, String> entry : map.entrySet()) {
|
// System1.out.println("KEY:" + entry.getKey());
|
// System1.out.println("VALUE:" + entry.getValue());
|
// }
|
}
|
|
}
|
