增强安全：对 DELETE 和 PUT 强制加 LIMIT；简化包含选项的写法：解决 "key<>": "a" 这种包含字符串的格式报错 Data truncation: Invalid JSON text，原来必须里面再用 "" 包装一次，JSON 中还得转义，现在直接写即可；

TommyLemon · TommyLemon · commit 6831cb6a6d9b · 2021-01-31T04:20:26.000+08:00
diff --git a/APIJSONORM/src/main/java/apijson/orm/AbstractObjectParser.java b/APIJSONORM/src/main/java/apijson/orm/AbstractObjectParser.java
@@ -650,7 +650,7 @@ public SQLConfig newSQLConfig(boolean isProcedure) throws Exception {
 	 */
 	@Override
 	public AbstractObjectParser setSQLConfig() throws Exception {
-		return setSQLConfig(1, 0, 0);
+		return setSQLConfig(RequestMethod.isQueryMethod(method) ? 1 : 0, 0, 0);
 	}
 
 	@Override
@@ -668,7 +668,7 @@ public AbstractObjectParser setSQLConfig(int count, int page, int position) thro
 				return this;
 			}
 		}
-		sqlConfig.setCount(count).setPage(page).setPosition(position);
+		sqlConfig.setCount(sqlConfig.getCount() <= 0 ? count : sqlConfig.getCount()).setPage(page).setPosition(position);
 
 		parser.onVerifyRole(sqlConfig);
 
diff --git a/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java b/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java
@@ -1284,11 +1284,11 @@ public String getLimitString() {
 	public static String getLimitString(int page, int count, boolean isTSQL) {
 		int offset = getOffset(page, count);
 
-		if (isTSQL) {
+		if (isTSQL) {  // OFFSET FECTH 中所有关键词都不可省略
 			return " OFFSET " + offset + " ROWS FETCH FIRST " + count + " ROWS ONLY";
 		}
 
-		return " LIMIT " + count + " OFFSET " + offset;
+		return " LIMIT " + count + (offset <= 0 ? "" : " OFFSET " + offset);  // DELETE, UPDATE 不支持 OFFSET
 	}
 
 	//WHERE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -2179,20 +2179,23 @@ public String getContainString(String key, Object[] childs, int type) throws Ill
 		String condition = "";
 		if (childs != null) {
 			for (int i = 0; i < childs.length; i++) {
-				if (childs[i] != null) {
-					if (childs[i] instanceof JSON) {
+				Object c = childs[i];
+				if (c != null) {
+					if (c instanceof JSON) {
 						throw new IllegalArgumentException(key + "<>:value 中value类型不能为JSON！");
 					}
 
 					condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR));
 					if (isPostgreSQL()) {
-						condition += (getKey(key) + " @> " + getValue(newJSONArray(childs[i]))); //operator does not exist: jsonb @> character varying  "[" + childs[i] + "]"); 
+						condition += (getKey(key) + " @> " + getValue(newJSONArray(c))); //operator does not exist: jsonb @> character varying  "[" + c + "]"); 
 					}
 					else if (isOracle()) {
-						condition += ("json_textcontains(" + getKey(key) + ", '$', " + getValue(childs[i].toString()) + ")");
+						condition += ("json_textcontains(" + getKey(key) + ", '$', " + getValue(c.toString()) + ")");
 					}
 					else {
-						condition += ("json_contains(" + getKey(key) + ", " + getValue(childs[i].toString()) + ")");
+						boolean isNum = c instanceof Number;
+						String v = (isNum ? "" : "\"") + childs[i] + (isNum ? "" : "\"");
+						condition += ("json_contains(" + getKey(key) + ", " +  getValue(v) + ")");
 					}
 				}
 			}
@@ -2390,9 +2393,9 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
 		case POST:
 			return "INSERT INTO " + tablePath + config.getColumnString() + " VALUES" + config.getValuesString();
 		case PUT:
-			return "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true);
+			return "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true) + config.getLimitString();
 		case DELETE:
-			return "DELETE FROM " + tablePath + config.getWhereString(true);
+			return "DELETE FROM " + tablePath + config.getWhereString(true) + config.getLimitString();
 		default:
 			String explain = (config.isExplain() ? (config.isSQLServer() || config.isOracle() ? "SET STATISTICS PROFILE ON  " : "EXPLAIN ") : "");
 			if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) {
@@ -2635,6 +2638,10 @@ public static SQLConfig newSQLConfig(RequestMethod method, String table, String
 				throw new NotExistException(TAG + ": newSQLConfig idIn instanceof List >> 去掉无效 id 后 newIdIn.isEmpty()");
 			}
 			idIn = newIdIn;
+			
+			if (method == DELETE || method == PUT) {
+				config.setCount(newIdIn.size());
+			}
 		}
 		
 		//对id和id{}处理，这两个一定会作为条件
@@ -2670,6 +2677,10 @@ else if (id instanceof Subquery) {}
 					throw new NotExistException(TAG + ": newSQLConfig  idIn != null && (((List<?>) idIn).contains(id) == false");
 				}
 			}
