× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Jay

You've run into a strange beast. Here's what I think I know about this, after working with a company that does encryption.

In RPG, if you read the table using RLA, it is sorted using the encrypted value, not the original, decrypted value. The decrypted value is displayed, the order comes from the encrypted value.

This is different from SQL, which decrypts the value before doing the sort.

This will give very strange results in RPG. To avoid it, you can use embedded SQL to read the table.

I don't know why this was the design for RPG - it feels wrong-headed, but there it is.

HTH
Vern

On 5/7/2019 9:21 AM, Jay Vaughn wrote:
So we have developed an in-house at rest encryption solution for sensitive
columns using FieldProcs and QC3 crypto api's...

Though it has been a challenging feat, we are happy with where we are with
it and how it is going to serve us.

We have one strange "behavior" that I am hoping someone can explain to us.
And though we know the challenges that come with encrypted key fields and
masked key fields, we have overcome them and have made appropriate changes
in our application layer for such. Tested - works great.

Here is what is strange...

We apply the fieldproc to our column.
It attaches and encrypts all data.
IMMEDIATELY after this, if you access the column with an sql such as...
(for a userProfile with masking selected) .
select encryptedColumn
from encryptedTable
where encryptedColumn >= someValue
order by encryptedColumn

The results are NOT correct and in fact appears to "alter" the last 3
digits in the column with 0... Though when you decrypt, all data is
restored to its true value.

Now the solution we have found - though doesn't explain anything - is that
we include one more step after the 2 above steps (attaching and
encrypting) to lastly, call a pgm that uses RLA and accesses the index/LF
via the encrypted column and 1.) do a chain with loval to the index/lf and
then 2.) setll/read through all rows of the table. Then, upon executing
the above sql (for a masked user), the results show correctly. It sounds
like some kind of strange "initialization" process that is prep'ing the
encrypted key column data.

So... we have a solution... it works and is just another small step in
applying the FIELDPROC... but why is this necessary?
Based on our 2 year run at this, and lack of feedback we'd always get from
online on this topic I'm not counting on much... but being here at the end
and last "odd behavior" to be explained... i thought I'd see if any new
experiences in this area could shed some light.

Please save all the doom and gloom negativity about how this is for 3rd
parties only to roll (such as Townsend)... And that encryption is like
quicksand and it's going to suck us under. :) Because it hasn't.

TIA

Jay


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.