Using Encrypted Data

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

So, I'm trying to store data in a particular field in the database in an encrypted manner, to prevent our report writer (crystal reports) from being able to report on it. I have written an encrypt and decrypt proc (cp_passwd) as per below, but I have two problems 1. From the documentation, it seems that to store data in a varchar I should be using the USTRING stuff to ensure there are no embedded nulls, but adding it in causes the whole thing to fail. 2. Putting this code <$fieldname>/init = cp_passwd("DECODE",<$fieldname>) in the decrypt trigger of the field, causes the data file to lock almost as soon as read (paranoid locking), I need it to stay as cautious locking (as is set) so is there any way to make the decryption not count as modifying the field?


returns string params string p_type : IN string p_inparams : IN endparams variables raw v_rawpasswd string v_inparams endvariables selectcase $uppercase(p_type) case "DECODE" ; v_rawpasswd = $decode("USTRING",p_inparams) v_rawpasswd = p_inparams v_inparams = $decode("BLOWFISH", v_rawpasswd,"My encryption key") case "ENCODE" v_rawpasswd = $encode("BLOWFISH", p_inparams,"My encryption key") ; v_inparams = $encode("USTRING",v_rawpasswd) v_inparams = v_rawpasswd endselectcase return v_inparams end


                
        
    
		

5 Comments

  1. To answer your point 2: Alternative A: The /init switch only has effect when assigning values to empty occurrences. You are modifying a value of a database field that already has been read. This indeed invokes the Lock trigger. You could add a non-database field to your model and put the result of the decryption into that. In the encrypt trigger you then encrypt the non-database field to the database field. That will give you optimistic locking for modifications of this field. Alternative B: If you do not need to do anything special with the field value in Proc code (just retrieve, user modifies, store) then you might consider to use the Format and Deformat triggers instead of Encrypt/Decrypt. You would not need a non-database field this way. Happy New Year, Theo Neeskens NB, from Ulibrary: /init—sets the value of the target field in a non-database occurrence without changing the status of $fieldmod, $occmod, $occdbmod, $formmod, $formdbmod, $instancemod, or $instancedbmod. The /init switch is useful when you are assigning a retrieve profile for an empty entity or initializing values in a new occurrence. This switch has no effect once a retrieve has been completed.


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. For point 2 I just had the same issue recently. It turned out that assigning the field in 3GL does not fire the Lock trigger. Probably the Encrypt/Decrypt triggers were designed with 3GL in mind as the $encode/$decode stuff did not exist yet.


    Author: dleveque (david.leveque@vision4health.be)
  3. Iain, First of all: a Happy New Year!

    FiresongKt said 1. From the documentation, it seems that to store data in a varchar I should be using the USTRING stuff to ensure there are no embedded nulls, but adding it in causes the whole thing to fail.

    In case the encoded data includes NULLS and you would like to store it in a varchar field then you need an additional encode step using either the BASE64, HEX or URL encoding scheme. This should make sure that everything is stored correctly.

    
    
    case "DECODE" v_rawpasswd = $decode("HEX",p_inparams) v_inparams = $decode("BLOWFISH", v_rawpasswd,"My encryption key") case "ENCODE" v_rawpasswd = $encode("BLOWFISH", p_inparams,"My encryption key") v_inparams = $encode("HEX",v_rawpasswd)
    
    

    And please make sue that you have enough space to store the additional data (a HEX encoded string is about twice as big as the original string). Hope this helps. Kind regards, Daniel


    Author: diseli (daniel.iseli@uniface.com)
  4. Hi Daniel, And a happy new year back. I have added the hex encoding to the password proc and this does appear to work where the USTRING encoding didn't. It's just a shame the documentation for this code is wrong in both this, and where it puts the encoding and decoding (the documentation encodes to blowfish, decodes from blowfish and then encodes to ustring, to be used properly it needs to provide a string safe version between the encoding using blowfish and the decoding (as per the stuff above). Hi Theo, Yes, I've put a non-database field in the model, which is then to all intents and purposes, the only one I use to work with, and the encrypt and decrypt trigger of the database field populate and draw from this non-database field. The only issue of this will be that it won't set $occdbmod if the (Non-database) password field is the only one modified. But I think I can live with that.


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  5. Hi Iain,

    FiresongKt said It's just a shame the documentation for this code is wrong in both this, and where it puts the encoding and decoding (the documentation encodes to blowfish, decodes from blowfish and then encodes to ustring, to be used properly it needs to provide a string safe version between the encoding using blowfish and the decoding (as per the stuff above).

    The documentation is actually correct. The value returned by $decode is raw data and in order to get a proper string you still have to encode the return value as USTRING. When encoding we do not need this extra step since the HEX encoding will just return numbers and letters (i.e. HEX values). So the decode code should actually look like this:

    case "DECODE" v_rawpasswd = $decode("HEX",p_inparams) v_rawpasswd = $decode("BLOWFISH", v_rawpasswd,"My encryption key") v_inparams = $encode("USTRING", v_rawpasswd) case "ENCODE" v_rawpasswd = $encode("BLOWFISH", p_inparams,"My encryption key") v_inparams = $encode("HEX",v_rawpasswd)

    Sorry for the confusion. Hope this helps. Regards, Daniel


    Author: diseli (daniel.iseli@uniface.com)