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.

Answer 1

#{} 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.