+			
+			if (method == DELETE || method == PUT) {
+				config.setCount(1);
+			}
 		}
 
 

Original file line number	Diff line number	Diff line change
`@@ -650,7 +650,7 @@ public SQLConfig newSQLConfig(boolean isProcedure) throws Exception {`
`650`	`650`	`*/`
`651`	`651`	`@Override`
`652`	`652`	`public AbstractObjectParser setSQLConfig() throws Exception {`
`653`		`- return setSQLConfig(1, 0, 0);`
	`653`	`+ return setSQLConfig(RequestMethod.isQueryMethod(method) ? 1 : 0, 0, 0);`
`654`	`654`	`}`
`655`	`655`
`656`	`656`	`@Override`
`@@ -668,7 +668,7 @@ public AbstractObjectParser setSQLConfig(int count, int page, int position) thro`
`668`	`668`	`return this;`
`669`	`669`	`}`
`670`	`670`	`}`
`671`		`- sqlConfig.setCount(count).setPage(page).setPosition(position);`
	`671`	`+ sqlConfig.setCount(sqlConfig.getCount() <= 0 ? count : sqlConfig.getCount()).setPage(page).setPosition(position);`
`672`	`672`
`673`	`673`	`parser.onVerifyRole(sqlConfig);`
`674`	`674`
Original file line number	Diff line number	Diff line change
`@@ -1284,11 +1284,11 @@ public String getLimitString() {`
`1284`	`1284`	`public static String getLimitString(int page, int count, boolean isTSQL) {`
`1285`	`1285`	`int offset = getOffset(page, count);`
`1286`	`1286`
`1287`		`- if (isTSQL) {`
	`1287`	`+ if (isTSQL) { // OFFSET FECTH 中所有关键词都不可省略`
`1288`	`1288`	`return " OFFSET " + offset + " ROWS FETCH FIRST " + count + " ROWS ONLY";`
`1289`	`1289`	`}`
`1290`	`1290`
`1291`		`- return " LIMIT " + count + " OFFSET " + offset;`
	`1291`	`+ return " LIMIT " + count + (offset <= 0 ? "" : " OFFSET " + offset); // DELETE, UPDATE 不支持 OFFSET`
`1292`	`1292`	`}`
`1293`	`1293`
`1294`	`1294`	`//WHERE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<`
`@@ -2179,20 +2179,23 @@ public String getContainString(String key, Object[] childs, int type) throws Ill`
`2179`	`2179`	`String condition = "";`
`2180`	`2180`	`if (childs != null) {`
`2181`	`2181`	`for (int i = 0; i < childs.length; i++) {`
`2182`		`- if (childs[i] != null) {`
`2183`		`- if (childs[i] instanceof JSON) {`
	`2182`	`+ Object c = childs[i];`
	`2183`	`+ if (c != null) {`
	`2184`	`+ if (c instanceof JSON) {`
