XF 2 Tip Some Useful Xenforo MySQL Queries

[xauUUL]

Hi everyone here is some mysql queries that i have been used for a while.

Query to replace the searched phrase with a different phrase in all messages

UPDATE xf_post

SET

message = REPLACE(message, 'old words', 'new words');

Changing a user's posting date with a SQL query

UPDATE xf_user

SET register_date = UNIX_TIMESTAMP('yyyy-mm-dd 00:00:00')

WHERE user_id = 2

Password reset with SQL query

UPDATE xf_user_authenticate

SET data = BINARY

CONCAT(

CONCAT(

CONCAT('a:3:{s:4:"hash";s:40:"', SHA1(CONCAT(SHA1('new password'), SHA1('salt')))),

CONCAT('";s:4:"salt";s:40:"', SHA1('salt'))

),

'";s:8:"hashFunc";s:4:"sha1";}'

),

scheme_class = 'XenForo_Authentication_Core'

WHERE user_id = 1;

Changing default language with sql query

UPDATE xf_user SET language_id = 2;

Changing default style with sql query

UPDATE xf_user SET style_id = 2;

I hope it helps

 

[cthctc]

How can we retrieve a user password? Not that I'm going to use it maliciously, but I just don't want to.

 

[BattleKing]

How can we retrieve a user password? Not that I'm going to use it maliciously, but I just don't want to.

cthctcYou cannot revert the password, you can only start a brute force attack, if you know as well the entered SALT key

 

[xauUUL]

@cthctc You want to edit a user passwords? can you give us more details about what exactly you want.

xauUULAn admin can set each users password to anything they want, but no one could read that password out of the database.

 

[BattleKing]

@cthctc You want to edit a user passwords? can you give us more details about what exactly you want.

xauUULAn admin can set each users password to anything they want, but no one could read that password out of the database.

 

[xauUUL]

An admin can set each users password to anything they want, but no one could read that password out of the database.

BattleKingYes, he needs to crack hash.

 

[BattleKing]

Yes, he needs to crack hash.

xauUULAlso not really possible if he do not know the entered SALT key and if they know it it takes a lot of time, depends on the length

 

[xauUUL]

Also not really possible if he do not know the entered SALT key and if they know it it takes a lot of time, depends on the length

BattleKingyep, exactly.