Retrieve the MD5 encrypted password and data in the MD5 encrypted database

Retrieve the MD5 encrypted password and data in the MD5 encrypted database

Sometimes, during the development process, if you accidentally change the password of the Project Administrator Account and forget that the password in the database is encrypted with MD5, what should you do? I was confused at the beginning, but I consulted with my predecessors. I also checked the information, specially sorted out the records, and shared them with everyone.

The premise is that you know which table the password is stored in, but it is encrypted. If it is an oracle database, you can use DBMS_OBFUSCATION_TOOLKIT.MD5 (input => utl_raw.cast_to_raw ('preset password ')) to obtain an MD5 encrypted password,

So we can UPDATE user set password = DBMS_OBFUSCATION_TOOLKIT.MD5 (input => utl_raw.cast_to_raw ('preset password ')) where name = 'sys 'to re-replace the unknown password with the preset password, so that we can re-log on to the account with the password forgotten, if it is MySQL, it seems that there is a built-in MD5 () function, which can also achieve this effect. For details, you can check the MD5 usage in MySQL. If it is SQL Server, does it seem that there is a corresponding MD5 home function? It cannot be clearly remembered. It should be HashByte ('encryption method ', 'value to be encrypted'). It can also be retrieved in this way.

DBMS_OBFUSCATION_TOOLKIT.MD5 is a function provided by oracle to obtain the md5 value. You can use DBMS_OBFUSCATION_TOOLKIT.MD5 (input => utl_raw.cast_to_raw ('preset password') a from dual to obtain the encrypted data, otherwise, raw data is obtained. You need to use utl_raw.cast_to_raw to convert it to our common md5 format. The md5 values obtained above are all in uppercase. If the data stored in the database is in lower case, you need to use the lower function to convert it.

We can also use stored procedures to implement MD5 encryption:

declarev_string varchar2(50);v_n number;beginv_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 it to implement MD5 encryption:

create or replace function fun_get_md5(i_username in varchar2, i_password in varchar2)return varchar2 isbeginreturn 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 user password information on the Internet for your reference:

create table sys_user (id number,username varchar2(50),password varchar2(50));

The process of accessing the user password should be written to the stored procedure for future calls. Here we try to omit it for writing.

Password stored during user registration:

sql> insert into sys_user values (1001,'zhangwz', fun_get_md5('zhangwz','123456')) ;sql> commit;

The user's password is retrieved upon Logon:

Create or replace procedure p_login (I _uname varchar2, lower varchar2) isv_id number; v_error_text varchar2 (200); beginselect idinto v_idfrom sys_userwhere username = Login password = lower (I _uname, lower ); exceptionwhen others thenv_error_text: = 'the user name or password is incorrect! '|', Sqlcode: '| sqlcode | 'sqlerrm:' | substr (sqlerrm, 1,200); end p_login;