Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    38

    Unanswered: Hashing a char column

    I want to do something like

    Code:
    SELECT MD5(SSN), FIELD1, FIELD2 FROM TABLE
    So I the actual SSN's are not shown on my screen, but the MD5 hashes are shown. In other words, I want the SSN to be hashed.

    How can I do this in DB2? If there is no built in function, how can I create one?

    Please note that I don't have many permissions, and I prefer something I can do on my own. However, if it's really impossible, I can ask the DB admin to enable features.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you didn't stick to MD5 algorithm, you can use ENCRYPT built-in function.

    IBM DB2 9.7 for Linux, UNIX, and Windows
    Version 9 Release 7
    SQL Reference, Volume 1

    Notes
    v Encryption algorithm: The internal encryption algorithm is RC2 block cipher
    with padding; the 128-bit secret key is derived from the password using an MD5
    message digest.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  4. #4
    Join Date
    Apr 2011
    Posts
    38
    Quote Originally Posted by tonkuma View Post
    If you didn't stick to MD5 algorithm, you can use ENCRYPT built-in function.

    IBM DB2 9.7 for Linux, UNIX, and Windows
    Version 9 Release 7
    SQL Reference, Volume 1
    Thank you! It worked after I executed the SET ENCRYPTION PASSWORD command.

    I found that when I was searching the forum (before asking this question) but I don't have create function permission

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Beware that hashing (like md5) and encrypting are not similar things!

    In order to encrypt, you need a password, which will allow you to decrypt later on.
    This means that someone in the possession of the password (e.g., the DBA, or the program that does the encryption) can recover the original SSN.

    Hashing, on the other hand, is a one-way function: no password is needed to "mangle" the data, but there exists no "unmangle" function, so there is a guarantee that no-one will be able to recover the SSN from its hashed value.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Frankly, I don't see any reason for displaying hashed or encrypted SSNs. If you don't want to show them, don't show them. I mean, at all. Why showing seemingly random characters instead?

Tags for this Thread

Posting Permissions

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