2

I would like to expose to my frontend the ability to search using logical expressions that are specified at runtime (with restrictions to ensure performance, but that's another topic). That is, I would like to run queries with WHERE clauses like

WHERE ((email like 'test') AND (user_id=1 OR user_id>=1000)) OR (trans_id <100 AND session_id> 500)

without knowing the tree structure of the logic up front (desire #1). I would also like to use the #{} mechanism (which substitutes "?" in the SQL string and builds a list of parameters to pass to a PreparedStatement) to encapsulate user-provided values (desire #2). Finally, I would like to use XML query construction rather than SQLBuilder construction because I am working with a significant amount of code based on the former (desire #3).

Unfortunately, even though the task which I want Mybatis to perform is well within the bounds of possibility (it's just recursion over a small hierarchy of java objects!) the limitations of mybatis seem to conspire to prevent me from achieving #1, #2, and #3 simultaneously. Hopefully I'm just missing something. Here is what I have tried:

Strategy A: Limit user queries to "OR of ANDs" structure (abandon #1). Those of you familiar with DeMorgan's laws will note that any logical tree can be put into this form fairly easily -- however, the DeMorganated statements cannot then be fed to MyBatis cleanly because the leaf statements (the things which get ANDed together -- e.g. user_id=1) do not have a fixed number of #{} arguments and cannot therefore be the subject of a foreach. This is the strategy taken in MyBatis - how to create w dynamic WHERE Clause

Strategy B: Just insert the recursively constructed WHERE with ${} (abandon #2). I must instead sanitize my own user input (slightly concerning) and translate it into strings which will no doubt introduce subtle bugs where the string conversion and subsequent parsing by the SQL server behaves differently than the '?' mechanism which passes objects out-of-band to the JDBC driver (highly concerning).

Strategy C: Convert all my code to SQLBuilder construction (abandon #3). I don't like this approach because SQLBuilder doesn't seem particularly mature -- in particular, there's an open issue speculating about strategies for adding parenthesis support, which I would no doubt need. https://github.com/mybatis/mybatis-3/issues/362

Strategy D: Build the WHERE clause recursively, manually inserting ? marks and keeping track of arguments to pass to a PreparedStatement. Manually chunk this string into blocks containing one ? each and then iterate over them from the XML. This will work but is painfully ugly.

<foreach item="block" index="i" collection="oneParamBlocks" open="" separator="" close="">${block.pfx}#{block.param}${block.sfx}</foreach>

Strategy E (wishful thinking): there's a mechanism I am not aware of to perform ${whereClause} substitution where the whereClause can contain a few "?" characters and the corresponding objects are specified by packaging them with the string somehow (e.g. getWhereClause() returns an object containing both the string "WHERE user_name=? AND user_email=?" and an array of parameters {"Bob","bob@example.com"}).

This is where mybatis parses #{}: https://github.com/mybatis/mybatis-3/blob/2d079fdd397f8a801679f701188be6e05ee9fdf5/src/main/java/org/apache/ibatis/builder/SqlSourceBuilder.java

This is where mybatis parses ${}: https://github.com/mybatis/mybatis-3/blob/2d079fdd397f8a801679f701188be6e05ee9fdf5/src/main/java/org/apache/ibatis/parsing/PropertyParser.java

If none of you can find something I've missed I'll probably just code up something to let me implement Strategy E and send a pull request.

Community
  • 1
  • 1
jjoonathan
  • 405
  • 5
  • 12

1 Answers1

0

It turned out to be possible to "cleanly" convert the logic tree to a token list after all. Key observation: the only filters which directly contain unsanitized data requiring a filter-dependent number of #{} substitutions are leaf filters. Interior filters (AND and OR) can be implemented by a generic token type which drops strings into the query. This is what we wound up using:

<foreach item="tok" collection="filter.tokenRepresentation" open="" separator="" close=""><choose>
        <when test="tok.mybatisChooseType == 'str'">${tok.hardcodedGlueStr}</when>

        <when test="tok.mybatisChooseType == &quot;&lt;&quot;">${tok.filter.validatedColumnName}&lt;#{tok.filter.unsanitizedLiteral}</when>
        <when test="tok.mybatisChooseType == &quot;&gt;&quot;">${tok.filter.validatedColumnName}&gt;#{tok.filter.unsanitizedLiteral}</when>
        ...
 </choose></foreach>

The tokenRepresentation is constructed by straightforward depth first search. The lack of spacing between choose and foreach prevents two newlines from being inserted between every token.

jjoonathan
  • 405
  • 5
  • 12