Inserting webusers into mysql using sql

Support
1

hi modxers. i need to be able to insert web login users using sql. i created a new test user in the modx /manager panel and i went into the mysql database to look for that test entry and i only see it in 2 mysql tables. can you confirm if that is correct please and if my info below is correct ? i also have a few questions for a few mysql fields below.

===============================
to insert one web user into mysql i first do this entry:

insert into modx_users(id,username,password,cachepwd,class_key,active,remote_key, remote_data,hash_class,salt, primary_group,session_stale,sudo,createdon)

values (‘’,‘genericwebuser2’,‘$2y$10$dhv2zdfqElZx2rOai1FjrusfOALCxpEHFn1VTHFA6zsEkQsAWbapq’,‘’,‘modxUser’,‘1’,‘’,‘’,‘hashing.modNative’, SALT-GOES-HERE??? , primary_group???, ‘’, ‘0’,‘TIMESTAMP-GOES-HERE’)

QUESTIONS:::::
----how do i set the ‘salt’? just stick in my own 32-character RANDOM entry?
----HOW DO I set the “primary_group”? it is not the group from the " modx_user_group_roles" table. but i need to keep it the same as the current webuser that i have which has a primary_group value of ‘4’ - that is the low-permission web user account group that i set up. i think i see that “4” in the " modx_membergroup_names" table and it looks correct that that is where the member groups are located.
----is a “sudo” value of ‘0’ ok for all new user entries?

#################################

i also see my new entry in the mysql table “modx_user_attributes”

for that entry i would use::::

INSERT INTO modx_user_attributes (id, internalKey, fullname, email, phone, mobilephone, blocked, blockeduntil, blockedafter, logincount, lastlogin, thislogin, failedlogincount, sessionid, dob, gender, address, country, city, state, zip, fax, photo, comment, website, extended)

VALUES
(4, 4, ‘genericwebuser2’, ‘jjjjjjjjjj@yahoo.co.uk’, ‘’, ‘’, 0, 0, 0, 0, 0, 0, 0, ‘327e43dh8347h34h348h’, 0, 0, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’);

-----the only values i need to consider are id, internalKey and sessionid. for “id” i will look for biggest current ‘id’ value in the table modx_user_attributes and add 1 to it, correct?

--------do i just make internalKey the same value as ‘id’ - that is how the current “modx_user_attributes” table appears to operate?

--------I can leave the sessionid value blank when i initially insert the new user, correct - and modx will edit it as required later?

--------is the “internalkey” in the “modx_user_attributes” table linked (the same) to the “id” field in the “modx_users” table?

---------anything else i need to consider?

------i don’t want a separate “username” and “email address” for the member logins - so can i just insert the email address for the username?

#############

any help would be appreciated.

2

Does it really have to be SQL?

I think it would be much easier using a PHP script:

3

yes :slight_smile: it will be sql statements in phpmyadmin initially, and soon that same sql will be inside a php file. thanks for the link. that post was 7 years ago - has nothing in modx user management or mysql profile changed in 7 years?

i don’t mind using sql in phpmyadmin or a php script as i can see directly what is going on - and it appears to be just 2 mysql tables in play so it is somewhat straightforward, i think

4

I haven’t tested the linked script, but the basics should still be the same. Changing these 2 tables would break a lot of user code, so I doubt much has changed.
(If you are using MODX 3 you may have to use the fully qualified class names.)

The problem I see with using SQL statements in phpMyAdmin is the password hash. This hash is generated with the PHP function password_hash and I don’t think you can replicate that in SQL.

To automatically generate the password hash and the salt value, it’s much easier to use the MODX classes modUser (corresponds to the database table modx_users) and modUserProfile (corresponds to modx_user_attributes).

5

You might take a look at the Register snippet (part of the Login package) or the Subscribe extra. Those would do a lot of that work for you.

Yes, front-end users should have 0 for sudo, though MODX will set that by default if you use Register or Subscribe. I haven’t checked, but I suspect that would also happen if you leave out that field.

6

thanks @bobray and @halftrainedharry - i’ll give that a go

7

Hi Bobray

for the “insert into modx_users” sql above, i will set the sudo to “0”, i will set the primary_group to “4”, but can i set the salt to some random “87987900909809809” string, bearing in mind that we will get ALL logins to reset their password before they can log in? will MODX put in a correct salt once they reset their password?

what does the ‘salt’ do?

#################

–for the “INSERT INTO modx_user_attributes” , i will get the next avaliable id number from modx_user_attributes for “id”, correct?

–do i just make internalKey the same value as ‘id’ - that is how the current “modx_user_attributes” table appears to operate? is the “internalkey” in the “modx_user_attributes” table linked (the same) to the “id” field in the “modx_users” table?

–I can leave the sessionid value blank when i initially insert the new user, correct - and modx will edit it as required later?

8

This is an auto-increment field. Don’t set it.

This field links the entry to the “modx_users” table. Use the ID of the corresponding row in the “modx_users” table.

The salt value doesn’t seem to be used at all currently. At least if the hash_class “hashing.modNative” is used.

You probably also have to add a row to the modx_member_groups database table for each user.

9

thanks @halftrainedharry - much appreciated