`2184`	`2185`	`throw new IllegalArgumentException(key + "<>:value 中value类型不能为JSON！");`
`2185`	`2186`	`}`
`2186`	`2187`
`2187`	`2188`	`condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR));`
`2188`	`2189`	`if (isPostgreSQL()) {`
`2189`		`- condition += (getKey(key) + " @> " + getValue(newJSONArray(childs[i]))); //operator does not exist: jsonb @> character varying "[" + childs[i] + "]");`
	`2190`	`+ condition += (getKey(key) + " @> " + getValue(newJSONArray(c))); //operator does not exist: jsonb @> character varying "[" + c + "]");`
`2190`	`2191`	`}`
`2191`	`2192`	`else if (isOracle()) {`
`2192`		`- condition += ("json_textcontains(" + getKey(key) + ", '$', " + getValue(childs[i].toString()) + ")");`
	`2193`	`+ condition += ("json_textcontains(" + getKey(key) + ", '$', " + getValue(c.toString()) + ")");`
`2193`	`2194`	`}`
`2194`	`2195`	`else {`
`2195`		`- condition += ("json_contains(" + getKey(key) + ", " + getValue(childs[i].toString()) + ")");`
	`2196`	`+ boolean isNum = c instanceof Number;`
	`2197`	`+ String v = (isNum ? "" : "\"") + childs[i] + (isNum ? "" : "\"");`
	`2198`	`+ condition += ("json_contains(" + getKey(key) + ", " + getValue(v) + ")");`
`2196`	`2199`	`}`
`2197`	`2200`	`}`
`2198`	`2201`	`}`
`@@ -2390,9 +2393,9 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {`
`2390`	`2393`	`case POST:`
`2391`	`2394`	`return "INSERT INTO " + tablePath + config.getColumnString() + " VALUES" + config.getValuesString();`
`2392`	`2395`	`case PUT:`
`2393`		`- return "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true);`
	`2396`	`+ return "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true) + config.getLimitString();`
`2394`	`2397`	`case DELETE:`
`2395`		`- return "DELETE FROM " + tablePath + config.getWhereString(true);`
	`2398`	`+ return "DELETE FROM " + tablePath + config.getWhereString(true) + config.getLimitString();`
`2396`	`2399`	`default:`
`2397`	`2400`	`String explain = (config.isExplain() ? (config.isSQLServer() \|\| config.isOracle() ? "SET STATISTICS PROFILE ON " : "EXPLAIN ") : "");`
`2398`	`2401`	`if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) {`
`@@ -2635,6 +2638,10 @@ public static SQLConfig newSQLConfig(RequestMethod method, String table, String`
`2635`	`2638`	`throw new NotExistException(TAG + ": newSQLConfig idIn instanceof List >> 去掉无效 id 后 newIdIn.isEmpty()");`
`2636`	`2639`	`}`
`2637`	`2640`	`idIn = newIdIn;`
	`2641`	`+`
	`2642`	`+ if (method == DELETE \|\| method == PUT) {`
	`2643`	`+ config.setCount(newIdIn.size());`
	`2644`	`+ }`
`2638`	`2645`	`}`
`2639`	`2646`
`2640`	`2647`	`//对id和id{}处理，这两个一定会作为条件`
`@@ -2670,6 +2677,10 @@ else if (id instanceof Subquery) {}`
`2670`	`2677`	`throw new NotExistException(TAG + ": newSQLConfig idIn != null && (((List<?>) idIn).contains(id) == false");`
`2671`	`2678`	`}`
`2672`	`2679`	`}`
	`2680`	`+`
	`2681`	`+ if (method == DELETE \|\| method == PUT) {`
	`2682`	`+ config.setCount(1);`
	`2683`	`+ }`
`2673`	`2684`	`}`
`2674`	`2685`
`2675`	`2686`