Encypt a columnm (XData/Aurelius)

I want store some columns encrypted in the database.
I use REST with XData and Aurelius.
I thought that I use Speck for encodeing and decoding.
Where can I do this? In Aurelius? In XData?
Aurelius has OnInsert/Update events but I found nothing for reading a dataset. Or can I set encryption with mapping.
XData can handle OnEntityGet event. 
What is the best solution?

That's an open question. You want to encrypt only a few columns, or the whole record? You just want to save encrypted value in the database, and transfer (client/server) it in plain text? Or you want to transfer it encrypted and decrypt it at client side?

All those questions need to be answered. But here is a small solution if I'm guessing right what you need:

Tweak your entity class so that it persists the encrypted value, and then you decrypt/encrypt on the fly when you try to use it. For example:

Trequesttbl = class
  Freq_from: string; // the encrypted one which will be 
  property ReqFromPlain: string read GetReqFromPlain write SetReqFromPlain;

Then you encrypt/decrypt:

function Trequesttbl.GetReqFromPlain: string;
  Result := Decrypt(Freq_from);

procedure Trequesttbl.SetReqFromPlain(value: string);
  Freq_from := Encrypt(Value);

My goal is to have the data encrypted in the database. 
The transfer from Server to Client is separately. 
Here I have only the server side.
I could tranfer encrypted, but I think it is better to do this only on server side. (The transfer may have its own encryption)

I want to encrypt only some columns of a table. Not the whole dataset.
I have functions for Encrypt(string), Decrypt(string).

But from where can I call the function?
If a User calls  over REST GET https://server:port/myModule/usertable/42
Here, I want to get the dataset from usertable with ID=42 and one column is stored encrypted in the database. Here I want to get the decrypted data.

You can just use the approach I mentioned. You will only map (persist) the class fields that hold the encrypted value. 

Ihave changed the Class in the mapping unit, bat it doesn't work. If I call the table in browser I don't get the decoded values.

Using XData entities will read from the class Field by default that's why you are getting it encripted

You can use attributes [XDataExcludeProperty] and [XDataProperty] to change this behavior, eg:

Trequesttbl = class
  [XDataExcludeProperty] {xdata shoud not include this field when serializing this class}
  Freq_from: string; // the encrypted one which will be 
  [XDataProperty] {xdata should include this property when serializing this class}
  property ReqFromPlain: string read GetReqFromPlain write SetReqFromPlain;


1 Like

I coming back to developing apps after 15 years not doing them. Today is GDPR must taken serious when developing apps where data is stored in cloud. If i have understand right, all data related to persons should be encrypted at database. I haven't yet tried to use Decrypt/Encrypt function yet, but i guess data is stored at db encrypted when using them.

Been thinking situation in future where i have database full of encrypted data where encryption is made with XData encrypt function. Can i decrypt data without XData?

XData doesn't encrypt data. It provides you ways for you to encrypt data. So, if you encrypted it, you can somehow decrypt it. It's up to you.

Ok, i thought that these Encrypt/Decrypt function were built-in functions in XData. So need to make own functions and use TMS Cryptography Pack in these.

Thanks again.

1 Like

I tested this with RemoteDB, not XData. Should this work with RemoteDB also, i don't get modified records with this code. "longitude" shows in client without 'M' at the end of string.

  [Id('Fid', TIdGenerator.IdentityOrSequence)]
  Tlocations = class
    [Column('id', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Fid: Integer;

    [Column('longitude', [], 20)]
    Flongitude: string;

    //Encode function/procedure
    function GetencLongitude: string;
    property id: Integer read Fid write Fid;
    //Encoded field
    property longitude: string read GetencLongitude write SetencLongitude;

  __Dic: TDicDictionary;

function Tlocations.GetencLongitude : string;
  encoded : String;
  encoded := Flongitude + 'M';
  Result := encoded;


What do you mean by "shows in client", exactly? Use a different property name for the unencrypted value. I.e, keep Flongitude field and Longitude property the way they were, and create a new UnencryptedLongitude property. Or, rename the private FLongitude to FEncryptedLongitude.