mysql和mogodb特殊字符转义

描述: 页面如果在查询输入框中输入特殊字符, 可能会对数据库查询有影响, 后端可能会报异常, 需要前端对请求参数加码, 后端接口对请求参数解码, 后端并对查询参数特殊字符转义处理

import org.springframework.util.ObjectUtils;
public class BaseStringUtil {
    private BaseStringUtil() {
    }

    public static String escapeChar(String before) {
        if (!ObjectUtils.isEmpty(before)) {
            before = before.replace("/", "//");
            before = before.replace("_", "/_");
            before = before.replace("%", "/%");
        }
        return before;
    }

    public static String escapeQueryChars(String s) {
        if (ObjectUtils.isEmpty(s)) {
            return s;
        } else {
            StringBuilder sb = new StringBuilder();
            for(int i = 0; i < s.length(); ++i) {
                char c = s.charAt(i);
                if (c == '\\' || c == '+' || c == '-' || c == '!' || c == '(' || c == ')' || c == ':' || c == '^' || c == '[' || c == ']' || c == '"' || c == '{' || c == '}' || c == '~' || c == '*' || c == '?' || c == '|' || c == '&' || c == ';' || c == '/' || c == '.' || c == '$' || Character.isWhitespace(c)) {
                    sb.append('\\');
                }
                sb.append(c);
            }
            return sb.toString();
        }
    }
}

import cn.hutool.core.net.URLDecoder;
import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import java.nio.charset.Charset;
import java.util.HashMap;
import java.util.Map;

public class ParamsUtil {
    private ParamsUtil() {
        // 工具类私有构造方法
    }

    /**
     * mongodb解码
     * @param value
     * @return
     */
    public static String mongodbDecode(String value) {
        if (StringUtils.isEmpty(value)) {
            return value;
        }
        String s = URLDecoder.decode(value, Charset.defaultCharset());
        return BaseStringUtil.escapeQueryChars(s);
    }

    /**
     * mysql解码
     * @param value
     * @return
     */
    public static String mySqlDecode(String value) {
        if (StringUtils.isEmpty(value)) {
            return value;
        }
        String s = URLDecoder.decode(value, Charset.defaultCharset());
        return BaseStringUtil.escapeChar(s);
    }
}

@RestController
@RequestMapping("/api/search/adGroup")
public class AdGroupController implements AdGroupApi {
    @GetMapping("/export")
    @Override
    public void export(
                     @RequestParam(required = false, name = "adGroup")
                     String adGroup) {
        adGroup = ParamsUtil.mySqlDecode(adGroup);
    }
}


// mysql查询时对特殊字符转义
public PageResult<GroupResultDto> listByGroupName(String groupName) {
     List<Group> groupList = groupMapper.listByGroupName(BaseStringUtil.escapeChar(groupName));
}


public interface GroupMapper {
    List<Group> listByGroupName(@Param("groupName") String groupName);
}

 

<select id="listByGroupName" parameterType="java.lang.String" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from cmd_group
  where del_flag = 0
  <if test="groupName != null and groupName != ''">
    and group_name like concat('%', #{groupName}, '%')  escape '/'
  </if>
  order by update_time desc
</select>