GaoEinstein opened a new issue, #25690:
URL: https://github.com/apache/shardingsphere/issues/25690
## Bug Report
The problem of using "join query" during update sql using the sharding
encryption module.
### Which version of ShardingSphere did you use?
`<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.2</version>
</dependency>
`
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC - encrypt
### Expected behavior
When there are identical ciphertext fields in two tables：
` Logic SQL: update t_person as t1
inner join t_person_addr as t2
on t1.person_name = t2.person_name
set t1.person_name = ?, t1.salaryAmount = ?
where t2.addr = ?`
`Actual SQL: ds0 ::: update t_person as t1
inner join t_person_addr as t2
on t1.person_name = t2.person_name
# Table name aliases will not be removed
set t1.person_name = ?, t1.person_name_query = ?, t1.salaryAmount =
?, t1.salaryAmountQuery = ?
where t2.addr_query = ? :::
[hXjd4OuZZyc42TKFPe+sMKm/vva66Zrr6svZ+9vwgRwY6tEVMcE28ymp5HfTYVHX5gRz7tliHCtifpJoaLfeH0tmioM3O0Kiu/jY+qHPz7Y=,
703073d538184081716a12336b69b208563a1e816ed5b6a92e91138a0bc005ab,
RIgcxGNBXknTyTsOczv/uzgnko9C8nATZvl51gVgFu7t+JcOPPvQiCYwpM7OIfNYDuWC8XRCbmmltoazHCOZeRJ5lNsr7Dme45aaeKbINtI=,
fadbef7b5f3e2e85c2944653076fed3261f2d8b8cf962cd611649355b726fe1d,
65ba77fdcc270873878615c4c1212635202085c8e66b9b8890dfd589df13318f]`
### Actual behavior
`Actual SQL: ds0 ::: update t_person as t1
inner join t_person_addr as t2
on t1.person_name = t2.person_name
set person_name = ?, person_name_query = ?, salaryAmount = ?,
salaryAmountQuery = ?
where t2.addr_query = ? :::
[hXjd4OuZZyc42TKFPe+sMKm/vva66Zrr6svZ+9vwgRwY6tEVMcE28ymp5HfTYVHX5gRz7tliHCtifpJoaLfeH0tmioM3O0Kiu/jY+qHPz7Y=,
703073d538184081716a12336b69b208563a1e816ed5b6a92e91138a0bc005ab,
RIgcxGNBXknTyTsOczv/uzgnko9C8nATZvl51gVgFu7t+JcOPPvQiCYwpM7OIfNYDuWC8XRCbmmltoazHCOZeRJ5lNsr7Dme45aaeKbINtI=,
fadbef7b5f3e2e85c2944653076fed3261f2d8b8cf962cd611649355b726fe1d,
65ba77fdcc270873878615c4c1212635202085c8e66b9b8890dfd589df13318f]`
Alias prefix removed，sql bad error：
`org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause:
java.sql.SQLIntegrityConstraintViolationException: Column 'person_name' in
field list is ambiguous`
### Reason analyze (If you can)
The operation is directly performed through field names, ignoring prefix
table aliases。
code position：
org.apache.shardingsphere.encrypt.rewrite.token.generator.EncryptAssignmentTokenGenerator#generateParameterSQLToken
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
`CREATE TABLE `t_person` (
`person_id` bigint NOT NULL,
`gender` smallint NOT NULL,
`person_name` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
`person_name_plain` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`person_name_query` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
`salaryAmount` varchar(256) CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci DEFAULT NULL COMMENT '薪资-元',
`salaryAmountQuery` varchar(256) CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci DEFAULT NULL COMMENT '薪资-元-查询字符串',
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `t_person_addr` (
`addr_id` bigint NOT NULL,
`person_id` bigint NOT NULL,
`addr` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
`addr_plain` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`addr_query` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`person_name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`person_name_query` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
PRIMARY KEY (`addr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
`
`
rules:
# 加密策略
encrypt:
encryptors:
enc-aes:
type: AES
props:
aes-key-value: 123456abc
enc-aes-c:
type: AES-C
props:
aes-c-key-value: 123456abc
enc-sm3:
type: SM3
tables:
t_person:
columns:
person_name:
encryptorName: enc-aes-c
cipherColumn: person_name
# plainColumn: person_name_plain
assistedQueryColumn: person_name_query
salaryAmount:
logicColumn: salaryAmount
encryptorName: enc-aes-c
cipherColumn: salaryAmount
assistedQueryColumn: salaryAmountQuery
t_person_addr:
columns:
addr:
logicColumn: addr
encryptorName: enc-aes-c
cipherColumn: addr
plainColumn: addr_plain
assistedQueryColumn: addr_query
person_name:
logicColumn: person_name
encryptorName: enc-aes-c
cipherColumn: person_name
assistedQueryColumn: person_name_query
`
`
int updateByConditionAtJoin2(@Param("addrCondition") String
addrCondition,
@Param("newName") String newName,
@Param("newSalary") BigDecimal newSalary);
`
`
<update id="updateByConditionAtJoin2">
update t_person as t1
inner join t_person_addr as t2
on t1.person_name = t2.person_name
set t1.person_name = #{newName}, t1.salaryAmount = #{newSalary}
where t2.addr = #{addrCondition}
</update>
`
`
@Test
void updateByConditionAtJoin2() {
int size = personMapper.updateByConditionAtJoin2("changsha", "job",
BigDecimal.valueOf(10.001));
System.out.println(size);
}
`
### Example codes for reproduce this issue (such as a github link).
It is relatively easy to reproduce, as shown above
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
