• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Data encryption

Status
Not open for further replies.

karlis

Member
Hello!
I don't seem to understand how the "encrypt data" under element/access seems to work. When I enable it for an element, it is no longer visible in the list view. Is this correct?
My db has personal information and by Latvian law must be encrypted.
 
It's working fine on my site.
Surely it doesn't work for existing records (because the unencrypted stored value will be interpreted as encrypted)
 
As Troester says, you can't just turn on encryption for existing data. Fabrik then expects the data to be encrypted when reading. Encryption in MySQL is not "seamless", it happens at the query level, so the application querying the data has to tell MySQL to encrypt or decrypt. You would usually set encryption before entering the data.

(Note that we do actually have code in Fabrik to encrypt/decrypt columns of data when switching encryption on and off, but for various reasons we disabled it - I may re-enable it in the next version of Fabrik)

So if you have existing data, you'll have to manually encrypt it.

First look at your ./configuration.php file in Joomla, and find the $secret, which will look something like 'XaqIyOcuDjcR6w4F' (a string of 16 random characters).

Then BACK UP YOUR DATABASE. Or at least the table you want to work on. If you don't backup, and you mess this step up, you lose your data.

Then MAKE SURE YOU BACKED UP YOUR DATA.

Then triple check that you BACKED UP YOUR DATA.

Then, for each of the elements you want to encrypt:

In Fabrik, enable encryption for the element. When you save, Fabrik will ask you to confirm changing the field to a "BLOB". Say Yes.

Then in phpMyAdmin (or whatever database client you use) run this:

Code:
UPDATE yourtable SET yourfield = AES_ENCRYPT(yourfield, 'XaqIyOcuDjcR6w4F');

Substitute your table and field names as appropriate. Be very careful that you get the secret correct, surrounded by single quotes.

Test that it worked by running this:

Code:
SELECT AES_DECRYPT(yourfield, 'XaqIyOcuDjcR6w4F') FROM yourtable;

... which should output the plain text (unencrypted) field values.

Repeat for each field you need to encrypt.

-- hugh
 
Last edited:
$secret may be also 32 characters (depending on the Joomla version used for the 1st installation?)
 
Thanks a lot. I'll be very careful on this step. I've lost enough data over the years. I'll make sure to back up my data, then back up my data, and check that I backed up my data. :p
 
Just FYI, I tested the steps I gave you, and it works. The most critical step (other than backing up, and "checking it twice") is setting the element to encrypted in Fabrik BEFORE you encrypt the data, so the field is changed to a "BLOB". If you don't do that first, and do the encryption while the field is still a VARCHAR, the encrypted data will be corrupted (it's binary data that won't store in a text field).

-- hugh
 
O oh... While setting a field to encrypt, saving, changing from varchar(30) to BLOB I get this error:
An error has occurred.
1170 BLOB/TEXT column 'uzvards' used in key specification without a key length.


All the other fields worked fine. Just this one (Surname) gliched. :eek:
 
Last edited:
Hmmm. Did you have a filter set on that?

I thought we did the correct thing and wouldn't create an index if it's a blob, but I guess we don't check to see if there's an index prior to changing to blob.

Normally we automagically create an index if you set an element with a filter, but for encrypted fields an index won't do any good.

-- hugh
 
I imported the db from MySQL workbench where I had converted from MS Access. Many of the indices were already in place before Fabrik.
BTW I don't know if it is an issue with encryption and Warp Themework, but once I encrypted the group buttons and javascripts didn't work anymore. Since then I've gone back to unencrypted data (there were other issues I hadn't thought of, like can't sort or search on encrypted fields), until I can think of a better way. Anyway, still plugging away... Have a nice day. :)
 
Hmmm, I though searching would work (except the BOOLEAN full text stuff). So basic element filtering should work, as we apply the AES_DECRYPT() in the where clause, like ...

WHERE AES_DECRYPT(name, 'secretkey') = 'Fred'

It does of course slow things down, as MySQL has to then decrypt every field in the selected set.

That said, I haven't actually tested any of this for a long time - kind of remiss of me, but I haven't set any of my standard test forms to use any encrypted fields for a few years. I've now set a couple of fields to encrypted, and will keep an eye on how they behave, and fix any issues I run across.

-- hugh
 
I'm using the Latvian characters from the utf8_general_ci collation. ?????? etc. When I did the AES_DECRYPT from Myphpadmin, those entries with such diacrytical marks returned some sort of jumble of characters about 8 long. (my secret is 32). But the entries were fine in Fabrik.
 
Thanks for the info. Maybe it was because the search filters were in place before the encryption?
BTW you could try a phrase that has more diacritical markings, such as "???r??i ??last?bai", which is a mouth-full for even native speakers. :eek:
 
Last edited:
Did the steps just as you said and still can't search on encrypted field. What's more is that in phpMyAdmin the names with diacritical markings show up as ciphers, as per screenshots (decrypt). Vivita1 shows that the encrypted field is visible. Vivita2 shows that when searching, no records are found. What am I doing wrong???
 

Attachments

  • decrypt.png
    decrypt.png
    193.3 KB · Views: 32
  • vivita1.png
    vivita1.png
    158.9 KB · Views: 32
  • vivita2.png
    vivita2.png
    151.1 KB · Views: 29
I assume you have this element included in the "search all" elements in list settings?

I can replicate:
The search all on encrypted elements is not working for capital letters (try searching for ivita -this should show the record).
If you enable the field search (Type field, exact=no) the search is case sensitive.

So with two records with "Vivita" and "vivita"
seach all will find "vivita" (no matter if the search string is "Viv" or "viv", it's converted to lower before searching)
field search will only find the record with the exact lower/upper character.

So it seems there's some "tolower" missing after the decrypt.

But diacritical is working fine (beside of this capital letter issue).

BTW:
AES_DECRYPT is showing all characters in hex in my DB
upload_2017-12-8_9-54-42.png upload_2017-12-8_9-55-48.png
 
Last edited:
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Staff online

Members online

Back
Top