java自定义sql 格式化工具

/**
 * @author ：Frans
 * @date ：Created in 2021/11/3 20:06
 * @description： sql占位符替换工具类
 * @modified By：
 */
public class SqlPlaceholderReplaceUtil {

	/**
	 *  替换掉SQL注入的那些字符 ['|;|--| and | or ]
	 */
	private static String SQL_INJECT_CHARS = "([';]+|(--)+|(\\s+([aA][nN][dD])\\s+)+|(\\s+([oO][rR])\\s+)+)";

	/**
	 * 替换{}的sql 也可以支持#{xx}
	 */
	private static PropertyPlaceholderHelper helper = new PropertyPlaceholderHelper(
			"{", "}");

	/**
	 * 替换占位符
	 * @param value
	 * @param properties
	 * @return
	 */
	public static String replacePlaceholders(String value, final Properties properties) {
		return helper.replacePlaceholders(value, properties);
	}

	/**
	 * @author: Frans
	 * @description: 根据问号占位符替换sql
	 * @date 2021/12/3 14:49
	 * @param sql
	 * @return params
	 */
	public static String paddingParam(String sql , List<Object> params) {
		if(CollectionUtil.isEmpty(params)){
			return sql;
		}
		//参数个数至少是?个数
		int countQuestion = StrUtil.count(sql, "?");
		if(countQuestion > params.size()){
			throw new IllegalArgumentException("sql need " + countQuestion + " params, but has only " + params.size() + "\r\n" + sql + "\r\n" + params);
		}

		// 填充参数
		for(int i = 0 , size = params.size(); i < size; i++){
			// 1.巧妙利用替换一次之后，后面的?就自动往前移动一位，那么replaceFirst每次替换的就是下一个?
			// 2.去掉某些特殊符号，防注入
			String param = (params.get(i) instanceof Number) ? params.get(i) + "" :
					"'" + params.get(i).toString().replaceAll(SQL_INJECT_CHARS, "")
							+ "'";
			sql = sql.replaceFirst("\\?", param);
		}
		return sql;
	}

	public static void main(String[] args) {
		String sql ="select * from sys_user where name = ? and age = ?";
//		Properties properties = new Properties();
//		properties.setProperty("name","foss");
//		properties.setProperty("age","'12'");
		String name = "zhangsan";
		int age = 12;
		List<Object> params = new ArrayList<>();
		params.add(name);
		params.add(age);
		String s = paddingParam(sql, params);
		System.out.println(s);
	}

部分代码转自码云

在网上没有看到解决办法,特此发布,以便于解决诸位码友困扰.