How to store usernames and passwords safely in MySQL database?

MySQLMySQLi Database

To store username and passwords safely in MySQL database, we can use MD5().

Let us see an example. First, we will create a table. The CREATE command is used to create a table.

mysql> create table UserNameAndPasswordDemo
- > (
- > U_Id int(10) unsigned NOT NULL AUTO_INCREMENT,
- > UserId varchar(255) DEFAULT NULL,
- > UserPassword varchar(255) DEFAULT NULL,
- > primary key(U_Id),
- > UNIQUE KEY `UserId` (`UserId`)
- > );
Query OK, 0 rows affected (0.61 sec)

Inserting records and safely storing passwords with the help of MD5().

mysql> INSERT INTO UserNameAndPasswordDemo(UserId, UserPassword) VALUES ('John@gg.com', MD5('john123'));
Query OK, 1 row affected (0.17 sec)

mysql> INSERT INTO UserNameAndPasswordDemo(UserId, UserPassword) VALUES (MD5('Carol@gg.com'), MD5('123Carol'));
Query OK, 1 row affected (0.14 sec)

Now, we will implement the SELECT statement to display the records, including password in MD5. Here, we are only displaying records for UserId 'John@gg.com'.

mysql> SELECT *from UserNameAndPasswordDemo where UserId='John@gg.com';

The following is the output.

+------+-------------+----------------------------------+
| U_Id | UserId      | UserPassword                     |
+------+-------------+----------------------------------+
| 1    | John@gg.com | 6e0b7076126a29d5dfcbd54835387b7b |
+------+-------------+----------------------------------+
1 row in set (0.00 sec)

To display all records.

mysql> SELECT *from UserNameAndPasswordDemo;

The following is the output wherein we have saved username and password using MD5 −

+------+----------------------------------+----------------------------------+
| U_Id | UserId                           | UserPassword                     |
+------+----------------------------------+----------------------------------+
| 1    | John@gg.com                      | 6e0b7076126a29d5dfcbd54835387b7b |
| 2    | 5f565a3d794f85e5db4f3bb7b5811a25 | f1d2fb85f7d6ce7428b9b3fd569be42b |
+------+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)
raja
Published on 20-Nov-2018 14:48:16
Advertisements