Manage Users Inside MySQL Database With phpMyAdmin

There are situations when you can’t just open your CMS or forum setup and manage users from within the platform. Suppose you have been hacked and cannot access your administrator account – what will you do?

At your service is the MySQL front-end web interface, phpMyAdmin, which helps you manage your site’s accounts by performing actions directly in the database. The tool is integrated with all main domain control panels (cPanel, Plesk, VistaPanel, etc.), and when not, it can be installed on your server via Fantastico or Softaculous (automatic script installers that come with the domain control panel) installation will not be considered for this quick start guide.

What is phpMyAdmin?

PHPMYADMIN is a PHP open source web tool for managing MySQL databases. Tobias Ratshiller, his inventor, began working on phpMyAdmin for 1998 as an alternative to MySQL-Webadmin, but he abandoned the project for 2000 because he did not have time to maintain it. The development was taken by three programmers for 2001, who founded the phpMyAdmin project. Its success among webmasters is due to a convenient, easy-to-use web interface and the ability to access the tool from the domain control panel (cPanel, Plesk, VistaPanel).

What approach will this guide take?

You can use two approaches to database management in phpMyAdmin:

  • management through phpMyAdmin interface
  • management by running SQL queries

This guide will show you how to use each of the two approaches to perform SQL operations on your user base.

Sample software in this manual

Among the most popular CMS scripts and forums, the choice for this guide has fallen for WordPress and the XMB Forum, although the tutorial can be easily applied to any custom software in general. The tutorial will show how to use both of the above approaches for each user database script table.

Manage phpMyAdmin users for WordPress

Interface method

Log in to your cPanel account (or any other web hosting control panel with your domain). Find phpMyAdmin under the Databases group and click the phpMyAdmin icon:

Databases in cPanel

The phpMyAdmin web interface will open in a new window. When you log in, select the database you want to work with from the left sidebar. In our example, the database is wptest_wp234. Click on it.

PHPMYADMIN

When you open your database, you will see a list of all its tables in the left sidebar, and on the main page you will see a list of the same tables with view / edit tools (in a row). To access your user list, click on the wp_users table and find the list of users.

WP users

You may have to change your credentials, email, website URL, etc. Open the line associated with your user account by clicking “Change” (next to the link there is a pencil icon) to start editing your information. The image below shows the user information fields that you can edit.

User information

To change the password, you must select MD5 from the drop-down menu. Write a strong password (you can use the Random Password Generator for best results). After you finish, save the changes.

MD5 Password Editing

MD5 is an abbreviation for Message-Digest (algorithm) v. 5, a cryptographic hash function that returns a 32-digit value. The user_pass field automatically converts your new password to an MD5 32 string.

Edit link for custom strings

If you need to get rid of all spam accounts, simply return to the “wp_users” table, select the user’s lines and click the “Delete” button at the bottom of the page. If you need to delete a single user, try clicking the “Delete” link in the user’s line (see the Image here on the right).

SQL query method

phpMyAdmin allows database administrators to execute SQL statements directly on the web interface. When you open your database in phpMyAdmin, you will see a series of tabs on the main page – Overview, structure, SQL, search, insert, export, import, operations: click the SQL tab to access the SQL web shell, write and execute your statements. See Screenshot 4th in this manual for the exact tab location.

The following are code snippets 3 that you can use to edit user accounts using SQL operations.

NOTE. By “youraccountname” I mean your hosting account username. This is the most common form of database identification in shared hosting environments where each database is assigned to a specific user. Therefore, an underscore (“_”) is between your hosting account name and the name of your database. There are other forms of database identification that use only the name of the database. The agreement you will use is the one shown in your phpMyAdmin installation.

1. Change User Password (MD5):

UPDATE `youraccountname_databasename`.`wp_users` SET` 
user_pass` = MD5 (‘testuserpasswhere’) 
WHERE ‘ID` = 2;

What does this code do?

  • UPDATE `youraccountname_databasename“ .wp_users`edits and updates the table ‘wp_users’ inside the database ‘youraccountname_databasename’.
  • SET `user_pass` = MD5 (‘testuserpasswhere’) sets the value of the ‘user_pass’ attribute to the MD5 hash string ‘testuserpasswhere’.
  • WHERE `ID` = 2; indicates that the user ID to which you apply the changes is # 2. Obviously, this is an example ID here; it can be any user id of your choice.

2. Modify user information:

UPDATE youraccountname_databasename`.`wp_users` 
SET `user_login` = ‘newusername’, 
` user_nicename` = ‘newusername’, 
`user_email` = ‘ [email protected] ‘ 
WHERE ‘ID` = 1;

What does this code do?

  • Regarding the first fragment, the UPDATEline indicates which table will be changed and in which database.
  • There SET function works with 3 different attributes: it sets the ‘user_login’ and ‘user_nicename’ new value ‘newusername’, and for ‘user_email’ – ‘ [email protected] ». Remember that ‘user_login’ and ‘user_nicename’ are two different attributes with the same value: the first is the user name used to log in to the system, the second is the name that will be displayed on the pages of your site. Example: “greatboy84” is the login name, “Frank Span” is the name displayed on the page.
  • WHERE ‘ID` = 1; indicates that the user ID you changed is # 1.

