• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 Which SQL queries to use for an external app to share XenForo's login database?

My site uses Dokuwiki, a wiki which can share logins with a forum by accessing the forum's database. All you need to do is plug in SQL queries to access the forum's database, and I've had this working with VB3.8 for a while.

Here are the VB3.8 queries that I'm trying to figure out XenForo equivalents for. Is there a good place to look for this information, or some example of an app doing something similar? Unfortunately I have little mysql experience...

SQL statement for checking passwords:
SELECT username AS login
FROM user
WHERE LOWER(username)=LOWER('%{user}')
and MD5(CONCAT(MD5('%{pass}'),salt)) = password
SQL statement for retrieving user information:
SELECT username AS name, email AS mail
FROM user
WHERE LOWER(username)='%{user}'
SQL statement for retrieving a user's group memberships:
SELECT g.title AS `group`
FROM usergroup g, user u
WHERE u.username = LOWER('%{user}')
and g.usergroupid = u.usergroupid
SQL statement to get the primary key of a given group:
SELECT usergroupid AS id
FROM usergroup
WHERE title='%{group}'
I'd love to get this working, not just because I already use Dokuwiki but because it could be useful for other XenForo forums. The mediawiki bridge plugin on this site hasn't been updated in two years and many people have had trouble running it, whereas Dokuwiki just requires you to paste the right SQL queries into fields in its configuration page.


XenForo developer
Staff member
The short answer is that passwords can't be validated solely via SQL. We support multiple authentication methods and the default one is bcrypt so there isn't a way to check that in MySQL. You need to use PHP.