terrymanu commented on issue #37025:
URL:
https://github.com/apache/shardingsphere/issues/37025#issuecomment-3497158342
Based on your description, I understand you're encountering the following
issues with ShardingSphere-Proxy 5.5.2 encryption:
1. SELECT with functions: Functions like CONCAT(), UPPER(), LOWER(),
LENGTH(), SUBSTRING() operate on ciphertext instead of decrypted values
2. UPDATE with functions: CONCAT() in UPDATE statements concatenates
ciphertext directly
3. UPDATE with CASE/JOIN: Complex expressions store plaintext without
encryption
Root Cause Analysis
This is not a bug but rather a design limitation of ShardingSphere's
encryption feature. According to the
https://shardingsphere.apache.org/document/current/en/features/encrypt/appendix/:
Unsupported SQL Operations:
- Calculation operations are not supported for encrypted fields, such as
AVG, SUM, and computation expressions
- Case-insensitive queries are not supported by encrypted fields
- Comparison operations are not supported for encrypted fields, such as
GREATER THAN, LESS THAN, ORDER BY, BETWEEN
The functions you're using (CONCAT, UPPER, LOWER, LENGTH, SUBSTRING) fall
under computation expressions, which are explicitly not supported by
ShardingSphere encryption.
Recommended Solution
Change Your Usage Pattern
Avoid using SQL functions on encrypted fields. Instead, handle these
operations in your application layer:
-- NOT RECOMMENDED (not supported)
SELECT CONCAT(status, '_update') FROM safe_string_constraints;
SELECT UPPER(status) FROM safe_string_constraints;
UPDATE safe_string_constraints SET status = CONCAT(status, '_updated')
WHERE id = 5;
UPDATE safe_string_constraints SET status = CASE WHEN rating = '4' THEN
'active' ELSE status END;
-- RECOMMENDED (application-layer processing)
-- 1. Query decrypted data
SELECT status FROM safe_string_constraints WHERE id = 5;
-- 2. Process strings in your application code
-- 3. Update using parameterized queries
UPDATE safe_string_constraints SET status = ? WHERE id = ?;
Use Supported Operations
-- SUPPORTED operations
SELECT status FROM safe_string_constraints WHERE id = ?;
SELECT * FROM safe_string_constraints WHERE status = ?; -- requires
assisted query column
INSERT INTO safe_string_constraints (id, status) VALUES (?, ?);
UPDATE safe_string_constraints SET status = ? WHERE id = ?;
DELETE FROM safe_string_constraints WHERE id = ?;
Additional Information Needed
If you believe your use case should be supported, please provide:
1. Complete encryption configuration:
CREATE ENCRYPT RULE `encrypt_rule` (
-- full configuration details
);
2. Table structure:
DESCRIBE safe_string_constraints;
3. Business requirements:
- Why do you need database-level string operations?
- Can these operations be moved to the application layer?
- What's your data volume scale?
4. Expected behavior:
- For CONCAT(status, '_update'), do you expect decrypted concatenation?
- For UPDATE operations, should encryption be automatic?
Conclusion
This is an expected behavior limitation, not a bug. You should adjust your
usage pattern by moving complex string processing logic to the application
layer, which is the recommended approach for ShardingSphere encryption.
If your business requires database-level function processing, consider:
1. Using other ShardingSphere features (like data masking)
2. Implementing encryption/decryption in your application
3. Using database native encryption features
--
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]