3. Delete spam account:

REMOVE FROM `youraccountname_databasename`.`wp_users` 
WHERE ‘ID` = 2

What does this code do?

  • The first line tells you that you are going to remove something from the wp_users table from the “youraccountname_databasename” database.
  • WHERE `ID` = 2 means that the user ID you are deleting is # 2.

Manage phpMyAdmin users for the XMB Forum

Interface method

This procedure is similar to WordPress user management.

Log into the domain control panel and open phpMyAdmin. Select the forum database and find the table ‘ xmb_members ‘: it contains the accounts of your forum.

a list of users

Click “Edit” in the row associated with your user account and edit your user information (see Figure below). Click the Go button to save the changes.

XMB User Editor

SQL query method

The following code snippets 2 show how to edit or delete an XMB user account through MySQL.

1. Change XMB Member Account:

UPDATE `youraccountname_xmbdatabase`.`xmb_members` SET` 
username` = ‘bigsmurf85’, 
`password` = MD5 (‘xmbuser178pass’), 
` email` = ‘[email protected] “, 
` site` =’ http: // domain .com ‘, 
`location` =’ US ‘ 
WHERE` uid` = 139;

As in the above WordPress examples, this SQL code updates the user’s current information to the new set points.

2. Delete the XMB member account:

REMOVE FROM `youraccountname_xmbdatabase`.`xmb_members` 
WHERE` uid` = 178

The first line says that you are going to delete one or more user IDs (“uid” here) from the xmb_members database. The second defines the user ID, 178 in this case.

Password Security Council

The MD5 algorithm was first vulnerable to 1996 when Hans Dobertin discovered collisions in the MD5 hash function and more reports.were divided with the public over the years. When we say “collisions”, we assume situations in which different strings of characters (ie, passwords) have the same hash value. The material is sufficient and cannot be covered by one paragraph in the quick start guide, but don’t be afraid – MD5 can still save you from a lot of headaches, as explained in this guide. However, the next security step that should be taken after you changed your password in phpMyAdmin (using MD5 encryption) is to change it again in your WordPress user profile. In fact, WordPress will encrypt your password using a library called phpass , which includes more secure and therefore not easily destructible algorithms.

“Lazy” trick!

Being lazy does not necessarily lead to the wrong choice. More often than ever, the tricks that we develop to save time turn the efficiency of the website and the higher traffic, so let’s not forget about this paragraph.

“Lazy Trick” – use the spammer’s user account to create a fictional character or friend account , How?

The procedure is simple: all you need to do is open a list of your users inside your database (you can use the interface for such a simple task), click the Edit button for the selected user string and edit the following fields (leave the ID as it is) :

  • user_login, user_pass, user_nicename, user_email
  • additional information (user_url, user_registered, etc.)

Alternatively, you can use the SQL query fragment to edit the user account that I showed you earlier in this guide.

When will this trick be useful?

Oh, let’s list a few significant examples: you may need fake accounts on your forum or blog to test new plugins, hacks and mods, or you may want to register accounts for your busy friends to be ready to use. In addition, you may need to use the “forum-bot”, which publishes the rules of the board, the rules of the section, etc. In fact, your imagination is the limit. 🙂

SQL bonus code: create user account

A small extra bonus doesn’t hurt, does it? The following are two pieces of SQL code: the first creates a new user account for your WordPress site, the second a new user for the XMB forum.

INSERT INTO `youraccountname_databasename`.`wp_users` ( 
` user_login`, 
`user_pass`, 
` user_nicename`, 
`user_email`, 
` user_registered`, 
`user_status` 

VALUES ( 
‘Newusername3’, 
MD5 (‘newpassword3’), 
“ Bully “, 
‘ [email protected] “, 
‘ 2012-04-13 00:00:00 ‘, 
“1” 
)

The sample code will create a new user and assign values ​​(user data) to the attributes user_login, user_pass, user_nicename, user_email, user_registered and user_status.

To create a new XMB forum member:

The INTO `youraccountname_databasename`.`xmb_members` the INSERT ( 
` username`, 
`parol`, 
` email ‘, 
`status`, 
` `Location 

VALUES ( 
‘ Fairy», 
the MD5 ( ‘fairypass123’), 
‘ [email protected] » , 
“Member”, 
“US” 
)

Have fun! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *