The difference between # and $ in MyBatis

Today in the work of a click to sort the function of debugging for a long time, the end of the search, summed up.
The requirement is this, the page has a table, a column of up and down arrows can be clicked and sorted. For this requirement, my Mybatis.xml SQL configuration is written as follows:

<if test= "map. Columnnamesort!=null and map. Columnnamesort!= ' ">
ORDER by ColumnName #{map. Columnnamesort}
</if>

Columnnamesort is the sort of front-end transmission, ASC or DESC.

Then, it should be expected that the output should be similar to the following

ORDER BY columnName Desc

But, really run up, the effect of sorting has not appeared, often a lookup, found to be MyBatis ' #{} ' the issue of value, it compiles the SQL statement into the following

ORDER by ColumnName ' desc ' or ORDER by ColumnName ' ASC '

In this way, DESC or ASC becomes a string rather than a keyword, and the SQL statement means that the alias of the ColumnName is DESC or ASC, and the default is a positive order when the sort key is not added, as follows

ORDER BY ColumnName "DESC" ASC or ORDER BY ColumnName "ASC" ASC

Sort of ineffective problem find out why, to solve, MyBatis provides another way to bind parameters –${param}, change the SQL configuration to

ORDER by ColumnName ${map. Columnnamesort}

In this way, MyBatis will add the Columnnamesort value directly to the SQL and not be escaped. Correct result:

ORDER BY columnName Desc

Finally, make a summary of the difference between the # and $ binding parameters in MyBatis to avoid similar problems in the future.

#{} treats incoming data as a string and adds a double quotation mark to the data that is automatically passed in. For example: ORDER by #{id}, if the value passed in is 111, then the value that is parsed into SQL is Order by "111", and if the value passed is an ID, the parsed SQL is the ORDER by "id".

${} Displays the incoming data directly into the SQL. For example: ORDER BY
${id}, if the value passed in is 111, then the value that is parsed into SQL is Order by 111, and if the value passed is ID, the parsed SQL is the order
by ID.

#方式能够很大程度防止sql注入.

The $ method cannot prevent SQL injection.

The $ method is typically used to pass in database objects, such as incoming table names.

The general can use the # is not to use $.

PS: In the use of MyBatis also encountered <! [cdata[]]> use, the statements within the symbol will not be treated as strings, but rather as SQL statements, such as executing a stored procedure.