Sorting xBrowse on an encrypted ADO table

[Rick Lipkin]

To All

I have encrypted a MS Access Employee table and in order to show the names in xBrowse I have to ADD the columns passing my Dencrypt() function to the row.

I have no problem with doing that and my results are acceptable, however, I can not sort the data in the browse because the underlying data is encrypted.

Instead of adding the Sort to the data .. is there a way to do something
like oLbx2:Sort( "Name" ) ?

Thanks
Rick Lipkin

Here is the code:

Code: Select all  Expand view

oRsVendor := TOleAuto():New( "ADODB.Recordset" )
oRsVendor:CursorType     := 1        // opendkeyset
oRsVendor:CursorLocation := 3        // local cache
oRsVendor:LockType       := 3        // lockoportunistic

cSQL := "SELECT * From Avendor Order by Lname,Fname"

TRY
   oRsVendor:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening AVENDOR table" )
   RETURN(.F.)
END TRY

If oRsVendor:eof
Else
   oRsVendor:MoveFirst()
   oRsVendor:Find( "Lname Like '"+alltrim(cLname)+"%'" )

   If oRsVendor:eof
      oRsVendor:MoveFirst()
   Endif
Endif

lOk2   := .f.
xTITLE := "Emp Browse "//+xDIST

DEFINE WINDOW oWndChild2 MDICHILD        ;
       FROM 7,7 to 28,55                 ;
       MENU BuildMenu()                  ;
       NOSYSMENU                         ;
       NOMINIMIZE                        ;
       NOZOOM                            ;
       OF oWind                          ;
       TITLE xTITLE

   DEFINE DIALOG oDlg2 RESOURCE "EMPBROW" OF oWndChild2 ;
          COLOR RGB(192,192,192), RGB(62,104,130)

   REDEFINE xBROWSE oLBX2               ;
       RECORDSET oRsVendor              ;
       ID 111 of oDlg2                  ;
       AUTOSORT AUTOCOLS LINES CELL

   oLbx2:lHScroll := .f. // turn off horiz scroll bar
   oLbx2:lRecordSelector := .f.

   oLbx2:nMarqueeStyle   := MARQSTYLE_HIGHLROW

   ADD oCol TO oLbx2 AT 1 DATA {|x| x := _ChkName(Dencrypt(oRsVendor:Fields("Lname"):Value),;
                                                  Dencrypt(oRsVendor:Fields("Fname"):Value),;
                                                  Dencrypt(oRsVendor:Fields("Mname"):Value))};
                                                  HEADER "Name" size 200 ;
                                                  Sort( "Lname")

   ADD oCol TO oLbx2 AT 2 DATA {|x| x := _ChkActive(Dencrypt(oRsVendor:Fields("Active"):Value))};
                                                   HEADER "Active" size 165


...

//----------------------------
Static Func _ChkName( cLast,cFirst,cMiddle )

Local cName

cName := substr("Unk"+space(45),1,45)

cName := substr(alltrim(cLast)+", "+alltrim(cFirst)+" "+;
               alltrim(cMiddle)+space(45),1,45)

Return( cName )


 

Here is the browse:


Here is what the data looks like encrypted:

[nageswaragunupudi]

1. Please check if the Encrypted values are also in the same sort order as the Decrypted values. If so, you can use oCol:cSortOrder := "LNAME,FNAME,MNAME"

2. (a) It is possible to assign a codeblock to oCol:cSortOrder
Example: oCol:cSortOrder := { |oCol| MySortFunc( oCol ) }
Inside the MySortFunc(oCol) you can sort the recordset as you want.

(b) Though xbrowse provides the maximum possible flexibility to sort the data source, your real problem is to sort the RecordSet in the order of decrypted values. You can sort a RecordSet only on the basis of a fieldname or combination of fieldnames. The decrypted values are not known the ADO and so can not be used to sort the RecordSet.

3. If you have the decryption function available in SQL, then you may read the data using the sql function inside the query.

4. I would suggest you make an array of decrypted table from the recordset and browse the array instead.

5. Academically, there is another way, which I do not recommend:
Read the recordset with batch locking ( adLockBatchOptimistic ). Then Detach the recordset ( important ) so that by mistake we do not save the modifications. Replace the fname,lname,mname with the decrypted values. Override oBrw:bSave := { || nil }. Then browse the recordset. Use oCol:cSortOrder := 'LNAME,FNAME,LNAME'.
This is possible but it is far more easier to derive an array and browse the array.

[Rick Lipkin]

Rao

Thank you for your insight .. I thought about this issue most of the night. As you mentioned .. I was concerned that xBrowse would not know the decrypted data at run-time .. so I thought of a couple of work arounds ..

1) Leave just the Last name un-encrypted .. and that works, however that leaves the last name visible to Access and ( yes ) you could make the case that addresses and identifying numbers are encrypted so therefore the personal information is secure .. perhaps

2) Another un-intended side affect is that the Employee find routine will only work properly if you search the entire last name for a value .. you can not just find a partial name and dencrypt Like % at run-time.

I did not think of copying and decrypting the three of four fields to an array .. I am glad you suggested that option.. that does solve all my problems at a cost of some run-time wait .. I will go down that path and see where it takes me.

Thank you again for your suggestions!
Rick Lipkin