powered by Jive Software

Sql syntax for adding users?


I’m new to openfire. and want to write a webbase users registration page for openfire. I don’t know java and want to write a simple webbase user registration in php (which I’m comfortable with) that can be integrated with our other staffs in our company.

I’ve read that we can add users directly to the database somewher in forum. When I tried to add a row in ofUser table, the new user didn’t showed up in admin page and the client could not login with the new user. Ive search a little bit, but could not find anything.

here is my sql query:

INSERT into ofUser (username , plainPassword , encryptedPassword ,name , email , creationDate , modificationDate ) VALUES ( ‘gggg’ , ‘aa’ , NULL , ‘alili’ , ‘a@b.com’ , NOW() , 0 ) ;

Isn’t it enough? What should a do?

Thank you very much. I’m using 3.6.4.

Best regards


sorry for disturbing you. But I found where the problem is. the now() function return a string with the format like this, “2009-10-20 09:0” , but the strangly the time is a string with format like this: 001256015562778. So I change the query and that works.

sorry again.

Hi platoali,

I have a dbout regaring this, actually in the ofUser table the encryptedPassword field has been in ecrypted format, which encryption are u using to attain this?

In the query you have given it just passes a null field.

INSERT into ofUser (username , plainPassword , encryptedPassword ,name , email , creationDate , modificationDate ) VALUES ( ‘gggg’ , ‘aa’ , NULL , ‘alili’ , ‘a@b.com’ , NOW() , 0 ) ;

Does this give you a reasonale output?

If you know please tell me what encryption is being used by this field.




I came arround with a different solution.

A plugin named “user service” is present with the openfire so I can add/edit/delete user but submitting through CURL or by fopen.

A class file from the phpclasses.org helped to make life simple this is a wrapper around the open fire to achive the functionality:


can you still tell me what is the encryption algorithm used in ofUser table for encrypted password and is there any way I can break it I need to with my other applications

The password in my sql query is unencrypted. If you set the user.usePlainPassword to true. You can force openfire to not use encryption for password. OP will work with mix database too. So if you have some passwords encrypted and others unencrypted, OP will work without any problem.The problem with that plug-in is that you can’t authenticate users against it, So it is not working for me. I’ve aslo open another thread for this but no one have answered to till now.

The encryption algorithem is blowfish. OP uses a key to generate a random seed, with that seed it generates the encrypted password. This is why if same password is used twice, you get two different encrypted string. they also add the seeds to the encrypted string, to be able to dycrypt it. But I could not find out how the seed is added to the string to be able to dycrypt it.


Hi platoali,

Can you post modified sql query for adding openfire users?

What do you use instead of Now() function?



Hi Alex,

better late than never!, but check out this article, dates are stored as Java Time, this article shows you how to convert java time to Sql time.