Informix

Expand all | Collapse all

ENCRYPT_AES/DECRYPT_CHAR

  • 1.  ENCRYPT_AES/DECRYPT_CHAR

    Posted 18 days ago
    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
    ------------------------------


  • 2.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 18 days ago
    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
    ------------------------------



  • 3.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 18 days ago
    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.






  • 4.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 10 days ago


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



  • 5.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 8 days ago

    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.






  • 6.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 7 days ago
    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
    ------------------------------



  • 7.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 7 days ago

    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.






  • 8.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 7 days ago

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

    ------------------------------
    David Williams
    ------------------------------



  • 9.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago

    Won't that mess up a checksum?






  • 10.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago

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






  • 11.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago
    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
    ------------------------------



  • 12.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago

    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.

     






  • 13.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago

    If it's illegal in en_US.utf8, what codeset might it be legal in?

     






  • 14.  RE: ENCRYPT_AES/DECRYPT_CHAR

    Posted 6 days ago

    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?