ENCRYPT_AES/DECRYPT_CHAR

Here's an esoteric one for you. If I have a column that's been encrypted with ENCRYPT_AES, and I know the encryption key, is it possible to decrypt that value external to the database, i.e., without using DECRYPT_CHAR? We're hitting an apparent bug where a particular column won't decrypt, but rather than giving a decryption error, it's mystifyingly giving error -202 (an illegal character was found in the statement). It's a single column in a single record in a table of 300,000. I'm able to isolate that value and insert it into a temp table and recreate the problem. I've replicated it in 12.10.FC14 and 14.10.FC6. I'm trying to figure out what's actually in the column (decrypted) to see if maybe there's an oddball character or something similar that's causing DECRYPT_CHAR to barf. If I can figure that out, I'd like to try to replicate the issue with a dummy encryption key; for obvious reasons, I don't want to send our actual encryption key in with the repro case. TIA, - TJG ------------------------------ TOM GIRSCH ------------------------------ #Informix

Hey Tom - is this possibly a case where the column storing the encrypted value is not large enough to store the whole string? If I remember, when this happens, you can still save the value, but can never read it back. I don't know whether this would produce a -202 error though. I tested this scenario a long time back, but don't remember the result! ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

That issue bit me, but this is different. I've gota check constraint to prevent that now. The column is 250 wide and the encrypted data is maybe 30 bytes long. Get Outlook for Android

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. David. ------------------------------ David Williams ------------------------------

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'd try copying the (encrypted) data into a dummy table with a char column just one byte smaller that what it currently is, then try the decrypt on that, maybe that would allow recovering of partial data at least. If not, try further narrowing the field, one byte at a time. ------------------------------ Andreas Legner ------------------------------

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.

I believe the idea is to keep remove characters from the end until you remove the bad characters and it decrypts ok. ------------------------------ David Williams ------------------------------

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."

Had missed that part about you column providing ample space for the encrypted string, sorry. I guess your encrypted data is 'readble', i.e. not causing that -202, and rather the decryption result would be causing the -202? Can you unload or display the encrypted data? The only way out I could think about is EILSEQ_COMPAT_MODE onconfig param set to 1, for ignoring *certain* instances of -202 - not sure this is one of them. You could enable this in a play Informix instance where you can copy the encrypted data to, then run the decrypt function on it. Maybe this would suppress the -202 and allow you access to the decrypted data. You could even play with DB_LOCALE, i.e. database codeset, there, so try this in different codesets since what would be an illegal character in one codeset might be legal and making sense in another. ------------------------------ Andreas Legner ------------------------------

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?

| view attached 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 ------------------------------ TOM GIRSCH ------------------------------