Jump to content


Photo
- - - - -

Processing Encrypted SQL Data In ProIV


5 replies to this topic

#1 Ross Bevin

Ross Bevin

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 226 posts
  • Gender:Male
  • Location:Manzanillo, Colima, Mexico

Posted 28 October 2013 - 01:43 PM

Hi everyone,

Does anyone have a solution for handling encrypted SQL Server data e.g. a credit card number? I already have a solution to encrypt it within ProIV but our CIO wants me to find him a purely SQL solution as well. The encryption has to be independent of ProIV so that non ProIV applications have access to the encrypted data as well.

E.g. the data is encrypted in the SQL table. I read that data into ProIV and some way decrypt it for use in my function. When I write the credit card number back to SQL, SQL encrypts it.


Thanks

Ross

#2 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 31 October 2013 - 02:57 PM

I've never done this and it might not be the most elegant solution but could you write an SSO where you pass in the connection information to the database and the SQL statement. The SSO would then connect to the database, issue the SQL statement, retrieve the results, decrypt the the results and return them back to ProIV?

#3 Ross Bevin

Ross Bevin

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 226 posts
  • Gender:Male
  • Location:Manzanillo, Colima, Mexico

Posted 31 October 2013 - 03:09 PM

Hi Mike,

Yes, I think an SSO is the way to go but bypassing SQL all together. Wim Soutendijk responded to a post on the ProIV Forum website. He has developed an SSO that encrypts/decrypts a string based on a fixed encryption key. The encrypted data is written to SQL as a varchar. I'm currently working with a Java developer to enhance it so that instead of the fixed encryption key, we pass it the key. This way users have complete control over their encrypted data with the developer not having access. I will let you know how I make out with this solution.

Regards

Ross

#4 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 02 November 2013 - 04:58 PM

Hi Ross,

I might have a need for that SSO as well.

Lew

#5 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 396 posts
  • Gender:Male
  • Location:Florida,USA

Posted 04 November 2013 - 03:58 PM

We did something similar with passwords where we had to store them as MD5 hashed values. Our DBA's created us a SQL function where we pass it a value and it returns a hashed value from that. I have included the SQL function but for obvious reasons have not included our 'salt' value.

// Function created to encrypt passwords via call to Oracle
//
// The dummy (SELECT '1' FROM DUAL WHERE 1 = 1) part of the statement is required to workaround the
// fact that ProIV will assume an implied WHERE statement if it sees a binded variable before
// locating a WHERE command and force the WHERE command just before the binded variable.

SQL
SELECT PASSWORD_HASHED, (SELECT '1' FROM DUAL WHERE 1 = 1)
FROM (SELECT SECUADMIN.HASH_VAL(:$PASSWORD) AS PASSWORD_HASHED FROM DUAL)
ENDSQL

---------------------------------------
SQL Function


CREATE OR REPLACE function SECUADMIN.hash_val (p_in_val in varchar2)--, p_key in varchar2)
return varchar2
is
l_enc_val raw(2000);
begin
l_enc_val :=
DBMS_CRYPTO.hash ( src => utl_i18n.string_to_raw (concat(p_in_val , 'salt value'),
'AL32UTF8'
),
typ => DBMS_CRYPTO.hash_md5
);
RETURN lower(l_enc_val);
End;
/
Things should be made as simple as possible, but not simpler

#6 Ross Bevin

Ross Bevin

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 226 posts
  • Gender:Male
  • Location:Manzanillo, Colima, Mexico

Posted 05 November 2013 - 12:58 PM

Hi Darren,

Thanks for posting your solution; much appreciated.

Regards
Ross



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users