Sc_decode - lookup request ((urgent))

Developers Programming
#1

Hello ALL,

I have the following SQL within the staff_id field lookup (AUTOMATIC)

SELECT people_id, sc_concat(firstname, ’ ', lastname)
FROM people
ORDER BY firstname, lastname

The fields firstname & lastname are encrypted using SC_ENCODE.

I’m trying to display the results within the lookup, but as you can imagine they are displayed encrypted.

HOW CAN I DISPLAY THE VALUE DECRYPTED?

I have tried many things but to no avail.

#2

Did you try with sc_decode?

#3

Hello Alvagar,

Yes, I tried with sc_decode.
The problem is within the SQL it doesn’t work.

eg
SELECT people_id, sc_concat(sc_decode(‘firstname’),’ ', lastname)
FROM op_people
ORDER BY firstname, lastname

Or any other version of sc_decode(firstname)

#4

I think that macro sc_encode and sc_decode does not work into SQL. Just works into events for scriptcase.

#5

So how do I setup a dropdown lookup and decode the field data?

The senario is as follows:

Table1 has a staff_id (foreign-key)

Table2 has the staff_id (primary-key), including the firstname and lastname fields. These fields are encrypted. (Not staff_id)

Within the app that’s related to Table1, I want to display the concat(firstname,’ ',lastname) via the autolookup feature within the field parameters associated with staff_id.

It works well, but obviously it is displaying the encrypted form of firstname and lastname.

So how do I get the decrypted version of the fields?

#6

I think the only solutions are:

  1. permanentlyly store e decrypted version of the crypted fields (additional fields in the same table or different table or in a different DB) at every record insert/update of Table2. Then use these decrypted versions in the lookup SQL statement of the Table1 app. But I guess you are storing encrypted data in the DB for security reasons so this would break your security policy.

  2. in the Table1 app, decrypt the fields at the onload events (or one of the earlier events) and store them unencrypted in a temporary SQL table. Then access the temporary table in the lookup SQL statement. You have to check what kind of temp tables your DB provide and if they match the security requirements you have. Not sure this solution is feasible: maybe the persistence of the temp table could be an issue.

  3. build you own form element via JavaScript and/or jQuery and populate it via PHP where you can use the SC macros
    ​​​

  4. buy a software that can encrypt/decrypt on the fly and transparently from the DB and your code. These are usually very expensive enterprise software and must rune iin dedicated machines (at least the one I know a little about)

#7

Thanks for your responses. I appreciate it.
However, none of the solutions work for me.

How do we go about informing Scriptcase of this situation. It seems to me that this should be a standard practice.

[SIZE=24px]Does anyone else have a solution?[/SIZE]

#8

Anoher possible solution could be to check if your DB has any encrypt\decrypt functions that can be used in SQL statements.
If so, you could quit using the SC macros and use SQL to encrypt and decrypt the data.
MySQL seems to have them:

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html

Failing that,I wouldn’t count on NetMake implementing any such change as they are not responsive at all to requests\suggestions made on this forum.