Data encryption

[karlis]

Nope. No improvement. But strangly I can type "Vi" or "vi" and it will find Vitvita (and all names with that string). But it won't find "K?" or "k?" for K?rlis, though it will find "lis".

 

[troester]

This is exactly the behaviiour I described.
"But strangly I can type "Vi" or "vi" and it will find Vitvita": this is because it's finding the 2nd "vi" in Vitvita

In search all it won't find any string with a capital letter if you are searching for this letter (no matter if it's upper or lower case in the search string, the search string is always converted to lower before it's used).
You'll find K?rlis with ?rlis.

I assume it's a MySQL thing.
AES_DECRYPT is returning this HEX-string

If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons.

@cheesegrits
I think something like
WHERE CAST(AES_DECRYPT(`vorname_encrypted`,'xyz') AS CHAR(100)) LIKE '%K?r%'
should do.
In phpmyAdmin it's ok this way.

 

[karlis]

The system administrator of my provider here in Latvia said about encrypting that it can't be BLOB but VARBINARY. He said otherwise it will not encrypt correctly. Don't shoot the messenger.

 

[troester]

Encrypt/decrypt is working, it's only the case-insensitive search.
I just tested and can't see any difference if using varbinary.

The CAST AS has to be fixed in Fabrik.

 

[karlis]

Ok.

 

[cheesegrits]

OK, the problem is that it's doing ...

AES_DECRYPT(LOWER(`fab_main_test`.`name`), 'xxxxxxxxxxxxxxxx') regexp LOWER('???r??i')

Rather than ...

LOWER(AES_DECRYPT(`fab_main_test`.`name`, 'xxxxxxxxxxxxxxx')) regexp LOWER('???r??i')

I'll see if I can fix that, although I have a suspicion it's going to be a bear.

BTW ... as you may have noticed, there's a rather nasty security issue that needs resolving, which I've been trying to figure out how to do without a backwards compatibility break or losing data. We need to md5() hash the secret before using it, so it doesn't get exposed in things like debug output. For now, if you use this feature, make sure you turn debug off!

-- hugh

 

[cheesegrits]

Surprisingly enough, that wasn't too bad, and I think this should fix it:

https://github.com/Fabrik/fabrik/commit/c1d1217b831d1c9f44be43e608d5338b586b1ebe

As always I'm a little sketchy about encoding, but I think it's OK to hard wire the "USING utf8" into that ....

LOWER(CONVERT(AES_DECRYPT(foo, 'xxxxx') USING utf-8))

... as I'm pretty sure J! and Fabrik both require / assume a UTF-8 character set and encoding.

Without the CONVERT() it won't work.

-- hugh

 

[karlis]

THANK YOU!

 

[karlis]

Maybe its just me. Installed from github, but nothing's changed. Can't search on k?rlis or K?rlis, but can search on ?rlis. I can give someone access to the backend to see if I have it configured right.

 

[troester]

Search all is working now (@cheesegrits: element search is still case sensitive, was only for search all).
What do you mean with "Installed from gihub"?
You can't "install", you have to copy the files, see Updating from Github

 

[karlis]

I updated to the wrong folder. Its working now. Sorry.