SoFunction
Updated on 2025-03-06

Mybatis Order by dynamic parameter anti-injection method

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.