Custom Database Integration

Openfire Openfire Support
1

I try to integrate an other database for User and Auth - but it don’t work.

I have configured following database field and configuration:

ofProperty Table

--
-- Dumping data for table `ofProperty`
-- INSERT INTO `ofProperty` (`name`, `propValue`) VALUES
('passwordKey', 'password'),
('xmpp.socket.ssl.active', 'true'),
('xmpp.domain', 'mybur.com'),
('provider.admin.className', 'org.jivesoftware.openfire.admin.DefaultAdminProvider'),
('provider.lockout.className', 'org.jivesoftware.openfire.lockout.DefaultLockOutProvider'),
('provider.user.className', 'org.jivesoftware.openfire.user.JDBCUserProvider'),
('provider.auth.className', 'org.jivesoftware.openfire.auth.JDBCAuthProvider'),
('xmpp.auth.anonymous', 'true'),
('provider.group.className', 'org.jivesoftware.openfire.group.DefaultGroupProvider'),
('provider.vcard.className', 'org.jivesoftware.openfire.vcard.DefaultVCardProvider'),
('provider.securityAudit.className', 'org.jivesoftware.openfire.security.DefaultSecurityAuditProvider'),
('xmpp.session.conflict-limit', '0'),
('update.lastCheck', '1270360818281'),
('jdbcProvider.connectionString', 'jdbc:mysql://localhost/database?databaseuser&password=password'),
('jdbcAuthProvider.passwordSQL', 'SELECT password FROM Table WHERE username=?'),
('jdbcAuthProvider.passwordType', 'md5'),
('jdbcUserProvider.loadUserSQL', 'SELECT name,email FROM Table WHERE username=?'),
('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM Table'),
('jdbcUserProvider.allUsersSQL', 'SELECT username FROM Table'),
('jdbcUserProvider.searchSQL', 'SELECT username FROM Table WHERE'),
('jdbcUserProvider.usernameField', 'username'),
('jdbcUserProvider.nameField', 'name'),
('jdbcUserProvider.emailField', 'email'),
('jdbcProvider.driver', 'com.mysql.jdbc.Driver');

openfire.xml

<connectionProvider>     <className>org.jivesoftware.database.DefaultConnectionProvider</className>   </connectionProvider>    <database>     <defaultProvider>       <driver>com.mysql.jdbc.Driver</driver>        <serverURL>jdbc:mysql://localhost:3306/database</serverURL>        <username>dbuser</username>        <password>dppassword</password>        <testSQL>select 1</testSQL>        <testBeforeUse>true</testBeforeUse>        <testAfterUse>true</testAfterUse>        <minConnections>5</minConnections>        <maxConnections>25</maxConnections>        <connectionTimeout>1.0</connectionTimeout>     </defaultProvider>   </database>    <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>
  </provider>
   <jdbcProvider>
    <driver>com.mysql.jdbc.Driver</driver>
    <connectionString>jdbc:mysql://localhost/database?user=dbuser&amp;password=passord</connectionString>
  </jdbcProvider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM Table WHERE username=?</passwordSQL>
     <passwordType>md5</passwordType>
  </jdbcAuthProvider>
  <jdbcUserProvider>
     <loadUserSQL>SELECT name,email FROM Table WHERE username=?</loadUserSQL>
     <userCountSQL>SELECT COUNT(*) FROM Table</userCountSQL>
     <allUsersSQL>SELECT username FROM Table</allUsersSQL>
     <searchSQL>SELECT username FROM Table WHERE</searchSQL>
     <usernameField>username</usernameField>
     <nameField>name</nameField>
     <emailField>email</emailField>
  </jdbcUserProvider>

So I have following Errors:

  • When I put the & in the openfire.xml it will give me a warning, that the configuration and the database are not the same value. It will delete all the User and Auth lines from the openfire.xml

  • When I remove the & in the config file, I will have an error, that the line in the config file need to be end with a ;

  • When I put the & into the database and in the config file, I have an SQL error. In this case openfire try to connect without the password to the database

So it looks for my like a BUG. Or have you a solution for that problem?

Thomas

2

When you have the & string in the XML but not in the database:

warn.log

2010.04.06 12:01:48 Property 'jdbcProvider.connectionString' as specified in openfire.xml differs from what is stored in the database.  P
lease make property changes in the database instead of openfire.xml.

aftter that, openfire will clear the values in the openfire.xml

<provider>     <auth></auth>      <user></user>   </provider>    <jdbcProvider>     <jdbcDriver>       <className>com.mysql.jdbc.Driver</className>     </jdbcDriver>      <connectionString>jdbc:mysql://localhost/database?user=user&amp;password=password</connectionString>   </jdbcProvider>    <jdbcAuthProvider></jdbcAuthProvider>    <jdbcUserProvider></jdbcUserProvider>

when you have the & or the & in the database and openfire.xml

Æorg.jivesoftware.util.XMLProperties.buildDoc(XMLProperties.java:474)Å Error reading XML properties
org.dom4j.DocumentException: Error on line 62 of document  : The reference to entity "password" must end with the ';' delimiter. Nested e
xception: The reference to entity "password" must end with the ';' delimiter.

I need a fast solution for this problem. Othwise I need to look for a other server solution without this problem

Thomas

1 Like