There is no value in included the encrypted field in the index for anything other than exact matches. Since it is not the actual value, and the collation of the encrypted value should not match the collation of the unencrypted value. If it did, then the encryption would be predictable, and therefore of little value. It is not going to speed up the retrieval, and would probably slow it down since it would always require a full travel of that level to evaluate all the encrypted values. The only way this could be corrected would be to store the value in the subscript level in the clear, but that would eliminate the value of having it encrypted in the first place. Mark
"Nick Hershberger" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The app is still being developed. I've been using AESEncode and AESDecode > with no problems, at least none that I've noticed. The trouble is getting > the SQL compiler to recognize that some of the subscripts of the index > global are actually encrypted data and to execute code accordingly to decode > those subscript values so that operators other than equality operators can > be used against those values. > > Nick Hershberger > > "Peter Lowndes" <peter dot lowndes at safewire dot net> wrote in message > news:[EMAIL PROTECTED] > > Are you stuck with the data that is already encoded or is the application > > still being developed? I had problems with the encrypted format until I > > changed my encryption method to wrap the AES stuff in the > > Base64Encode/Decode methods, also part of the $System.Encryption class. > This > > gave me much more convenient / readable encrypted versions that were ok > for > > data and subscripts. > > > > e.g. > > $System.Encryption.Base64Encode($System.Encryption.AESEncode(Text,Key)) > > and > > > $p($System.Encryption.AESDecode($System.Encryption.Base64Decode(EncryptedTex > > t,Key),$c(0)) > > > > The $p(...,$c(0)) was to get rid of trailing ascii zeroes that occurred > when > > the original text was less than 16 characters long. > > > > Peter Lowndes > > Safewire Ltd. > > > > "Nick Hershberger" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > > > I've got an encrypted field that is used as one of the subscripts in a > > > multi-field index, with the structure for the index being > > ^UICREDIT(field1, > > > field2, record id) with field 2 being the encrypted field. The data has > > > been encrypted using the AESEncode method from the $system.Encryption > > class. > > > Specifying retrieval code for this field works for getting the data from > > > disk when a query is executed, however, I've run into problems with > > indexes > > > that use encrypted fields. The retrieval code does not work when doing > > > traversals on the index. Intersystems suggested that I write a > > user-defined > > > data type for encrypted fields. I've tried that, creating a rough > > Encrypted > > > data type class that has a StorageToLogical and LogicalToStorage method. > > > The StorageToLogical method gets generated into the compiled SQL if the > > > field is mapped as Encrypted. However, it still isn't being used for > the > > > encrypted subscript. > > > > > > Since that didn't work, I modified the SQL Storage map for the index > > global > > > in Studio, specifying the access type for the encrypted subscript as > Sub, > > > with an expression of: > > ##class(arch.system.Encrypted).FieldDecryption(Data). > > > This does get generated into the global when it is referenced in the > > > compiled SQL: > > > > > > s > > > > > > %cur123850d(1)=$o(^UICREDIT(%cur123850d(4),##class(arch.system.Encrypted).Fi > > > eldDecryption(Data),%cur123850d(1))) > > > > > > However, if instead of data I pass in {L2} for the subscript level the > > class > > > won't compile. If I use the field's SqlFieldName, {AUCREDIT} then the > > class > > > method call is not added to the compiled SQL. > > > > > > Does anyone have any thoughts on this, or any experience with writing > data > > > type classes, or with using the $system.Encryption class? Any help > would > > be > > > greatly appreciated. Thanks. > > > > > > Nick Hershberger > > > > > > > > > > > >
