MyTable questions

[DonnEdwards]

I’m trying to use the features of the CapeSoft MyTable templates in the “Learning Clarion” ABC application. I have added the required “EncryptionVersion” LONG variable to the Customer table and the Phone table.
In the Customer table I have doubled the length of the text fields, except state, since it’s a foreign key lookup field, and added in the External Names as follows: “Company | Encrypted(MyTable)” without the quotes. After creating a new application using the application wizard, I added in the following global extensions:

and I have set the secrets in the MyTables extension as shown in the image.

By changing to Category mode in the main application tree, I have selected “BrowseCustomer” and “SelectCustomer” and added in the MyTableLocalBrowse extension and set the secure tab values:

In the same manner I have added the MyTableLocalForm extension with the same values to the “UpdateCustomer” form.

When I run the application I can edit the data:

I put a dot at the end of each field and clicked OK.

Looking at the raw data file using topscan the address field did not encrypt, but the others did.

The browse form also shows the data as missing.

I tried adding a new customer record:

But again, some of the fields are lost once I save the record.

I’m too much of a newbie to figure out what is going wrong. Any suggestions?

Also, how can I automatically create the “sort key” value from the first 3 letters of the company name, instead of having to type it in?

Any advice, suggestions, etc would be most welcome.

[DonnEdwards]

Update: I tried changing the field sizes to 200, and it is now working.

Any suggestions on calculating the sort key would be greatly appreciated.

[Bruce]

Hi Donn,

You don’t mention how long the fields were before you made them longer, but typically encrypted fields need to be longer because they are stored as base64 - meaning only 3/4 of the string can be used by the app. So an address field of 200 chars is space for 150 actual characters and so on. You might want to consider the length of the other fields (and the picture on your entry form) with this in mind.

Also, how can I automatically create the “sort key” value from the first 3 letters of the company name, instead of having to type it in?

In The TakeCompleted method on the form, Priority 2800 or lower, do something like
cus:CompanyOrder = Sub(cus:Company,1,3)

[DonnEdwards]

Thanks Bruce. I came to much the same conclusion, assuming one character per byte:

AES256 has a 128-bit block size. If you want to encrypt a single byte, then MyTable adds its row salt to the data. If the salt is 8 bytes, then there are 9 bytes to encrypt. These are placed in a block of 128 bits, and the encrypted result is also 128 bits long, i.e. 16 bytes. Once encrypted, these 16 bytes are Base64 encoded, resulting in 24 bytes of data to be stored in the table field.

We are advised to use a 16-byte GUID as the salt, so encrypting 1 byte will result in Base64 output of 44 bytes, because the input data is 17 bytes, which takes two 128-bit blocks, and 32 encrypted byes output is encoded by Base64 as 44 bytes.

So the minimum practical length for a short text field of 16 characters (or less) is 44 bytes.

Assuming we are using a 16-byte salt, the maximum amount of field data you can store in a 255 character field is 160 characters. With the salt added, it becomes 11 blocks of 128 bytes, and the Base64 encoding results in 236 characters of output. So make sure your string fields are at least 236 characters big, and the field picture is no more than @s160 or you will get size errors.

I was assuming that doubling the length of 20 characters to 40 characters was more than enough, since Base64 takes 3 bytes and makes it 4. But I forgot about the 16 byte salt, and the 16 byte block size.

Also, when it comes to the secrets:

AES256 has a 256-bit encryption key. That’s 32 bytes. So the combination of Program Secret, Customer Secret and Table Secret should be 32 bytes. If you make each secret 32 bytes, then effectively there is only one secret, because AES256 will only ever use the first 32 bytes for its key.

So, rather set the program secret to, say, 12 bytes
Customer secret to 10 bytes, and Table secret to 12 bytes.

[DonnEdwards]

@Bruce

In The TakeCompleted method on the form, Priority 2800 or lower, do something like
cus:CompanyOrder = Sub(cus:Company,1,3)

StringTheory to the rescue! Thanks once again.

[Bruce]

cus:SortField = st.Sub(1,3)