problems_kingbase
1 sql语句无法执行
Desc:
连接的是人大金仓的数据库，执行时报错。
error log:
2021-03-22 14:54:03.283 ERROR 8556 --- [nio-7779-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: stack depth limit exceeded
Hint: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet
RCA:
因为ids太多了，有14983个id，字符串太长了，导致程序崩溃了。
solution:
不使用hql语法，直接拼接好sql。
/* 旧代码：
private void addTemplateFilter(TReportTemplate tReportTemplate, Map<String, List<?>> map, StringBuffer sql) {
if (!StringUtils.isBlank(tReportTemplate.getComNo())) {
List<String> ids = Arrays.asList(tReportTemplate.getComNo().split(","));
if (!CollectionUtils.isEmpty(ids)) {
sql.append(" AND M.ID IN (:ids)");
map.put("ids", ids);
}
}
}
*/
旧sql:
SELECT M.COMNAME,M.ID , CASE WHEN to_char(COMCODE)='-' THEN '－' ELSE to_char(COMCODE)END AS COMCODE,
CASE WHEN to_char(COMJTNAME)='-' THEN '－' ELSE to_char(COMJTNAME)END AS COMJTNAME,
CASE WHEN to_char(COMHQCODE)='-' THEN '－' ELSE to_char(COMHQCODE)END AS COMHQCODE,
CASE WHEN to_char(COMPANYLEVEL)='-' THEN '－' ELSE to_char(COMPANYLEVEL)END AS COMPANYLEVEL,
CASE WHEN to_char(JYGM)='-' THEN '－' ELSE to_char(JYGM)END AS JYGM,
CASE WHEN to_char(ZZXS)='-' THEN '－' ELSE to_char(ZZXS)END AS ZZXS,
CASE WHEN to_char(SZDQ)='-' THEN '－' ELSE to_char(SZDQ)END AS SZDQ
from (
SELECT B.SSHYCODE1,B.XZGXCODE1,B.INAREA1,B.BELONG_CODE,B.ABROADCODE1,A.*
FROM TDATA_BROWSER_2020 A
LEFT JOIN TCOMPANYDETAIL_2020 B ON A.COMNO = B.COMNO) M
WHERE 1=1 AND M.ID IN (:ids)
// 新代码
private void addTemplateFilter(TReportTemplate tReportTemplate, StringBuffer sql) {
String tmpComNos = tReportTemplate.getComNo();
if (!StringUtils.isBlank(tmpComNos)) {
sql.append(" AND M.ID IN ('")
.append(tmpComNos.replace(",", "','"))
.append("')");
}
}
新sql:
SELECT M.COMNAME,M.ID , CASE WHEN to_char(COMCODE)='-' THEN '－' ELSE to_char(COMCODE)END AS COMCODE,
CASE WHEN to_char(COMJTNAME)='-' THEN '－' ELSE to_char(COMJTNAME)END AS COMJTNAME,
CASE WHEN to_char(COMHQCODE)='-' THEN '－' ELSE to_char(COMHQCODE)END AS COMHQCODE,
CASE WHEN to_char(COMPANYLEVEL)='-' THEN '－' ELSE to_char(COMPANYLEVEL)END AS COMPANYLEVEL,
CASE WHEN to_char(JYGM)='-' THEN '－' ELSE to_char(JYGM)END AS JYGM,
CASE WHEN to_char(ZZXS)='-' THEN '－' ELSE to_char(ZZXS)END AS ZZXS,
CASE WHEN to_char(SZDQ)='-' THEN '－' ELSE to_char(SZDQ)END AS SZDQ
from (
SELECT B.SSHYCODE1,B.XZGXCODE1,B.INAREA1,B.BELONG_CODE,B.ABROADCODE1,A.*
FROM TDATA_BROWSER_2020 A
LEFT JOIN TCOMPANYDETAIL_2020 B ON A.COMNO = B.COMNO) M
WHERE 1=1 AND M.ID IN ('1111','2222', ... ,'3333','4444')
2 前台的密文传递到后端无法解密
error log:
javax.crypto.IllegalBlockSizeException: Input length not multiple of 16 bytes
action:
查看源码后发现前端传递过来的加密参数字符串，不能直接用来解密，还需要经过一步额外的解密 java.net.URLDecoder.decode()，这个是java8内置的解密方法。
RCA:
- 前端传递过来的加密参数字符串，不能直接用来解密，还需要经过一步额外的解密 java.net.URLDecoder.decode()，这个是java8内置的解密方法。
solution:
refer to the following code:
String encryptStrFromFrontEnd = "mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDSzI%2B%2FopqGifYFPQZKjfTNud2vgbzLEU%2FGZzjH8cy6KqMX5F%2BLmJm1TXb0pvDzXNCe2xC89W6VkM%2FH4eYNCvCc7X%2BmJoKUjFxtrsxGVT6yHKYDLrflTPWiZYvdSaE3YkKo%3D";
String encryptStr = URLDecoder.decode(encryptStrFromFrontEnd, "UTF-8");
String json = AESUtils.aesDecode(encryptStr);
System.out.println("解密后的明文是: " + json);
note:
程序最终的输出：
加密后的密文是: mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDSzI%2B%2FopqGifYFPQZKjfTNud2vgbzLEU%2FGZzjH8cy6KqMX5F%2BLmJm1TXb0pvDzXNCe2xC89W6VkM%2FH4eYNCvCc7X%2BmJoKUjFxtrsxGVT6yHKYDLrflTPWiZYvdSaE3YkKo%3D
解密后的明文是: {"companyLevel":"一级集团总部","sshyCode1":"01","columns":["COMCODE"],"id":"","reportYear":"2020","comNo":""}
Attached: source code:
package com.ciic.sca.util;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;
import javax.crypto.*;
import javax.crypto.spec.SecretKeySpec;
import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.security.*;
public class AESUtils {
private static String KEY = "iSCAtdfCIIC@x0w!";
public static void setKey(String key) {
AESUtils.KEY = key;
}
public static String aesEncode(String content){
try {
Cipher cipher = Cipher.getInstance("AES/ECB/NoPadding");
int blockSize = cipher.getBlockSize();
byte[] dataBytes = content.getBytes();
int plaintextLength = dataBytes.length;
if (plaintextLength % blockSize != 0) {
plaintextLength = plaintextLength + (blockSize - (plaintextLength % blockSize));
}
byte[] plaintext = new byte[plaintextLength];
System.arraycopy(dataBytes, 0, plaintext, 0, dataBytes.length);
SecretKeySpec secretKey = new SecretKeySpec(KEY.getBytes(), "AES");
cipher.init(Cipher.ENCRYPT_MODE, secretKey);
byte[] byte_AES = cipher.doFinal(plaintext);
String AES_encode = new String(new BASE64Encoder().encode(byte_AES));
//11.将字符串返回
return AES_encode;
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
} catch (NoSuchPaddingException e) {
e.printStackTrace();
} catch (InvalidKeyException e) {
e.printStackTrace();
} catch (IllegalBlockSizeException e) {
e.printStackTrace();
} catch (BadPaddingException e) {
e.printStackTrace();
}
return null;
}
public static String aesDecode(String content){
try {
Cipher cipher = Cipher.getInstance("AES/ECB/NoPadding");
SecretKeySpec secretKey = new SecretKeySpec(KEY.getBytes(), "AES");
cipher.init(Cipher.DECRYPT_MODE, secretKey);
byte [] byte_content = new BASE64Decoder().decodeBuffer(content);
byte [] byte_decode = cipher.doFinal(byte_content);
String AES_decode = new String(byte_decode,"UTF-8");
return AES_decode;
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
} catch (NoSuchPaddingException e) {
e.printStackTrace();
} catch (InvalidKeyException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (IllegalBlockSizeException e) {
e.printStackTrace();
} catch (BadPaddingException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args) throws Exception {
/*
* 加密
*/
String content = "{\"reportId\":27}";
// String content="{\"reportYear\":\"2019\",\"reportNo\":\"RP05-150100000460\"}";
// String content = "{\"page\":2, \"pageSize\":30, \"filters\":{\"reportYear\":\"2014\",\"reportNo\":\"RP01-150206103730\"}}";
// content = "admin";
String encrypt = AESUtils.aesEncode(content);
System.out.println("加密后的密文是:" + URLEncoder.encode(encrypt, "UTF-8"));
/*
* 解密
*/
// content = encrypt;
// System.out.println("解密后的明文是: " + AESUtils.aesDecode(content));
// content = "unkSPpFasZMVsobdVk8Kn8XSLWjaQBbEELu/Yh+Vkxqt3HhPcCfUq53Ri0g/6UHD1M/ecUfQRWfRBNBxN1ts4sDvTagOrU04pBgdSHNfgBpmPKdl2ex3IBSPg5uEw9mLCr4wIki7M/4aLjt9dIqJO9lTzk3vq4QC2MXP9uQWmS8=";
// content = "mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDRTUt2hxo6b2vtWveKc4HuxYfZdGdYQBLGEA8hdHHurr81n6p3PUVJ7MYnT0Uz6yviw0QQtny5zUfkNbVMGxGN3i%2FZvHuKtACFOoavLpbFlkV9BaRrfUwGhvQhDyvU5JYrCYay0bWYSv6LVY6ywNCX%2FhY%2FmZpAPM%2BcqCPNC8DFVJASBgjrhneZSt4NzUlXEoaU%3D";
// content = "mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDSzI%2B%2FopqGifYFPQZKjfTNu9MWvoY0gvXl4e26eIKmP%2FejuP4zQtG%2F05yiXy0YmLN7PJraGUCAX2QCvsPERjaGzsuE7dZdOs6lpUGnfMADFL2cd9iT5x9f%2Bv8bo8edej48%3D";
// content = "15vYhlyb2g3HPT4jAuIZBV1yxEkdutDnwUTDk7hqCoQ%3D";
// content = "uQLSPnAiCaMDiFbzHlvhrw%3D%3D";
// content = "mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDSzI%2B%2FopqGifYFPQZKjfTNud2vgbzLEU%2FGZzjH8cy6KqMX5F%2BLmJm1TXb0pvDzXNCe2xC89W6VkM%2FH4eYNCvCc7X%2BmJoKUjFxtrsxGVT6yHKYDLrflTPWiZYvdSaE3YkKo%3D";
String encryptStrFromFrontEnd = "mFTHYULUyd784zGWWcrtetJ1PNchhgqM%2Bof6ZfWauDSzI%2B%2FopqGifYFPQZKjfTNud2vgbzLEU%2FGZzjH8cy6KqMX5F%2BLmJm1TXb0pvDzXNCe2xC89W6VkM%2FH4eYNCvCc7X%2BmJoKUjFxtrsxGVT6yHKYDLrflTPWiZYvdSaE3YkKo%3D";
String encryptStr = URLDecoder.decode(encryptStrFromFrontEnd, "UTF-8");
String json = AESUtils.aesDecode(encryptStr);
System.out.println("解密后的明文是:" + json);
}
}
