Encrypt just 1 field?

[Raymond Lyons]

Other than the method in one of Peter Wayne's early books, does anyone have a way of encypting just one field that they can share? There would still have to be way for the user to do queries on the field, e.g. the number 123 (actually >123) looks for the encrypted value of @%# or greater. In my current case I am only interested in a numerical field. Also, I would need to be able run an update that would transform a field's normal values (e.g., 565) into the other encrypted field (e.g., @#&). Of course I would much prefer some translation scheme that would be relatively difficult to figure out)

FYI, I once had some kind of problem (do not remember what) using Peter's method in his book, so I do not want to revisit that.

Ray

[JerryBrightbill]

RE: Encrypt just 1 field?

Ray

There is a method to encrypt just one field, but it does require a little work. In older versions of A5 before table encryption, I had a database that stored social security numbers which obviously must be secure. The solution was to create my own encryption algorithm and encrypt the ss numbers in a second field. Now A5V5 gives you an encryption method with the functions encrypt_string() and decrypt_string()

Here is how it worked. When the database was opened, a script ran an update on the table. There were two fields in the table for ss number, one encrypted, one not. The update filled in the non-encrypted field by decrypting the encrypted field. The encrypted field was actually a calculated field, so whenever the ss number was entered or edited, the encrypted field immediately changed. Anytime the database was exited, the non-encypted field was blanked out with an update operation, which was very fast. If someone looked at the dbf with any viewer, all they could see was the encrypted field, which was jibberish. The non-encrypted field was blank.

Jerry

[Peter.Greulich]

RE: Encrypt just 1 field?

You can also use the CanWrite event and the a_field_value system variable to trigger the encryption.

Character Encrypted_String = ENCRYPT_STRING( Input_String as C [, Key as C ] )

? encrypt_string("Alpha Software")
= "$Z)kd*ky+B#1@"
? encrypt_string("Alpha Software", "password")
= ">jYM?_Po?w'js5"

? decrypt_string("$Z)kd*ky+B#1@")
= "Alpha Software"
? decrypt_string(">jYM?_Po?w'js5", "password")
= "Alpha Software"

[Raymond Lyons]

RE: Encrypt just 1 field?

OK and however:

For anyone interested, it turns out that for use in updates and queries I had to build encryption and decryption functions, ala:

FUNCTION C_encrypt AS C (Input AS C )
dim s as c
s= encrypt_string(input,"memomma")
C_encrypt = s
END FUNCTION

and similarly for decryption.

In my case the field in question is never input or changed; it gets imported when the table is originally created from an ASCII file. I not only do not want the user to be able to see the value of the decrypted field (I delete the unencrypted field after the update that creates the encrypted value), I do not want a savy A5 user to see it using the the decrypt function. BUT, I need this guy to be able to create his own queries using the encrypted field, something like: C_Decrypt(fld2,"memomma")>"600".

Of course using a key means only this guy could write the query, but it also means he could create his own calculated field on his own little form and be able to see anyone's very private data (similar to a social security number). The question is, is there any way to stop the latter sort of thing? Contractually, by the way, I am allowed to see this private data, but I am not allowed to let anyone else see it. And yes, even allowing a query at all could in itself pose a problem. Any ideas?

Ray

[JerryBrightbill]

RE: Encrypt just 1 field?

Ray

Unless you restrict every access that uses the decrypted number, you can't prevent access. The only solution I see is to find out ahead of time what queries they may need and hard code as many as possible.

Jerry

[Raymond Lyons]

RE: Encrypt just 1 field?

Jerry,

I think you are correct. The client wants his computer savvy user to be able to do the queries but if he becomes savvy enough with A5 he could figure out how to decrypt everything. I am going to have to assess the possible legal implications of making this possible.

Thanks,

Ray

[Bo Vandenberg]

RE: Encrypt just 1 field?

Would it help to move the data out of the table structure?

Externalize just the high security fields and then acces them over some encrypted process?

Bo

[Raymond Lyons]

RE: Encrypt just 1 field?

Bo,

I am not sure exactly what you mean, but I don't see how it would help. We are talking about 400,000+ records. The field could be in a separate table, but how would that stop a savvy user from figuring out how to use the function that decrypts for queries to look at data for an individual? Casual users would never figure it out but you and I could, not to mention many people on this board.

Ray

[JerryBrightbill]

RE: Encrypt just 1 field?

Ray

After rereading your post, any query on an encrypted field could reveal the data in the field. For example, if you are looking for a ss number, but don't have access to the encrypted field, you could design your own query to search for a ss number between 555-55-5555 and 555-55-5557. By continually narrowing the query, you could return any number, without knowing exactly what is in the field. If the field must be secure, then you can't really run any query. The only way any data can be secure is severly limit any access to it.

You could easily run a search for a single value. I use encrypted passwords in a number of application. I don't decrypt the actual record, but encrypt the value I am using for the find using the same algorithm as the data stored in the field. For example, if the saved password is "password", I may have used the following to save a value in field in the password table.

encrypt_string("password","alpha")
returns "i#])B=!T"

Now when the user logs on using ui_get_password() or something similar, I take the password they enter, trim it, encrypt it, and search for that value

user_password = "password "
?encrypt_string(alltrim("password "),"alpha")
= "i#])B=!T"

I index the field and search for "i#])B=!T". Of course, trial and error still might eventually find the record anyway. Just how secure must this be and why is this "savvy expert" being granted access?

Jerry

[Peter.Greulich]

RE: Encrypt just 1 field?

I don't follow. If the user can create a query, can't he see everyone's data anyway?