How can I add a user through SQL?

Question

By mistake I just ran this query: (note the extra ;)

DELETE FROM wp_users; WHERE ID = 321;

How can I add a user through SQL?

I searched around but couldn't find a proper answer to this fairly simple question.

More info about the situation I was in when I asked this:

• Can't use wp-cli on this shared hosting environment and it's way easier to launch mysql than to figure out how to run some PHP.
• I was not logged into the site (so I can't log in anymore).

Answer 1

This will create a new admin user called username with password: password in a database called DATABASE with table prefix wp_

Change the two accordingly to your own database name and table prefix.

Try this:

First create a row in wp_users. Replace DATABASE with your database name, username with your choosen username, password with your password of choice.

INSERT INTO `DATABASE`.`wp_users` (`ID`, `user_login`, `user_pass`, 
`user_nicename`, `user_email`, `user_url`, `user_registered`,
`user_activation_key`, `user_status`, `display_name`) VALUES ('9999', 
'username', MD5('password'), 'nickname', '[email protected]', '', 
'2014-11-04 00:00:00', '', '0', 'username');

Next insert two rows into wp_usermeta. Replace DATABASE with your database.

INSERT INTO `DATABASE`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`,
`meta_value`) VALUES (NULL, '9999', 'wp_capabilities', 
'a:1:{s:13:"administrator";s:1:"1";}'), (NULL, '9999', 'wp_user_level', '10');

The key 9999 is a unique ID number so choose something that is not used.

Answer 2

Generally, WordPress has functions to handle what you want, it is much preferable to use them. For example, because it isn't a complete list, some of those are:

• wp_delete_user()
• wp_create_user()
• wp_insert_user()
• wp_update_user()

The class behind (most of) it is:

• WP_User

If you really need to know the SQL that is used, I would suggest you read the according source files. But as I said, it is much preferable to use the possibilities WordPress already offers, instead of doing your own custom SQL.