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]);
|
}
|
}
|
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);
|
if (valueList.size() == columnNames.length) {
|
for (int i = 0; i < columnNames.length; 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");
|
}
|
}
|
