Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Need some help with Encryption

    I have the following stored procedure created under oracle 9.2 and need some help modifying this.
    Code:
    CREATE OR REPLACE PROCEDURE Proc_decrypt_password(UserName VARCHAR2) 
        IS
        
         key_string          VARCHAR2(8)  := 'scottsco';
           
         encrypted_string    VARCHAR2(2048);
         decrypted_string    VARCHAR2(2048);
         
       BEGIN
        select user_password into encrypted_string
         from User_Admin
         where Alias_Name = UserName;
       
         dbms_obfuscation_toolkit.DESDecrypt(
                          input_string => encrypted_string, 
                          key_string => key_string, 
                          decrypted_string => decrypted_string);
                  
         update User_Admin
         set User_Password = decrypted_string
         where Alias_Name = UserName;          
                       
       END;
    The part where I do the update, well I do not want to do the update but rather just return the encrypted string to a temp variable so that I can call it from my asp page. Any ideas? If I update it, the password chosen will show up in the database but I want it left encrypted in the datebase(which I have successfully done). If it is encrypted though, then my asp page cannot validate the password when the user types it in. Therefore, I think I need to just pass it to a variable. Any help. Thanks again

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you just want to return it back to ASP, two choices you have are making the procedure a function and use return to return back the value to ASP or else, have the procedure have a second OUTPUT parameter on which you set this value and catch it back in ASP.

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    JMArtinez,

    Any way you could give me some code example of this. I learn better by example. Thanks so much. Much appreciated.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    many,many fine coding example can be found at http://asktom.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Thanks for the reply anacedent but I wasn't sure what JMartinez meant about his second statement "have a second OUTPUT parameter on which you set this value and catch it back in ASP". I actually just wanted to modify my existing code and not use any new recreate this from scratch.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Untested, something like this ..
    Code:
    CREATE OR REPLACE PROCEDURE Proc_decrypt_password(UserName VARCHAR2, p_decrypted_string OUT varchar2 )
    ......
    ......
    p_decrypted_string := decrypted_string;
    ......
    ......
    END;
    Then, in ASP, define an OUTPUT parameter (just like you define an IN for the USERNAME parameter) and catch this value on your var. I currently don't have an ASP page laying around here to show you the exact same code but you will find tons of it on the web.

  7. #7
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Hello again JMartinez,

    I am almost there with your help. Much appreciated again. This is what I came up with
    Code:
    CREATE OR REPLACE PACKAGE Pack_Decrypt_Password
    AS
      TYPE CURSOR_TYPE IS REF CURSOR;
      PROCEDURE p_Decrypt_Password (UserName VARCHAR2,
                                                    p_decrypted_string OUT VARCHAR2, 
                                                    results_cursor IN OUT CURSOR_TYPE); 
    END;
    /
    CREATE OR REPLACE PACKAGE BODY Pack_Decrypt_Password 
    AS
      PROCEDURE p_Decrypt_Password (UserName VARCHAR2,
                                                   p_decrypted_string OUT VARCHAR2, 
                                                   results_cursor IN OUT CURSOR_TYPE)
      IS
         key_string          VARCHAR2(8)  := 'scottsco';
           
         encrypted_string    VARCHAR2(2048);
         decrypted_string    VARCHAR2(2048);
      
      
      BEGIN
        OPEN results_cursor FOR
           select user_password into encrypted_string
         from User_Admin
         where Alias_Name = UserName;
       
         dbms_obfuscation_toolkit.DESDecrypt(
                          input_string => encrypted_string, 
                          key_string => key_string, 
                          decrypted_string => decrypted_string);
                  
         p_decrypted_string := decrypted_string;   
      END;
    END;
    And the ASP Code
    Code:
                 oConn.Open
    	oCmdUserInfo.ActiveConnection = oConn
    	oCmdUserInfo.CommandType = adCmdStoredProc
    	oCmdUserInfo.CommandText                                                                   = "Pack_Decrypt_Password.p_decrypt_password"
    	'Set objRsUserInfo = oCmdDirector.Execute
    
    	oCmdUserInfo.Parameters.Append oCmdUserInfo.CreateParameter("UserName", adVarChar, adParamInput, 400, UserName)
    	oCmdUserInfo.Parameters.Append oCmdUserInfo.CreateParameter("p_decrypted_string", adVarChar, adParamOutput, 8)
    	Set objRsUser = oCmdUserInfo.Execute
    	
    ' We now check if the user is valid. If user is valid, the recordset MUST
    ' haverecord. Otherwise it is empty. If user exists, we set authentication 
    ' status to 1 and send the user to appropriate page, say welcome.asp. 
    ' Else send the user back to login.asp
    
    If objRsUser.EOF Then
      Session("Authenticated") = 0
    ........ it keeps going but I stopped here%>
    I get error:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Oracle][ODBC][Ora]ORA-28231: no data passed to obfuscation toolkit ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0 ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153 ORA-06512: at "test.PACK_DECRYPT_PASSWORD", line 19 ORA-06512: at line 1
    /test/test/login.asp, line 108

    line 108 is
    Code:
    Set objRsUser = oCmdUserInfo.Execute
    Am I doing something wrong in my package? Thanks again.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    By the way, I would not store the password encrypted, I would store it hashed similar to the way oracle does it. This way, not even the administrator can hack a password. The users password is put through a one way hash and stored that way. When the user enters a password, the password is put through the same process and the hashed values are compared and a simple flag is sent back to the user indicating if the password is correct or not. This is a much more secure method then storing the actual passwords.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Apr 2006
    Posts
    140
    Hmmmm beilstwh I guess you figured my next question was going to be how do I do this? Is there anyway to just modify my existing code or is this a complete rewrite. Could you explain how I would do this and this "Hashing" does this apply to oracle 9.2. Thanks again. Hopefully I can get my existing code to work with the hash....your help is needed. Thank you.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    For a nice description of the methoid see the following link

    http://builder.com.com/5100-6388-5218673.html
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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