3

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).

2 Answers 2

9

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.

3
  • oh yeah if you wanna use phpmyadmin look here: wpbeginner.com/wp-tutorials/…
    – odinp123
    Nov 4, 2014 at 14:25
  • This wasn't working for me for the fact that i forgot the change the wp_ part in wp_capabilities. The wp_ should be your database prefix Jan 12, 2019 at 0:49
  • I've updated answer to reflect your concern @NMENewMediaEntertainment
    – odinp123
    Jun 21, 2019 at 20:13
3

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:

The class behind (most of) it is:

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.

7
  • How am I supposed to run these functions? I wasn't able to login to the site.
    – the
    Nov 4, 2014 at 14:50
  • 2
    You can run them for example from the functions.php or a (mu-)plugin. Aside from that, how should I know you aren't able to login to your site. @KasperSouren Nov 4, 2014 at 15:08
  • @KasperSouren please add that info to your question, does this mean you can't log into WP or does it mean you don't have ftp/ssh/cpanel access?
    – birgire
    Nov 4, 2014 at 15:27
  • How can I log in to a site with an empty wp_users? :) What is "the functions.php" and how do I use it? Is it possible to use from the command line? Please add more info. I've been using WP for 8 years or so but I don't know what you mean here so I can't accept this answer.
    – the
    Nov 4, 2014 at 16:24
  • 2
    In fact, I thought the "through SQL" part of the question title was pretty clear.
    – the
    Nov 4, 2014 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.