An error was reported about the fuzzy query statement

[CarryALin]

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

I used shardingSphere5.3.2。An exception occurred when executing the following sql using mybatis in the project
sql
@select("select * from test_user where password like CONCAT('%',#{keyword},'%') ")
The following exception occurs

But I changed the sql to @select("select * from test_user where password like CONCAT('%','ab','%') ")
The function can run normally

[strongduanmu]

@linhongzhi123 Can you provide more information to help us reproduce this bug?

[CarryALin]

@linhongzhi123 Can you provide more information to help us reproduce this bug?
This error occurs when I upgrade to 5.4.0

---

Invocation of init method failed; nested exception is Cannot create property=rules for JavaBean=org.apache.shardingsphere.driver.api.yaml.YamlJDBCConfiguration@cc5460
in 'reader', line 1, column 1:
dataSources:
^
Cannot create property=tables for JavaBean=org.apache.shardingsphere.encrypt.yaml.config.YamlEncryptRuleConfiguration@186374c
in 'reader', line 9, column 3:
- !ENCRYPT
^
Cannot create property=columns for JavaBean=org.apache.shardingsphere.encrypt.yaml.config.rule.YamlEncryptTableRuleConfiguration@7bc16b
in 'reader', line 12, column 7:
columns:
^
Cannot create property=cipherColumn for JavaBean=org.apache.shardingsphere.encrypt.yaml.config.rule.YamlEncryptColumnRuleConfiguration@6ad6d5
in 'reader', line 14, column 11:
cipherColumn: id_card
^
Unable to find property 'cipherColumn' on class: org.apache.shardingsphere.encrypt.yaml.config.rule.YamlEncryptColumnRuleConfiguration
in 'reader', line 14, column 25:
cipherColumn: id_card
^

in 'reader', line 13, column 9:
id_card:
^

in 'reader', line 11, column 5:
test_user:
^

in 'reader', line 9, column 1:
- !ENCRYPT
^

[strongduanmu]

The encryption API has been changed in version 5.4.0. Please refer to the documentation for modification.

[CarryALin]

@linhongzhi123 Can you provide more information to help us reproduce this bug?

Here is my configuration information

.properties
spring.datasource.dynamic.datasource.shareen.url=jdbc:shardingsphere:classpath:sharding.yml
spring.datasource.dynamic.datasource.shareen.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver

---

.yml
dataSources:
shareen:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.postgresql.Driver
jdbcUrl: jdbc:postgresql://localhost:5432/tz_wg_zx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&stringtype=unspecified
username: postgres
password: 123456
rules:

• !ENCRYPT
  tables:
  test_user:
  columns:
  id_card:
  cipher:
  name: id_card
  encryptorName: password_encryptor
  likeQuery:
  name: id_card_like
  encryptorName: like_encryptor
  queryWithCipherColumn: true
  encryptors:
  assisted_encryptor:
  type: AES
  props:
  aes-key-value: 123456abc
  like_encryptor:
  type: CHAR_DIGEST_LIKE
  password_encryptor:
  type: AES
  props:
  aes-key-value: 123456abc
  props:
  sql-show: true

---

The above error occurs when the configuration is performed according to the 5.4.0 documentation。
Hope you can answer

[CarryALin]

The encryption API has been changed in version 5.4.0. Please refer to the documentation for modification.

my yml

[CarryALin]

I checked my configuration file and the format should be correct

[CarryALin]

Can the information be reproduced? If not, I can provide you with more information you need

[CarryALin]

加密 API 在 5.4.0 版本中已更改。请参考文档进行修改。
I am waiting for your reply

[strongduanmu]

Can the information be reproduced? If not, I can provide you with more information you need

The queryWithCipherColumn has been removed.Everything else looks right.

[CarryALin]

I have modified it according to your prompt, but it doesn't work. Do I need to add other dependencies? Here are my dependencies

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.7</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>
    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>4.2.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core</artifactId>
        <version>5.4.0</version>
    </dependency>
    <dependency>
        <artifactId>snakeyaml</artifactId>
        <groupId>org.yaml</groupId>
        <version>1.33</version>
    </dependency>

[strongduanmu]

Can you provide a demo to help reproduce this exception?

[CarryALin]

您能提供一个演示来帮助重现此异常吗？
After investigation, the reason is that I used Dynamic. During the configuration, the data source with vague encryption was handed over to Dynamic to manage, and the problem occurred when I read yml. My Dynamic configuration is as follows:

@configuration
@AutoConfigureAfter({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfig {

public static final String SHARDING_DATA_SOURCE_NAME = "shareen";
/**
 * 动态数据源配置项
 */
@Resource
private DynamicDataSourceProperties dynamicDataSourceProperties;

/**
 * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
 *
 * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
 * <p>2. 主从数据源: masterSlaveDataSource;
 * <p>3. 脱敏数据源：encryptDataSource;
 * <p>4. 影子数据源：shadowDataSource
 * <p>
 * shardingjdbc默认就是shardingDataSource
 * 如果需要设置其他的可以使用
 *
 * @Resource(value="") 设置
 */
@Lazy
@Resource
DataSource shardingDataSource;

/**
 * 将shardingDataSource放到了多数据源（dataSourceMap）中
 * 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
 */
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
    Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
    return new AbstractDataSourceProvider() {
        @SneakyThrows
        @Override
        public Map<String, DataSource> loadDataSources() {
            File file = new File("src/main/resources/sharding.yml");
            DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(file);
            Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
            // 将 shardingjdbc 管理的数据源也交给动态数据源管理
            dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, dataSource);
            return dataSourceMap;
        }
    };
}

/**
 * 将动态数据源设置为首选的
 * 当spring存在多个数据源时, 自动注入的是首选的对象
 * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
 *
 * @return
 */
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
    DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
    dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
    dataSource.setStrict(dynamicDataSourceProperties.getStrict());
    dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
    dataSource.setProvider(dynamicDataSourceProvider);
    dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
    dataSource.setSeata(dynamicDataSourceProperties.getSeata());
    return dataSource;
}

---

DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(file);
//There was a problem reading yml here

However, neither encryption nor blurring will work if I do not configure it this way, which is OK in version 5.3.2. If I want to use shardingSphere with Dynamic in version 5.4.0, how should I configure it? Please give me some guidance

[CarryALin]

您能提供一个演示来帮助重现此异常吗？

https://github.com/linhongzhi123/springbootdemo

---

This is my demo, please help me to have a look

[CarryALin]

您能提供一个演示来帮助重现此异常吗？

https://github.com/linhongzhi123/springbootdemo

I changed the api call mode to start normally, but it still has the same problem when fuzzy search as 5.3.2

The encryption is normal and the password_like field can write normal values

This exception also occurs when the project is started

DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(createDataSources(), Collections.singleton(encryptRuleConfig), props);

selectSQL：
==> Parameters: 111(String)
2023-09-18 15:17:35.007 INFO 20416 --- [nio-9050-exec-2] ShardingSphere-SQL : Logic SQL: select * from test_user where password like CONCAT('%',?,'%')
2023-09-18 15:17:35.008 INFO 20416 --- [nio-9050-exec-2] ShardingSphere-SQL : Actual SQL: shareen ::: select test_user."id", test_user."name", test_user."password" AS "password", test_user."phone", test_user."create_time", test_user."password_ext" from test_user where password_like like CONCAT ('%', ?, '%') ::: [000]

[CarryALin]

The query error is caused by the &stringtype=unspecified parameter following the database connection, but the following exception still occurs during startup

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.