If you are using Oracle as your Database Software to manage your datas, did you know that Oracle have a function to do simple string encryption. Although it was simple but the result is prety powerfull to encrypt any input string. in fact it can be used to encrypt any number data types. below i will give you an example how to encrypt a password that have a string data type and stored in a table called LOGIN that contain users login records to login to the application that you made for example. this table have the following structure :

USERID NUMBER,

USERNAME VARCHAR2(30),

PASSWORD VARCHAR2(30),

PROFILE VARCHAR2(15)

Syntax :TRANSLATE(input_string,pattern_string1,pattern_string2)

Using TRANSLATE Function in SQL Insert Statement

for example you have 'Database Administrator' as your application login password. you can change the SQL statement for validate, inserting, or updating users login data in your application source code or you can add this SQL Statement to your application source code to encrypt the password before you store it on your application login table. this is how to use TRANSLATE function in the SQL Insert Statement :

Each character in string 'Database Administrator' will be looked in pattern_string1then this function will look for character in pattern_string2 which it's position is equal to the character in pattern_string1. in the example of SQL Statement above, character 'D' in string 'Database Administrator' have 14th position at pattern_string1 so the function will look for the character that have the 14th position at pattern_string2 so the result is character '|', and so on.

Using TRANSLATE Function in SQL Select Statement

This is used to retrieving data from LOGIN table. in this example is for checking user login data.

SELECT * FROM login

WHERE username='ADMINISTRATOR'

AND PASSWORD = TRANSLATE(

'Database Administrator',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')

SQL Output :

you must use the same pattern_string as you use in SQL insert statement before.

Using TRANSLATE Function in SQL Update Statement

You may want to update your login password, for that purpose you need to update the record using SQL update statement. this is how to use TRANSLATE function in the SQL Update Statement :

UPDATE login SET password=TRANSLATE(

'oracle dba',

'ZXCVBNMLKJHGFDSAQWERTYUIOP0987654321 zxcvbnmlkjhgfdsaqwertyuiop',

'~!@#$%^&*()_+|+_)(*&^*%#@!%%*&&)(*&!%#@$#_ )@*$^@%@$#!@#!@#_)(*')

WHERE userid=111

Result :

this way anyone who accidentally see your login table will never know what your really password is. there is no harm to be wary. i hope this article will be usefull.