Home » SQL & PL/SQL » SQL & PL/SQL » Crypt and Decrypt Fileds
icon9.gif  Crypt and Decrypt Fileds [message #202904] Mon, 13 November 2006 04:26 Go to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Hi guys,
I need to crypt some filed into my database.
I need to create a PL-SQL Stored Procedure to crypt that fields.

Obviously I need also to decrypt encrypted fields with this procedure...

Anyone can help me ?

This is the list of possible chars in my DB:

Quote:
',1,2,3,4,5,6,7,8,9,0,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,z,x,y,w,
A,B,C,D,E,F,G,H,I,L,M,N,O,P,Q,R,S,T,U,V,Z,X,Y,W,.,_,-,*,@,,,;,:,!,",$,%,&,(,),=,
?,^,+,[,],0,>,<,/,~,`,\,,,,,,,,,,,,,,,,,,,,,~



I tried to build a function in PL-SQL, but for some chars as z,x,y and ,,~ it returns NULL string Sad

This is the PL-SQL Procedure I created, but it dooesn't work when it find some special char (,,, etc.):

Quote:
CREATE OR REPLACE FUNCTION example(clear_stringa VARCHAR2) RETURN VARCHAR2 IS
stringa VARCHAR2(255);
crypt_stringa VARCHAR2(255);
i INT;
l INT;
cur VARCHAR2(2);

BEGIN

/***********************************************************
* Check for NULL string.
***********************************************************/

IF clear_stringa = ''
THEN
crypt_stringa := '';
ELSIF clear_stringa IS NULL
THEN
crypt_stringa := NULL;
ELSE
stringa := clear_stringa;
l := LENGTH(stringa);
crypt_stringa := '';

FOR i IN 0..l-1 LOOP
cur := SUBSTR(stringa, i+1, 1);

crypt_stringa := crypt_stringa || CHR(ASCII(cur) + 8);
END LOOP;

END IF;

RETURN crypt_stringa;

END example;



Help me please Sad

Thanks

[Updated on: Mon, 13 November 2006 06:07]

Report message to a moderator

Re: Crypt and Decrypt Fileds [message #202940 is a reply to message #202904] Mon, 13 November 2006 06:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not encryption - that's just a letter shift.

If you want to encrypt it, use something like Dbms_Obfuscation_toolkit

Also, you seem to have a bit of confusion about empty strings and nulls - in Oracle the string '' is null.


From the code you've posted, it looks like the problem is that you do not always get a printable ASCII chr by taking a prinable ASCII chr and adding 8 to its code.

What you want to do is to make a string containing all the chrs that you wish to use, and then when you want to do a substitution, simply look 8 chrs further along that string thant the letter you are substituting.
Re: Crypt and Decrypt Fileds [message #203003 is a reply to message #202904] Mon, 13 November 2006 08:47 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

I know this is not a really encryption, but if I want go on this way...can I solve the problem ?

The problem (in particular) is this:

For example, I have a table C where I've inserted possible chars to "crypt".

Using SQL Plus line command...

Quote:
select c, CHR(ASCII(c) + 8 ) Encrypter_Char from chars_table where c = 'z';


The result is:
Quote:

Char Encrypter_Char
----- ---------------
z



Using PL-SQL procedure, some strings as "z" are encrypted with NULL value, because I think PL-SQL character set doesn't support this char...

The result is:
Quote:

Char Encrypter_Char
----- ---------------
z



There's a way to solve this problem ?

I've seen DBMS_SESSION package to force DB Character Set to PL-SQL session, but it doesn't work...

Do you have an idea to solve this ?

[Updated on: Mon, 13 November 2006 08:48]

Report message to a moderator

Re: Crypt and Decrypt Fileds [message #203014 is a reply to message #203003] Mon, 13 November 2006 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I explained my idea to fix it.
I'll explain it again.

Create a string containing all the characters that you want to be able to 'encrypt'.

When you want to encrypt a chr, find it's position in that string using INSTR.

Then add 8 to the that position, using the MOD command to ensure that you'll wrap round to the start of the string if you go off the end.

Take the chr that you find at this position of the string.

Re: Crypt and Decrypt Fileds [message #203015 is a reply to message #203014] Mon, 13 November 2006 09:47 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Ooops...sorry...Before I didn't understand
Can you post me an example ?

I need also the decrypt procedure.

[Updated on: Tue, 14 November 2006 01:32]

Report message to a moderator

Re: Crypt and Decrypt Fileds [message #203735 is a reply to message #202904] Thu, 16 November 2006 02:52 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Hi...
I've found this package to use to encrypt and decrypt data in my DB

Quote:
CREATE OR REPLACE PACKAGE user_security AS

FUNCTION encrypt (p_text IN VARCHAR2) RETURNRAW;
FUNCTION decrypt (p_raw IN RAW) RETURNVARCHAR2;

PROCEDURE update_user_password (
p_username IN VARCHAR2,
p_new_password IN VARCHAR2);

END user_security;
/
CREATE OR REPLACE PACKAGE BODY user_security AS
g_key RAW(32767) :=UTL_RAW.cast_to_raw('12345678');
g_pad_chr VARCHAR2(1) := '~';

PROCEDURE padstring (p_text IN OUT VARCHAR2);

FUNCTION encrypt (p_text IN VARCHAR2) RETURNRAW IS
l_text VARCHAR2(32767) := p_text;
l_encrypted RAW(32767);
BEGIN
padstring(l_text);
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input =>
TL_RAW.cast_to_raw(l_text),
key => g_key,
encrypted_data => l_encrypted);
RETURN l_encrypted;
END;

FUNCTION decrypt (p_raw IN RAW) RETURNVARCHAR2 IS
l_decrypted VARCHAR2(32767);
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input=> p_raw,
key => g_key,
decrypted_data => l_decrypted);

RETURNRTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
END;

PROCEDURE padstring (p_text IN OUT VARCHAR2)IS
l_units NUMBER;
BEGIN
IF LENGTH(p_text) MOD 8 > 0 THEN
l_units := TRUNC(LENGTH(p_text)/8) + 1;
p_text := RPAD(p_text, l_units * 8,g_pad_chr);
END IF;
END;

PROCEDURE update_user_password (
p_username IN VARCHAR2,
p_new_password IN VARCHAR2
)
AS
v_rowid ROWID;
BEGIN
SELECT ROWID
INTO v_rowid
FROM encryption t
WHERE t.uname = (p_username)
FOR UPDATE;

UPDATE encryption
SET encryption.password =encrypt(p_new_password)
WHERE ROWID = v_rowid;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20000,'Invalid username/password.');
END;

END user_security;
/


There are three problems:

1. If input string is NULL, procedure fails...but it isn't a really problem because I insert an IF and solve the problem.

2. If I have (for example) a field with max lenght = 10, and the string to update is 10 chars, when I run the procedure the output encrypted string length > field max length. How can I solve this problem ?

3. If the field is a NUMBER datatype ?
Re: Crypt and Decrypt Fileds [message #203764 is a reply to message #203735] Thu, 16 November 2006 04:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If these are passwords that you're storing, then you'd be much better off using the Dbms_obsfuscation.md5 hashing algorithm.

The problem with encrypting passwords is that you have to store the encryption key as well, which compromises the security.

If you hash the password when you store it, then all you have to do to check if a password is valid is hash the entered password and compare it to the stored hash. There is no realistic way of getting the password back from the hash, so this system is noticably more secure.
Re: Crypt and Decrypt Fileds [message #203766 is a reply to message #203735] Thu, 16 November 2006 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
2) You'll have to increase the size of the field where you store the data.
3) Convert the number to a varchar2 and then pass it to the encryption routine.
Re: Crypt and Decrypt Fileds [message #204406 is a reply to message #202904] Mon, 20 November 2006 09:28 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

1) I need to encrypt not only passwords, but also names, surnames, addresses, etc.
2)I can not increase field values.

I would to use MD5 encryption procedures, but I don't know how to use this.

Can you post me an example ??? Sad

Thank you very much
Re: Crypt and Decrypt Fileds [message #204408 is a reply to message #204406] Mon, 20 November 2006 09:42 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can check the stored password matches the entered pwd with a line like this:
IF v_stored_pwd = DBMS_obfuscation_toolkit.md5(input_string => LOWER(v_entered_pwd) THEN
  <login stuff>
ELSE
  <failed login stuff>
END IF;


What exactly are you trying to achieve here?
Previous Topic: Subtotal & Grand Total at the last in output.
Next Topic: DB2 to Oracle migration
Goto Forum:
  


Current Time: Tue Dec 06 12:30:42 CST 2016

Total time taken to generate the page: 0.06019 seconds