MyBatis: how to pass default parameter to every query

Question

I'm developing a Spring Boot application and I need to pass a default parameter to every select query from a table.

This in order to get some previously encrypted user data.

Ex:

select
         a.ID,
         CAST(AES_DECRYPT(a.FIRST_NAME, SHA2(#{secretKey}, 512)) AS CHAR) FIRST_NAME,
         CAST(AES_DECRYPT(a.LAST_NAME, SHA2(#{secretKey}, 512)) AS CHAR) LAST_NAME,
         USERNAME,
         PASSWD,
         b.ID ID_ROLE,
         b.NAME ROLE_NAME
       from users a join
       roles b on a.ID_ROLE = b.ID
       where
        USERNAME = #{username,jdbcType=VARCHAR}

I'm using the mybatis-spring library with a SqlSessionFactoryBean configured in this way

SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:sqlmaps/*.xml"));
        sessionFactory.setDataSource(ds);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.getVariables().put("secretKey", "my secret key");        
        configuration.setCallSettersOnNulls(true);
        sessionFactory.setConfiguration(configuration);
        return sessionFactory;

But the "secretKey" It's not passed to the query.

What's the correct way to achieve this?

Thanks.

ave · Accepted Answer · 2021-02-16 01:29:28Z

2

#{} is for referencing parameter(s) and its properties.
To reference variables, you need to use ${}.

As ${} is string substitution, it must be enclosed in single quotes and it is your responsibility to escape special characters like ' in the string.

SHA2('${secretKey}', 512)

Note that this solution is not recommended when secretKey is user-provided string because it is vulnerable to SQL injection attack.

edited Feb 16, 2021 at 1:29

answered Feb 15, 2021 at 4:10

ave

3,7242 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

The Impaler Over a year ago

Beware. I don't think this is a correct answer. ${} shouldn't be used indiscriminately. It's open to SQL Injection and should be used only when you control the source of the parameter; that is, the parameter does not come from outside the code base (like the UI, a file, etc.).

ave Over a year ago

In the provided code, secretKey is put into the configuration, so I assumed that it is not a user-provided string.

The Impaler Over a year ago

Then, all is good. Just wanted to make sure the OP won't use this strategy the wrong way.

ave Over a year ago

Added a note as it's a very important point. Thank you for the comment, @TheImpaler !

HK15 Over a year ago

@TheImpaler can confirm that the secret key is passed by configuration so It's safe

Collectives™ on Stack Overflow

MyBatis: how to pass default parameter to every query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related