Retrieve MD5 encrypted password and MD5 data in encrypted database

Sometimes, in the development process, if you accidentally change the password of the project administrator account and forget, the existence of the password in the database is MD5 encrypted, what happens at this time? The most rookie of me, just beginning also very confused, but to the predecessors consulted the consult, oneself also checked the information, deliberately collated records, and share it to everyone.

The premise is that you know which field of the table the password exists in, but it is encrypted, and if it is an 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 in update user set. MD5 (input = utl_raw.cast_to_raw (' preset password ')) where name= ' sys ' to re-replace the unknown password with the preset password, so we can use the forgotten password to login again, if it is MySQL, It seems that there is a built-in MD5 () function, can also play such a effect, specific you can check the details of MySQL MD5 usage, if it is SQL Server, it seems to have a corresponding MD5 home function, remember not too clear, should be hashbyte (' encryption method ', ' to encrypt the value ') can also be recovered in this way.

Dbms_obfuscation_toolkit. MD5 is a function provided by Oracle to obtain MD5 values that can be used directly with Dbms_obfuscation_toolkit. MD5 (input = utl_raw.cast_to_raw (' preset password ')) A from dual to obtain the encrypted data, otherwise the raw type of data, need to use utl_raw.cast_to_ Raw is converted to our common MD5 format, and the above MD5 values are 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:

Declarev_string varchar2 (v_n) number;beginv_string: = Utl_raw.cast_to_raw (SYS.DBMS_OBFUSCATION_TOOLKIT.MD5 ( input_string = ' 123456 '); V_n: = Length (v_string);d bms_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 Isbeginreturn Utl_ra W.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 the online pick up a user password information example, for your reference:

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

The process of accessing the user's password should be written to the stored procedure to facilitate later invocation, here is as far as possible to omit to write.

Password when user registers:

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

Remove password when user logs in:

Create or Replace procedure P_login (i_uname varchar2,i_passwd varchar2) isv_id number;v_error_text varchar2 (200); Beginselect Idinto v_idfrom sys_userwhere username = I_unameand Password = fun_get_md5 (I_uname, i_passwd); Exceptionwhen o Thers thenv_error_text: = ' username or password is incorrect! ' | | ', Sqlcode: ' | | Sqlcode | | ' sqlerrm: ' | | SUBSTR (SQLERRM, 1, +); end P_login;

Retrieve MD5 encrypted password and MD5 data in encrypted database