I have a reproduction. The offending character was successfully encrypted and inserted into a UTF-8 database via a host variable in a prepared statement by a Java program running JDBC 4.10.JC14. The attached ZIP flie contains an unload file and a SQL script to reproduce. Run this against a UTF-8 database and you'll get Err -202 (unless you have EILSEQ_COMPAT_MODE set to 1, in which case you get -23013). Run the same script against a Latin-1 database and everything works fine.
If you try to simply encrypt the offending value via an ad hoc query tool, the engine will [correctly] refuse to encrypt and give the -23013 without allowing the insert. So the issue seems to have something to do with JDBC and/or prepared statements/host variables.I tried to unload the contents of syssqltrace, syssqltrace_iter and syssqltrace_hvar to get full details on the offending statement, but the attempt to unload syssqltrace_hvar also produced the -23013. But onstat -g his showed the offending statement in full:
Statement # 562029: @ 0x4a6cffa8 Database: webacct Statement text: INSERT INTO tjg_kaboom (enc_fname, enc_lname) VALUES ( encrypt_aes(?), encrypt_aes(?)); INSERT using table [ tjg_kaboom ] Iterator/Explain ================ ID Left Right Sender Next Est Cost Est Rows Num Rows Partnum Type 1 0 0 0 0 1 1 1 0 Insert Host Variables ============== 0 varchar Roberto<U+1F1EA><U+1F1F8> 1 varchar Romero Statement information: Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ 189 304 INSERT 15:02:27 0.0004 238f7331 0 Statement Statistics: Page Buffer Read Buffer Page Buffer Write Read Read % Cache IDX Read Write Write % Cache 0 3 100.00 0 0 3 100.00 Lock Lock LK Wait Log Num Disk Memory Requests Waits Time (S) Space Sorts Sorts Sorts 2 0 0.0000 676 B 0 0 0 Total Total Avg Max Avg I/O Wait Avg Rows Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec 2 0.0024 0.0012 0.0018 0.000000 0.000000 2253.8775 Estimated Estimated Actual SQL ISAM Isolation SQL Cost Rows Rows Error Error Level Memory 2 1 1 0 0 CR 22816
Hi,Sounds like one for Tech Support.If you can dump the page to get the row contents and have the key perhaps Tech Support can provide a small C program to decrypt it.
A small C program is what I was hoping for, but so far they haven't offered that. My guess – and it's only that – is that the encrypted data contains an odd character (perhaps an emoji or something) that's throwing off the algorithm.
I'm not sure how that would help. The data in the "bad" column is 43 bytes wide and the column allows a length of up to 250.
Won't that mess up a checksum?
As soon as you truncate even one character off the end of the string, the error [correctly and expectedly] pivots to -26012, "The internal base64 decoding function failed."
Now we're getting somewhere! When I set EILSEQ_COMPAT_MODE to 1, the error changes from 202 to this: 23103: Code-set conversion function failed due to illegal sequence or invalid value.
If it's illegal in en_US.utf8, what codeset might it be legal in?
Andreas is our winner and gets a cookie! The database in question is a UTF-8 database. I unloaded the data and loaded it into a Latin-1 database and was able to decrypt! There is indeed an offending character: the Spanish Flag emoji, which the user entered after his name.
Now the problem pivots: Shouldn't the engine have thrown an error on the encryption step?