Home » SQL & PL/SQL » SQL & PL/SQL » Crypt and Decrypt Fileds
 Crypt and Decrypt Fileds [message #202904] |
Mon, 13 November 2006 04:26  |
**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 
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
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  |
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 |
**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 |
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 #203735 is a reply to message #202904] |
Thu, 16 November 2006 02:52 |
**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 |
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 |
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 #204408 is a reply to message #204406] |
Mon, 20 November 2006 09:42 |
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?
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 13:34:39 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
