View Only
Expand all | Collapse all



    IBM Champion
    Posted Fri September 03, 2021 06:27 PM
    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.


    - TJG



    IBM Champion
    Posted Fri September 03, 2021 06:37 PM
    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


    IBM Champion
    Posted Fri September 03, 2021 07:01 PM
    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.


    Posted Sat September 11, 2021 11:01 AM


    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 Williams


    IBM Champion
    Posted Mon September 13, 2021 05:44 PM

    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.


    Posted Tue September 14, 2021 11:27 AM
    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


    IBM Champion
    Posted Tue September 14, 2021 11:52 AM

    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.


    Posted Tue September 14, 2021 06:34 PM

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

    David Williams


    IBM Champion
    Posted Wed September 15, 2021 09:11 AM

    Won't that mess up a checksum?


    IBM Champion
    Posted Wed September 15, 2021 09:15 AM

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


    Posted Wed September 15, 2021 10:22 AM
    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


    IBM Champion
    Posted Wed September 15, 2021 10:28 AM

    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.



    IBM Champion
    Posted Wed September 15, 2021 10:37 AM

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



    IBM Champion
    Posted Wed September 15, 2021 10:56 AM

    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?



    IBM Champion
    Posted Thu September 23, 2021 12:32 PM
      |   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 (

     INSERT using table [ tjg_kaboom ]

       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    



    zip   743 B 1 version