alt="User Management Within MySQL Database With phpMyAdmin" src="http://whsr.webrevenueinc1.netdna-cdn.com/wp-content/uploads/2015/04/default-image-500x308_c.jpg" />
There are situations when you can’t simply open your CMS or forum installation and manage users from inside the platform. Suppose you’ve been hacked and can’t access your admin account – what will you do?
Coming to your rescue is a web-based MySQL front-end — phpMyAdmin — that helps you manage your site user accounts by performing actions directly on the database. The tool is integrated with all the major domain control panels (cPanel, Plesk, VistaPanel, etc.) and, when not present, it can be installed on your server via Fantastico or Softaculous (automated script installers that come with a domain control panel). Manual installation will not be considered for this short guide.
What Is phpMyAdmin?
href="http://www.phpmyadmin.net/" target="_blank">phpMyAdmin is a web-based, open source PHP tool for MySQL database management. Tobias Ratschiller, its inventor, started to work on phpMyAdmin on 1998 as an alternative to MySQL-Webadmin, but he dropped the project on 2000 because he had no time to maintain it. The development was taken over by three programmers on 2001, who founded The phpMyAdmin Project. Its success among webmasters is due to a user-friendly, easy-to-use web interface and the possibility to access the tool from a domain control panel (cPanel, Plesk, VistaPanel).
What Approach Will This Guide Take?
You can use two approaches to database management within phpMyAdmin:
- management via phpMyAdmin interface
- management via SQL query execution
This guide will show you how to use each of the two approaches to perform SQL operations on your user base.
Example Software In This Guide
Among the most popular CMS and forum scripts, the choices for this guide fell for WordPress and XMB Forum, although the tutorial can easily be applied to any user-based site software in general. The guide will show you how to use both of the two above mentioned approaches for each script’s database user table.
phpMyAdmin User Management for WordPress
Interface Method
Login into your cPanel account (or any other web hosting control panel with your domain). Look for phpMyAdmin under the group ‘Databases’ and click on the phpMyAdmin icon:
src="http://www.webhostingsecretrevealed.com/images/2012/0416-1.jpg" class="border" alt="Databases in cPanel" width="600" height="149" />
The phpMyAdmin web interface will open in a new window. Once you’re in, select the database you need to work on from the left sidebar. In our example, the database is wptest_wp234. Click it.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-2.jpg" class="border" alt="phpMyAdmin" width="600" height="256" />
When you open your database, you will see a list of all its tables on the left sidebar, while the main page shows you a list of those same tables with browsing/editing tools (per row). To access your users list, click on the ‘wp_users’ table and look up the list of users.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-3.jpg" class="border" alt="WP Users" width="600" height="181" />
You may need to change your credentials, email, website URL, etc. Open the row associated with your user account by clicking “Edit” (there’s a pencil icon next to the link) to start editing your information. The image below shows the user info fields you can edit.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-4.jpg" class="border" alt="User Info Edit" width="600" height="306" />
To change your password, you need to select MD5 from the drop-down menu. Write a strong password (you can use a href="http://www.thebitmill.com/tools/password.html" target="_blank">random password generator for better results). Once you’re done, save your changes.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-5.jpg" class="border" alt="MD5 Password Edit" />
The MD5 is acronym for Message-Digest (algorithm) v. 5, a cryptographic hash function that returns a 32-digits value. The ‘user_pass’ field will automatically convert your new password into a MD5 32-digits string.
id="right">
src="http://www.webhostingsecretrevealed.com/images/2012/0416-6.jpg" alt="Edit Link for User Rows" />
In case you need to get rid of all spam accounts, just go back to the ‘wp_users’ table, select the user rows of choice and hit the “Delete” button at the bottom of the page. If you need to remove a single user, intead, simply hit the “Delete” link on the user row (see image here on the right).
SQL Query Method
phpMyAdmin allows database managers to execute SQL statements directly on the web interface. When you open your database in phpMyAdmin, you’ll see a series of tabs on the main page — Browse, Structure, SQL, Search, Insert, Export, Import, Operations: click the SQL tab to access an SQL web shell in which to write and run your statements. Refer to the 4th screenshot in this guide for the exact tab location.
Following are 3 code snippets you can use to edit user accounts by SQL operations.
NOTE: By ‘youraccountname’ I mean your hosting account username. This is the most common form of database identification on shared hosting environments, where each database is assigned to a specific user. Hence the underscore(“_”) between your hosting account username and your database name. There are other forms of database identification that only use a database name. The convention 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 ‘wp_users’ table inside the database ‘youraccountname_databasename’.
- SET `user_pass` = MD5( ‘testuserpasswhere’ )sets the value of the attribute ‘user_pass’ to the MD5 hash string of ‘testuserpasswhere’.
- WHERE `ID` =2;tells you that the user ID you’re applying modifications to is the #2. Obviously this is an example ID here; it can be any user ID of choice.
2. Edit user info:
UPDATE `youraccountname_databasename`.`wp_users`
SET `user_login` = ‘newusername’,
`user_nicename` = ‘newusername’,
`user_email` = ‘newusername@domain.com’
WHERE `ID` = 1;
What does this code do?
- As for the first snippet, the UPDATEline specifies which table will be modified, and in which database.
- The SETfunction here operates on 3 different attributes: it sets ‘user_login’ and ‘user_nicename’ to a new value ‘newusername’, and ‘user_email’ to ‘newusername@domain.com’. Mind that ‘user_login’ and ‘user_nicename’ are two different attributes with the same value: the former is the username used to login, the latter is the name that will be shown on your website pages. Example: ‘greatboy84’ is the login name, ‘Frank Span’ is the name shown on page.
- WHERE `ID` = 1;tells you that the user ID you modified is number #1.
3. Delete spammer account:
DELETE FROM `youraccountname_databasename`.`wp_users`
WHERE `ID` = 2
What does this code do?
- The first line tells you that you’re going to delete something from the ‘wp_users’ table from database ‘youraccountname_databasename’.
- WHERE `ID` = 2means the user ID you’re deleting is #2.
phpMyAdmin User Management for XMB Forum
Interface Method
The procedure is similar to WordPress user management.
Login to your domain control panel and open phpMyAdmin. Select your forum database and look for the table ‘xmb_members‘: it contains your forum’s member accounts.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-7.jpg" class="border" alt="XMB Forum Members List MySQL" />
Click ‘Edit’ on the row associated with your user account and edit your user info (see image below). Hit the ‘Go’ button to save your changes.
src="http://www.webhostingsecretrevealed.com/images/2012/0416-8.jpg" class="border" alt="XMB User Edit" />
SQL Query Method
The following 2 code snippets show you how to edit or delete an XMB user account via MySQL.
1. Edit XMB member account:
UPDATE `youraccountname_xmbdatabase`.`xmb_members`
SET `username` = ‘bigsmurf85’,
`password` = MD5( ‘xmbuser178pass’ ) ,
`email` = ‘testmail@gmx.com’,
`site` = ‘http://domain.com’,
`location` = ‘US’
WHERE `uid` =139;
As with the WordPress examples above, this SQL code updates a user’s current information to new specified values.
2. Delete XMB member account:
DELETE FROM `youraccountname_xmbdatabase`.`xmb_members`
WHERE `uid` = 178
The first line says you’re going to delete one or more user IDs (‘uid’ here) from the database ‘xmb_members’. The second specifies the user ID number, 178 in this case.
A Password Security Tip
The MD5 algorithm has first proven vulnerable on 1996, when href="cseweb.ucsd.edu/~bsy/dobbertin.ps" target="_blank">Hans Dobbertin discovered collisions in the MD5 hash function, and more href="http://www.infosec.sdu.edu.cn/uploadfile/papers/How%20to%20Break%20MD5%20and%20Other%20Hash%20Functions.pdf" target="_blank">reports were shared with the public over the years. When we say ‘collisions’ we intend situations in which different strings of characters (i.e. passwords) have the same hash value. The material is ample and can’t certainly be covered by a single paragraph in a short guide, but do not fear– MD5 can still save you from a lot of headaches as it was explained in this guide. However, the next safety step to take after you changed your password in phpMyAdmin (using the MD5 encryption) is to change it again in your WordPress user profile. In fact, WordPress will encrypt your password using a library called href="http://www.openwall.com/phpass/" target="_blank">phpass, that includes safer and therefore not easily breakable algorithms.
The ‘Lazy’ Trick!
To be lazy does not necessarily result in wrong choices. More often than ever, the tricks we develop to save time convert into website efficiency and higher traffic, so let’s not overlook this paragraph.
The ‘lazy trick’ is to take advantage of a spammer’s user account to create a fictional character’s or friend’s account. How?
The procedure is simple – all you need to do is open your users list inside your database (you can use the interface approach for this kind of easy task), click the “Edit” button for the chosen user row and edit the following fields (leave the ID as is):
- user_login, user_pass, user_nicename, user_email
- optional details (user_url, user_registered, etc.)
In alternative, you can use the SQL query snippet for user account editing I showed you earlier in this guide.
When will this trick turn out 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 perhaps you want to register accounts for your busy friends to have them ready to use. Also, you may need to use a ‘forum bot’ that publishes board rules, section rules and so on. Really, your imagination is the limit. :)
Bonus SQL Code: Create A User Account
A small extra bonus won’t hurt, will it? Following are two SQL code snippets: the first creates a new user account for your WordPress site, the second a new XMB Forum user.
INSERT INTO `youraccountname_databasename`.`wp_users` (
`user_login` ,
`user_pass` ,
`user_nicename` ,
`user_email` ,
`user_registered` ,
`user_status`
)
VALUES (
‘newusername3’,
MD5( ‘newpassword3’ ) ,
‘Mally Bally’,
‘mallybally@domain.com’,
‘2012-04-13 00:00:00’,
‘1’
)
The sample code will create a new user and will assign values (user information) to the attributes ‘user_login’, ‘user_pass’, ‘user_nicename’, ‘user_email’, ‘user_registered’ and ‘user_status’.
To create a new XMB Forum member:
INSERT INTO `youraccountname_databasename`.`xmb_members` (
`username` ,
`password` ,
`email` ,
`status`,
`location`
)
VALUES (
‘fairyland’,
MD5( ‘fairypass123’ ) ,
‘fairyland@domain.com’,
‘Member’,
‘US’
)
Have fun! :)
Page 24 – Web Hosting Secret Revealed
