Mybatis-3: Feature Request: Provide an intuitive way to insert escape column / table names in SQL builder

[sluongng]

Current SQL class which extends AbstractSQL does not provide an intuitive way to escape columns and table names.

Suggestion: including an option (perhaps global config?) to have "escape character style" be enabled by default.

I.e.: mybatis.sql-builder.escape-style=MYSQL (POSTGRES etc...)
This would result columns and tablenames being wraped by constant character like

---

// Given
new SQL().INSERT_INTO("my_table")
  .VALUES("name", "#{name}")
  .VALUES("address", "#{address}")
  .toString();

// Expect
INSERT INTO `my_table` (`name`, `address`) VALUES (#{name}, #{address})

// Actual
INSERT INTO my_table (name, address) VALUES (#{name}, #{address})

[sluongng]

This ideally would be an Enum option inside AbstractSQL class and exposed to mybatis spring boot starter module as a configuration.

[kazuki43zoo]

@sluongng Thanks you for your suggestion!

I think your idea is interesting, but I think not easy that apply your idea to the SQL class because it is designed that specify an SQL statement directly on some methods as follow:

e.g.

SET("a = #{a}, b = #{b}")

INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");

In this cases, we need to parse a specified sql statement to apply the escape character.

I have one question. In fact, Is there case that you need to escape all object names? In my guess, I think you don't need to escape the object name by most cases. So that I'm wondering how much benefit there is to add the logic to parse the specified SQL statement and let it escape.

I think that it is sufficient to specify the escaped object name as needed as follows:

new SQL().INSERT_INTO("`rank`")
  .VALUES("a", "#{a}")
  .VALUES("b", "#{b}")
  .toString();

WDYT?

---

@harawata @jeffgbutler Please give me a comment if need.

[sluongng]

Its the same concern that led me to use SQL() builder api instead of just returning plain String query: better accuracy and less risk.

If you are building a product that you may need to transfer the maintenance to other people, OR a library on top of mybatis, it would be nice to have some safety guarantee that downstream user would not made silly mistakes.

In fact, I would settle for a simple static function that can just escape the string given a DB type / SQL syntax enum.

INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");

I would also like to separate the ON() syntax as a separate childBuilder class after a JOIN() to force the user to declare JOIN("").ON("") together but perhaps thats too extreme.

[jeffgbutler]

@kazuki43zoo I agree that we should avoid parsing SQL in all cases! I think with the SQL() class it is incumbent on the user to ensure that names are escaped properly.

@sluongng I suggest that you take a look at https://github.com/mybatis/mybatis-dynamic-sql - this is a more capable API for building SQL than the SQL() class included in MyBatis. It will handle name escaping in a more consistent manner.

[sluongng]

So i tested mybatis-dynamic-sql the last couple of days. Have some thoughts about it but I will leave it for another issue on that project.

Generally I still prefer the simplicity of SQL() builder class.

---

As for this issue, I am thinking of another approach:

1. Provide a static class for fluent api style init.
   Instead of having to call new SQL().SELE...., user should be able to SQL.init() or SQL.builder()

2. Provide an 'interceptor'-like configuration where you can provider a list of functional interceptor that help with sanitizing / customizing builder input.

SQL.init()
  .withInterceptor(s -> s)
  .withInterceptor(s -> "`" + s + "`")
  .SELECT("a")
  .toString();

// result in: SELECT `a`

Interceptor can also take in some sort of Enum event (select, insert, update, group by) to handle each action separately.