2

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

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@example.com', '', 
'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.

|improve this answer|||||
  • oh yeah if you wanna use phpmyadmin look here: wpbeginner.com/wp-tutorials/… – odinp123 Nov 4 '14 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 – NME New Media Entertainment Jan 12 '19 at 0:49
  • I've updated answer to reflect your concern @NMENewMediaEntertainment – odinp123 Jun 21 '19 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.

|improve this answer|||||
  • How am I supposed to run these functions? I wasn't able to login to the site. – kqw Nov 4 '14 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 – Nicolai Nov 4 '14 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 '14 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. – kqw Nov 4 '14 at 16:24
  • 1
    In fact, I thought the "through SQL" part of the question title was pretty clear. – kqw Nov 4 '14 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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