1. Let me first mention Mybatis dynamic parameters
Parameter symbols | Compilation | Safety | value |
#{} | Precompiled | Safety | The processed value and character types are all in double quotes. |
${} | Not precompiled | Unsafe, there is a SQL injection problem | Whatever comes in is what |
2. Order by dynamic parameters
Order by The following parameter value is a table field or SQL keyword
So using #{} is invalid, only ${}
Then the SQL injection problem is here
3. Solve the problem of Order by dynamic parameter injection
1. Use regular expressions to avoid
Special characters * + - / _ etc
Use indexOf to determine that it has returned directly
There may be other situations that cannot be completely avoided, but you can check it step by step
2. Skills
- 2.1 Think about order by dynamic parameters first
There are only two combinations
- order by field name (asc can be omitted directly)
- order by field name desc
So we just need to find the corresponding set and judge whether our dynamic sorting conditions are included.
- 2.2 Get the sorting condition set
Some hardworking friends may have started writing
userOrderSet = ['id','id desc','age','age desc',.......] scoreOrderSet = ['yuwen','yuwen desc','shuxue','shuxue desc',.......] .............
If there are n tables n fields, it will be a big deal
We use annotation + mapping to get
- 2.3 Dynamic collection
/** First, transform the entity class, including @Column annotation mapping, and also using @JsonProperty. Both are fine. If you don’t use @Column mapping, just add @JsonProperty, which does not affect it. I am lazy to use @JsonProperty **/ @Data @Builder @JsonIgnoreProperties(ignoreUnknown = true) public class ContentEntity { @JsonProperty("id") private Long id;//Primary key ID @JsonProperty("code") private String code;//coding @JsonProperty("content") private String content;//content @JsonProperty("is_del") private Integer isDel;// Whether to delete, 0 not deleted, 1 has been deleted @JsonProperty("creator") private String creator;//Create @JsonProperty("creator_id") @JsonFormat(pattern = DatePattern.NORM_DATETIME_PATTERN, timezone = "GMT+8") private Date createAt;//Creation time @JsonProperty("updater") private String updater;//Update @JsonProperty("updater_id") @JsonFormat(pattern = DatePattern.NORM_DATETIME_PATTERN, timezone = "GMT+8") private Date updateAt;//Update time }
/**Tool class is here**/ public class MybatisDynamicOrderUtils { private static final String desc = " desc"; /** * Get the object JsonProperty value list Replace it with Column * @param object * @return */ public static Set<String> getParamJsonPropertyValue(Class<?> object){ try { //Get filed array Set<String> resultList = new HashSet<>(); Field[] fields = (); for (Field field:fields){ //Get JsonProperty annotation if(()!=null){ JsonProperty annotation = (); if (annotation != null) { //Get the value of JsonProperty String jsonPropertyValue = (); (jsonPropertyValue); } } } return resultList; }catch (Exception e){ (); } return null; } /** * Determine whether the dynamic order is reasonable * @param order * @param object * @return */ public static Boolean isDynamicOrderValue(String order,Class<?> object){ //First get the collection in JsonProperty annotation Set<String> set = getParamJsonPropertyValue(object); //Belong to the direct field and return directly if((order)){ return true; } //There are more inverse order, first remove the inverse order field and then make a judgment if((desc)>0){ String temp = (0,(desc)); if((temp)){ return true; } } return false; } }
//Call the operation //Check whether the dynamic order is reasonable and prevent SQL injectionif(!(sort,)){ ("dynamic order is error:{}",sort); return null; } // @Select({"<script>select * from content order by ${sort}</script>"}) List<ContentEntity> getList(@Param("sort") String sort);
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.