Retrieve MD5 encrypted password and MD5 encrypted database data

Sometimes, in the development process, if accidentally changed the project Administrator account password and forgot, there is the password in the database is MD5 encryption, this time how to do? The most rookie of me, just at the beginning is also very confused, but to the predecessors consulted for advice, I also checked the data, deliberately sorted out the record, And share them with you.

The premise is that you know which table the password exists in which field, but is encrypted, if it is Oracle database, you can use Dbms_obfuscation_toolkit. MD5 (Input => utl_raw.cast_to_raw (' preset password ') to get a MD5 encrypted password,

So we can password=dbms_obfuscation_toolkit the update user set. MD5 (Input => utl_raw.cast_to_raw (' preset password ')) where Name= ' SYS ' to replace the password you don't know with the preset password, so we can forget the password of the account login again, if it is MySQL, as if there is a built-in MD5 () function, can also play such an effect, specific you can check the details of MySQL MD5 usage, if it is SQL Server words, as if there is a corresponding MD5 home function, remember not too clear, should be hashbyte (' encryption mode ', ' to be encrypted value '), can also be retrieved by this way.

Dbms_obfuscation_toolkit. MD5 is a function provided by Oracle to obtain MD5 values, which can be directly used by Dbms_obfuscation_toolkit. MD5 (Input => utl_raw.cast_to_raw (' preset password ')) A from dual to obtain the encrypted data, otherwise get raw type of data, need to use utl_raw.cast_to_ Raw is converted into our usual MD5 format, and the above MD5 values are all uppercase, if the database is stored in lowercase, you need to use the lower function to convert.

We can also use stored procedures to implement MD5 encryption:

Declare

V_string VARCHAR2 (50);

V_n number;

Begin

V_string: = Utl_raw.cast_to_raw (sys.dbms_obfuscation_toolkit.md5 (input_string => ' 123456 '));

V_n: = Length (v_string);

Dbms_output.put_line (v_string | | '--' || V_n);

End

You can also write a function and call implement MD5 encryption:

Create or Replace function fun_get_md5 (i_username in Varchar2, I_password in varchar2)

Return VARCHAR2 is

Begin

Return Utl_raw.cast_to_raw (dbms_obfuscation_toolkit.md5 input_string => (i_username| | I_password)));

End FUN_GET_MD5;

Sql> Select FUN_GET_MD5 (' Zhangwz ', ' 123456 ') from dual;

FUN_GET_MD5 (' zhangwz ', ' 123456 ')

--------------------------------------------------------------------------------

0d8df9100cd33ef80af0527858136e0b

Below is an example of accessing the user's password information on the Internet for your reference:

CREATE TABLE Sys_user (ID number,username varchar2 (m), password varchar2 (50));

The process of accessing a user's password should be written to a stored procedure to facilitate later invocation, which is omitted to write as much as possible.

User Login Password:

sql> INSERT into Sys_user values (1001, ' Zhangwz ', fun_get_md5 (' zhangwz ', ' 123456 '));

Sql> commit;

Remove password when user logs on:

Create or Replace procedure P_login (I_uname varchar2

, i_passwd varchar2) is

V_ID number;

V_error_text VARCHAR2 (200);

Begin

Select ID

Into v_id

From Sys_user

where username = I_uname

and password = FUN_GET_MD5 (I_uname, I_PASSWD);

exception

When others then

V_error_text: = ' username or password is incorrect! ' | | ', Sqlcode: ' | | Sqlcode | |

' SQLERRM: ' | | SUBSTR (SQLERRM, 1, 200);

End P_login;