Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Red face Unanswered: User Defined Functions and OLE

    Hi everyone

    I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.

    I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.

    This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.

    Are you with me so far?

    So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.

    I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query
    eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.

    It works, I get the results that I want.

    But, alas, the performance has taken a search severe hit.

    Has anyone else ventured down this road?

    Is there a better way of doing this?

    Thanks

    Ray

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've never heard of an encryption algorithm that employs a different key each time it is used.
    Sorry, but you are screwed on this, as far as performance goes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2006
    Posts
    7
    Blindman

    Tell you what man. Write yourself a CAPICOM testing web page, check how it works. You will see that the output from the Encrypt method changes everytime you encrypt the same string so that no two encryptions are the same. There is obviously a very good reason for this from a security viewpoint.

    The encryption key and algorithm detail is stored in the decrypted data - so that the CAPICOM Decrypt method only needs the original password to decrypt.

    When you've done that then if you have anything constructive to offer I would be pleased to discuss it. In the meantime, you might want to think before making everyone here aware of your ignorance.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Ummmm ... lets see

    Blindman ... posts = 5000+ ... customers served ... thousands+ ... no words of encouragement and an observation that given the parameters RayPooley presented, there will be no joy in mudville

    RayPooley ... posts = 3 ... customers served ... 0 (zero) ... slams Blindman for no words of encouragement. Shows ignorance and intolerance by lashing out at the one person who takes the time to respond.

    My thoughts ... RayPooley, why don't you take your attitude and go lurk someplace else. With that attitude, you are not welcome here and have nothing to contribute.

    If you have no way to reencrypt the input string in the same manner as the original, of course you will have to decrypt the original ... and not gain any performance in the operation.

    There ... you have your answer. Nothing to see here. Move along!


    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by RayPooley
    Tell you what man. Write yourself a CAPICOM testing web page, check how it works. You will see that the output from the Encrypt method changes everytime you encrypt the same string so that no two encryptions are the same.
    I don't recall questioning the veracity of your statement...
    Quote Originally Posted by RayPooley
    There is obviously a very good reason for this from a security viewpoint.
    Uhm....no, there is not. Plenty of secure encryption algorithms are deterministic. http://csrc.nist.gov/cryptval/aes/aesval.html
    Quote Originally Posted by RayPooley
    The encryption key and algorithm detail is stored in the decrypted data
    And this is supposed to make it MORE secure? Whatever. I'm not blaming you for writing the encryption, (unless of course you did write the encryption, which would explain you sensitivity to the issue...).

    Quote Originally Posted by RayPooley
    When you've done that then if you have anything constructive to offer I would be pleased to discuss it. In the meantime, you might want to think before making everyone here aware of your ignorance.
    Yes, one of these days I should get around to offering some constructive advice on this forum. But until that time....you are still screwed, and for some reason that doesn't make me feel bad.
    Last edited by blindman; 08-18-06 at 13:13.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2006
    Posts
    7
    Tom/Blindman

    Yes - my mistake. I thought I would be dealing with grown ups rather than guys who measure their computing prowess by the number of postings on a forum (5000 hey Tom? That IS a big number it's true but all I can say is that if they are all of the standard I have seen so far then what a waste of keystrokes!! But still, 5000 is cool!!)

    CAPICOM is a MS encryption module. If you knew anything about encryption you wouldn't need me to point that out. Its not the only one though.

    So I didn't write it. In fact the encryption that I am replacing was written by me and conforms to FIPS. But the client wants CAPICOM because it was written by Microsoft and, as you know, NOBODY writes code like Microsoft so I don't have a choice.

    Time to move on as you say chaps and leave this forum to the chattering (or posting) classes.

  7. #7
    Join Date
    Jul 2006
    Posts
    87

    Talking

    Quote Originally Posted by blindman
    Yes, one of these days I should get around to offering some constructive advice on this forum. But until that time....you are still screwed, and for some reason that doesn't make me feel bad.
    Priceless!

    BTW, this guy likes to start threads and then rip people. Here is a classic from january of last year.

    http://www.programmersresource.com/f...hp/t-2102.html

    I can't prove it is the same guy, but the MO is suspicious. Start a thread, rip the responders, disappear.
    Last edited by Code Carpenter; 08-18-06 at 15:17.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by RayPooley
    Time to move on as you say chaps and leave this forum to the chattering (or posting) classes.
    Your absence will leave an empty hollow space on dbforums.
    Hey guys! Let's fill it up with beer!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sadly, the empty hollow space left by that nitwit will not hold much beer for us.
    Paul

  10. #10
    Join Date
    Aug 2006
    Location
    Tornado Alley of Texas
    Posts
    45

    RE: Post 5

    Blindman,

    You the man, I was LMAO, I loved it.

    Tom,

    That was perfectly stated, this is why I love this forum. I've been on several but this one is outstanding and I'm learning so much, as well as the humor and quick wit. I hope I'm welcome here, you have so much to teach me and so little time.

    TTFN
    One can never stop learning if they open their eyes every morning with optimism and hope...Bill R. Loy
    I have a 8 gigabyte mind in a 80 gigabyte world something is bound to be forgotten as I age serenely into senilaity....

    Morath
    No matter what, the earth keeps spinning.

  11. #11
    Join Date
    Aug 2006
    Location
    Tornado Alley of Texas
    Posts
    45

    Thumbs down RE: RayPooley

    Quote Originally Posted by RayPooley
    Tom/Blindman


    Time to move on as you say chaps and leave this forum to the chattering (or posting) classes.

    Then move along, don't let the door hit you on the way out, you have no idea the intelligence of Blindman and Tom and the rest. I leave you with a quote:

    "Sometimes it's better to keep your mouth closed and let the world assume you a fool, then to open your mouth and remove all doubt"


    Unfortuntly, some people like you can't restrain themselves and pop off any way. Do you ever have an unexpressed thought? Microsoft forum allows "people" like you to expose their stupidity why don't you post your "opinion" there???? Ask for Margolotta, I think you two would make quite a pair.

    Bye, little man, I'm sure the rest of us won't miss you a bit
    One can never stop learning if they open their eyes every morning with optimism and hope...Bill R. Loy
    I have a 8 gigabyte mind in a 80 gigabyte world something is bound to be forgotten as I age serenely into senilaity....

    Morath
    No matter what, the earth keeps spinning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just FYI, but CAPICOM is non-deterministic for a reason... The basic algorithm is deterministic, but a salt was added late in the specification process to remove (or at least drastically limit) the deterministic quality.

    This choice was made intentionally, with considerable forethought. The non-determinisim has almost no effect on the security of the algorithm itself, but it does add a novel twist to several of the original uses for CAPICOM that the implementors decided would make the whole algorithm more useful.

    In fact, the primary reason for adding the non-determinism was to inhibit the efficiency of lookups like this.

    As pretty much everyone has observed, you're screwed, blued, and tatooed on this issue. The designers went to additional lengths in order to prevent what you're trying to do.

    Just FYI, taking pot shots at the folks who offer to help you is a great way to ensure that you'll have complete creative freedom. My guess is that few if any folks will be in a hurry to step up to help you, even if they know how.

    -PatP

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I guess it doesn't surprise me that much that such an encryption method exists. But it seems a poor choice for two-way encryption in a database.
    The one-way encryption algorithm I worked out actually uses the password itself as the randomizing seed. I think that makes it pretty durn tight, but its only for one-way encryption.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •