Encypt a columnm (XData/Aurelius)

BIZ TMS XData
1
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?
2

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;
begin
  Result := Decrypt(Freq_from);
end;


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

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

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

5

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.

6

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;

HTH,

1 Like
7

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?

8

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.

9

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
10

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.

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

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

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

var
  __Dic: TDicDictionary;

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

;

11

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.