001/* 
002    Licensed to the Apache Software Foundation (ASF) under one
003    or more contributor license agreements.  See the NOTICE file
004    distributed with this work for additional information
005    regarding copyright ownership.  The ASF licenses this file
006    to you under the Apache License, Version 2.0 (the
007    "License"); you may not use this file except in compliance
008    with the License.  You may obtain a copy of the License at
009
010       http://www.apache.org/licenses/LICENSE-2.0
011
012    Unless required by applicable law or agreed to in writing,
013    software distributed under the License is distributed on an
014    "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015    KIND, either express or implied.  See the License for the
016    specific language governing permissions and limitations
017    under the License.  
018 */
019package org.apache.wiki.auth.user;
020
021import org.apache.commons.lang3.StringUtils;
022import org.apache.wiki.api.core.Engine;
023import org.apache.wiki.api.exceptions.NoRequiredPropertyException;
024import org.apache.wiki.auth.NoSuchPrincipalException;
025import org.apache.wiki.auth.WikiPrincipal;
026import org.apache.wiki.auth.WikiSecurityException;
027import org.apache.wiki.util.Serializer;
028
029import javax.naming.Context;
030import javax.naming.InitialContext;
031import javax.naming.NamingException;
032import javax.sql.DataSource;
033import java.io.IOException;
034import java.io.Serializable;
035import java.security.Principal;
036import java.sql.Connection;
037import java.sql.DatabaseMetaData;
038import java.sql.PreparedStatement;
039import java.sql.ResultSet;
040import java.sql.SQLException;
041import java.sql.Timestamp;
042import java.util.Date;
043import java.util.HashSet;
044import java.util.Map;
045import java.util.Properties;
046import java.util.Set;
047
048/**
049 * <p>
050 * Implementation of UserDatabase that persists {@link DefaultUserProfile}
051 * objects to a JDBC DataSource, as might typically be provided by a web
052 * container. This implementation looks up the JDBC DataSource using JNDI. The
053 * JNDI name of the datasource, backing table and mapped columns used by this 
054 * class can be overridden by adding settings in <code>jspwiki.properties</code>.
055 * </p>
056 * <p>
057 * Configurable properties are these:
058 * </p>
059 * <table>
060 * <tr> <thead>
061 * <th>Property</th>
062 * <th>Default</th>
063 * <th>Definition</th>
064 * <thead> </tr>
065 * <tr>
066 * <td><code>jspwiki.userdatabase.datasource</code></td>
067 * <td><code>jdbc/UserDatabase</code></td>
068 * <td>The JNDI name of the DataSource</td>
069 * </tr>
070 * <tr>
071 * <td><code>jspwiki.userdatabase.table</code></td>
072 * <td><code>users</code></td>
073 * <td>The table that stores the user profiles</td>
074 * </tr>
075 * <tr>
076 * <td><code>jspwiki.userdatabase.attributes</code></td>
077 * <td><code>attributes</code></td>
078 * <td>The CLOB column containing the profile's custom attributes, stored as key/value strings, each separated by newline.</td>
079 * </tr>
080 * <tr>
081 * <td><code>jspwiki.userdatabase.created</code></td>
082 * <td><code>created</code></td>
083 * <td>The column containing the profile's creation timestamp</td>
084 * </tr>
085 * <tr>
086 * <td><code>jspwiki.userdatabase.email</code></td>
087 * <td><code>email</code></td>
088 * <td>The column containing the user's e-mail address</td>
089 * </tr>
090 * <tr>
091 * <td><code>jspwiki.userdatabase.fullName</code></td>
092 * <td><code>full_name</code></td>
093 * <td>The column containing the user's full name</td>
094 * </tr>
095 * <tr>
096 * <td><code>jspwiki.userdatabase.loginName</code></td>
097 * <td><code>login_name</code></td>
098 * <td>The column containing the user's login id</td>
099 * </tr>
100 * <tr>
101 * <td><code>jspwiki.userdatabase.password</code></td>
102 * <td><code>password</code></td>
103 * <td>The column containing the user's password</td>
104 * </tr>
105 * <tr>
106 * <td><code>jspwiki.userdatabase.modified</code></td>
107 * <td><code>modified</code></td>
108 * <td>The column containing the profile's last-modified timestamp</td>
109 * </tr>
110 * <tr>
111 * <td><code>jspwiki.userdatabase.uid</code></td>
112 * <td><code>uid</code></td>
113 * <td>The column containing the profile's unique identifier, as a long integer</td>
114 * </tr>
115 * <tr>
116 * <td><code>jspwiki.userdatabase.wikiName</code></td>
117 * <td><code>wiki_name</code></td>
118 * <td>The column containing the user's wiki name</td>
119 * </tr>
120 * <tr>
121 * <td><code>jspwiki.userdatabase.lockExpiry</code></td>
122 * <td><code>lock_expiry</code></td>
123 * <td>The column containing the date/time when the profile, if locked, should be unlocked.</td>
124 * </tr>
125 * <tr>
126 * <td><code>jspwiki.userdatabase.roleTable</code></td>
127 * <td><code>roles</code></td>
128 * <td>The table that stores user roles. When a new user is created, a new
129 * record is inserted containing user's initial role. The table will have an ID
130 * column whose name and values correspond to the contents of the user table's
131 * login name column. It will also contain a role column (see next row).</td>
132 * </tr>
133 * <tr>
134 * <td><code>jspwiki.userdatabase.role</code></td>
135 * <td><code>role</code></td>
136 * <td>The column in the role table that stores user roles. When a new user is
137 * created, this column will be populated with the value
138 * <code>Authenticated</code>. Once created, JDBCUserDatabase does not use
139 * this column again; it is provided strictly for the convenience of
140 * container-managed authentication services.</td>
141 * </tr>
142 * </table>
143 * <p>
144 * This class hashes passwords using SHA-1. All of the underying SQL commands
145 * used by this class are implemented using prepared statements, so it is immune
146 * to SQL injection attacks.
147 * </p>
148 * <p>
149 * This class is typically used in conjunction with a web container's JNDI
150 * resource factory. For example, Tomcat provides a basic
151 * JNDI factory for registering DataSources. To give JSPWiki access to the JNDI
152 * resource named by <code></code>, you would declare the datasource resource
153 * similar to this:
154 * </p>
155 * <blockquote><code>&lt;Context ...&gt;<br/>
156 *  &nbsp;&nbsp;...<br/>
157 *  &nbsp;&nbsp;&lt;Resource name="jdbc/UserDatabase" auth="Container"<br/>
158 *  &nbsp;&nbsp;&nbsp;&nbsp;type="javax.sql.DataSource" username="dbusername" password="dbpassword"<br/>
159 *  &nbsp;&nbsp;&nbsp;&nbsp;driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL:database"<br/>
160 *  &nbsp;&nbsp;&nbsp;&nbsp;maxActive="8" maxIdle="4"/&gt;<br/>
161 *  &nbsp;...<br/>
162 * &lt;/Context&gt;</code></blockquote>
163 * <p>
164 * To configure JSPWiki to use JDBC support, first create a database 
165 * with a structure similar to that provided by the HSQL and PostgreSQL 
166 * scripts in src/main/config/db.  If you have different table or column 
167 * names you can either alias them with a database view and have JSPWiki
168 * use the views, or alter the WEB-INF/jspwiki.properties file: the 
169 * jspwiki.userdatabase.* and jspwiki.groupdatabase.* properties change the
170 * names of the tables and columns that JSPWiki uses.
171 * </p>
172 * <p>
173 * A JNDI datasource (named jdbc/UserDatabase by default but can be configured 
174 * in the jspwiki.properties file) will need to be created in your servlet container.
175 * JDBC driver JARs should be added, e.g. in Tomcat's <code>lib</code>
176 * directory. For more Tomcat JNDI configuration examples, see <a
177 * href="http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html">
178 * http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html</a>.
179 * Once done, restart JSPWiki in the servlet container for it to read the 
180 * new properties and switch to JDBC authentication.
181 * </p>
182 * <p>
183 * JDBCUserDatabase commits changes as transactions if the back-end database
184 * supports them. Changes are made immediately (during the {@link #save(UserProfile)} method).
185 * </p>
186 * 
187 * @since 2.3
188 */
189public class JDBCUserDatabase extends AbstractUserDatabase {
190
191    private static final String NOTHING = "";
192
193    public static final String DEFAULT_DB_ATTRIBUTES = "attributes";
194
195    public static final String DEFAULT_DB_CREATED = "created";
196
197    public static final String DEFAULT_DB_EMAIL = "email";
198
199    public static final String DEFAULT_DB_FULL_NAME = "full_name";
200
201    public static final String DEFAULT_DB_JNDI_NAME = "jdbc/UserDatabase";
202
203    public static final String DEFAULT_DB_LOCK_EXPIRY = "lock_expiry";
204
205    public static final String DEFAULT_DB_MODIFIED = "modified";
206
207    public static final String DEFAULT_DB_ROLE = "role";
208
209    public static final String DEFAULT_DB_ROLE_TABLE = "roles";
210
211    public static final String DEFAULT_DB_TABLE = "users";
212
213    public static final String DEFAULT_DB_LOGIN_NAME = "login_name";
214
215    public static final String DEFAULT_DB_PASSWORD = "password";
216
217    public static final String DEFAULT_DB_UID = "uid";
218
219    public static final String DEFAULT_DB_WIKI_NAME = "wiki_name";
220
221    public static final String PROP_DB_ATTRIBUTES = "jspwiki.userdatabase.attributes";
222
223    public static final String PROP_DB_CREATED = "jspwiki.userdatabase.created";
224
225    public static final String PROP_DB_EMAIL = "jspwiki.userdatabase.email";
226
227    public static final String PROP_DB_FULL_NAME = "jspwiki.userdatabase.fullName";
228
229    public static final String PROP_DB_DATASOURCE = "jspwiki.userdatabase.datasource";
230
231    public static final String PROP_DB_LOCK_EXPIRY = "jspwiki.userdatabase.lockExpiry";
232
233    public static final String PROP_DB_LOGIN_NAME = "jspwiki.userdatabase.loginName";
234
235    public static final String PROP_DB_MODIFIED = "jspwiki.userdatabase.modified";
236
237    public static final String PROP_DB_PASSWORD = "jspwiki.userdatabase.password";
238
239    public static final String PROP_DB_UID = "jspwiki.userdatabase.uid";
240
241    public static final String PROP_DB_ROLE = "jspwiki.userdatabase.role";
242
243    public static final String PROP_DB_ROLE_TABLE = "jspwiki.userdatabase.roleTable";
244
245    public static final String PROP_DB_TABLE = "jspwiki.userdatabase.table";
246
247    public static final String PROP_DB_WIKI_NAME = "jspwiki.userdatabase.wikiName";
248
249    private DataSource m_ds;
250
251    private String m_deleteUserByLoginName;
252
253    private String m_deleteRoleByLoginName;
254
255    private String m_findByEmail;
256
257    private String m_findByFullName;
258
259    private String m_findByLoginName;
260
261    private String m_findByUid;
262
263    private String m_findByWikiName;
264
265    private String m_renameProfile;
266
267    private String m_renameRoles;
268
269    private String m_updateProfile;
270
271    private String m_findAll;
272
273    private String m_findRoles;
274
275    private String m_insertProfile;
276
277    private String m_insertRole;
278
279    private String m_attributes;
280
281    private String m_email;
282
283    private String m_fullName;
284
285    private String m_lockExpiry;
286
287    private String m_loginName;
288
289    private String m_password;
290
291    private String m_uid;
292    
293    private String m_wikiName;
294
295    private String m_created;
296
297    private String m_modified;
298
299    private boolean m_supportsCommits;
300
301    /**
302     * Looks up and deletes the first {@link UserProfile} in the user database
303     * that matches a profile having a given login name. If the user database
304     * does not contain a user with a matching attribute, throws a
305     * {@link NoSuchPrincipalException}. This method is intended to be atomic;
306     * results cannot be partially committed. If the commit fails, it should
307     * roll back its state appropriately. Implementing classes that persist to
308     * the file system may wish to make this method <code>synchronized</code>.
309     * 
310     * @param loginName the login name of the user profile that shall be deleted
311     */
312    @Override
313    public void deleteByLoginName( final String loginName ) throws WikiSecurityException {
314        // Get the existing user; if not found, throws NoSuchPrincipalException
315        findByLoginName( loginName );
316
317        try( final Connection conn = m_ds.getConnection() ;
318             final PreparedStatement ps1 = conn.prepareStatement( m_deleteUserByLoginName );
319             final PreparedStatement ps2 = conn.prepareStatement( m_deleteRoleByLoginName ) )
320        {
321            // Open the database connection
322            if( m_supportsCommits ) {
323                conn.setAutoCommit( false );
324            }
325
326            // Delete user record
327            ps1.setString( 1, loginName );
328            ps1.execute();
329
330            // Delete role record
331            ps2.setString( 1, loginName );
332            ps2.execute();
333
334            // Commit and close connection
335            if( m_supportsCommits ) {
336                conn.commit();
337            }
338        } catch( final SQLException e ) {
339            throw new WikiSecurityException( e.getMessage(), e );
340        }
341    }
342
343    /**
344     * @see org.apache.wiki.auth.user.UserDatabase#findByEmail(java.lang.String)
345     */
346    @Override
347    public UserProfile findByEmail( final String index ) throws NoSuchPrincipalException {
348        return findByPreparedStatement( m_findByEmail, index );
349    }
350
351    /**
352     * @see org.apache.wiki.auth.user.UserDatabase#findByFullName(java.lang.String)
353     */
354    @Override
355    public UserProfile findByFullName( final String index ) throws NoSuchPrincipalException {
356        return findByPreparedStatement( m_findByFullName, index );
357    }
358
359    /**
360     * @see org.apache.wiki.auth.user.UserDatabase#findByLoginName(java.lang.String)
361     */
362    @Override
363    public UserProfile findByLoginName( final String index ) throws NoSuchPrincipalException {
364        return findByPreparedStatement( m_findByLoginName, index );
365    }
366
367    /**
368     * @see org.apache.wiki.auth.user.UserDatabase#findByWikiName(String)
369     */
370    @Override
371    public UserProfile findByUid( final String uid ) throws NoSuchPrincipalException {
372        return findByPreparedStatement( m_findByUid, uid );
373    }
374
375    /**
376     * @see org.apache.wiki.auth.user.UserDatabase#findByWikiName(String)
377     */
378    @Override
379    public UserProfile findByWikiName( final String index ) throws NoSuchPrincipalException {
380        return findByPreparedStatement( m_findByWikiName, index );
381    }
382
383    /**
384     * Returns all WikiNames that are stored in the UserDatabase as an array of
385     * WikiPrincipal objects. If the database does not contain any profiles,
386     * this method will return a zero-length array.
387     * 
388     * @return the WikiNames
389     */
390    @Override
391    public Principal[] getWikiNames() throws WikiSecurityException {
392        final Set<Principal> principals = new HashSet<>();
393        try( final Connection conn = m_ds.getConnection();
394             final PreparedStatement ps = conn.prepareStatement( m_findAll );
395             final ResultSet rs = ps.executeQuery() ) {
396            while( rs.next() ) {
397                final String wikiName = rs.getString( m_wikiName );
398                if( StringUtils.isEmpty( wikiName ) ) {
399                    log.warn( "Detected null or empty wiki name for {} in JDBCUserDataBase. Check your user database.", rs.getString( m_loginName ) );
400                } else {
401                    final Principal principal = new WikiPrincipal( wikiName, WikiPrincipal.WIKI_NAME );
402                    principals.add( principal );
403                }
404            }
405        } catch( final SQLException e ) {
406            throw new WikiSecurityException( e.getMessage(), e );
407        }
408
409        return principals.toArray( new Principal[0] );
410    }
411
412    /**
413     * @see org.apache.wiki.auth.user.UserDatabase#initialize(org.apache.wiki.api.core.Engine, java.util.Properties)
414     */
415    @Override
416    public void initialize( final Engine engine, final Properties props ) throws NoRequiredPropertyException, WikiSecurityException {
417        final String jndiName = props.getProperty( PROP_DB_DATASOURCE, DEFAULT_DB_JNDI_NAME );
418        try {
419            final Context initCtx = new InitialContext();
420            final Context ctx = (Context) initCtx.lookup( "java:comp/env" );
421            m_ds = (DataSource) ctx.lookup( jndiName );
422
423            // Prepare the SQL selectors
424            final String userTable = props.getProperty( PROP_DB_TABLE, DEFAULT_DB_TABLE );
425            m_email = props.getProperty( PROP_DB_EMAIL, DEFAULT_DB_EMAIL );
426            m_fullName = props.getProperty( PROP_DB_FULL_NAME, DEFAULT_DB_FULL_NAME );
427            m_lockExpiry = props.getProperty( PROP_DB_LOCK_EXPIRY, DEFAULT_DB_LOCK_EXPIRY );
428            m_loginName = props.getProperty( PROP_DB_LOGIN_NAME, DEFAULT_DB_LOGIN_NAME );
429            m_password = props.getProperty( PROP_DB_PASSWORD, DEFAULT_DB_PASSWORD );
430            m_uid = props.getProperty( PROP_DB_UID, DEFAULT_DB_UID );
431            m_wikiName = props.getProperty( PROP_DB_WIKI_NAME, DEFAULT_DB_WIKI_NAME );
432            m_created = props.getProperty( PROP_DB_CREATED, DEFAULT_DB_CREATED );
433            m_modified = props.getProperty( PROP_DB_MODIFIED, DEFAULT_DB_MODIFIED );
434            m_attributes = props.getProperty( PROP_DB_ATTRIBUTES, DEFAULT_DB_ATTRIBUTES );
435
436            m_findAll = "SELECT * FROM " + userTable;
437            m_findByEmail = "SELECT * FROM " + userTable + " WHERE " + m_email + "=?";
438            m_findByFullName = "SELECT * FROM " + userTable + " WHERE " + m_fullName + "=?";
439            m_findByLoginName = "SELECT * FROM " + userTable + " WHERE " + m_loginName + "=?";
440            m_findByUid = "SELECT * FROM " + userTable + " WHERE " + m_uid + "=?";
441            m_findByWikiName = "SELECT * FROM " + userTable + " WHERE " + m_wikiName + "=?";
442
443            // The user insert SQL prepared statement
444            m_insertProfile = "INSERT INTO " + userTable + " ("
445                              + m_uid + ","
446                              + m_email + ","
447                              + m_fullName + ","
448                              + m_password + ","
449                              + m_wikiName + ","
450                              + m_modified + ","
451                              + m_loginName + ","
452                              + m_attributes + ","
453                              + m_created
454                              + ") VALUES (?,?,?,?,?,?,?,?,?)";
455            
456            // The user update SQL prepared statement
457            m_updateProfile = "UPDATE " + userTable + " SET "
458                              + m_uid + "=?,"
459                              + m_email + "=?,"
460                              + m_fullName + "=?,"
461                              + m_password + "=?,"
462                              + m_wikiName + "=?,"
463                              + m_modified + "=?,"
464                              + m_loginName + "=?,"
465                              + m_attributes + "=?,"
466                              + m_lockExpiry + "=? "
467                              + "WHERE " + m_loginName + "=?";
468
469            // Prepare the role insert SQL
470            final String roleTable = props.getProperty( PROP_DB_ROLE_TABLE, DEFAULT_DB_ROLE_TABLE );
471            final String role = props.getProperty( PROP_DB_ROLE, DEFAULT_DB_ROLE );
472            m_insertRole = "INSERT INTO " + roleTable + " (" + m_loginName + "," + role + ") VALUES (?,?)";
473            m_findRoles = "SELECT * FROM " + roleTable + " WHERE " + m_loginName + "=?";
474
475            // Prepare the user delete SQL
476            m_deleteUserByLoginName = "DELETE FROM " + userTable + " WHERE " + m_loginName + "=?";
477
478            // Prepare the role delete SQL
479            m_deleteRoleByLoginName = "DELETE FROM " + roleTable + " WHERE " + m_loginName + "=?";
480
481            // Prepare the rename user/roles SQL
482            m_renameProfile = "UPDATE " + userTable + " SET " + m_loginName + "=?," + m_modified + "=? WHERE " + m_loginName
483                              + "=?";
484            m_renameRoles = "UPDATE " + roleTable + " SET " + m_loginName + "=? WHERE " + m_loginName + "=?";
485        } catch( final NamingException e ) {
486            log.error( "JDBCUserDatabase initialization error: " + e.getMessage() );
487            throw new NoRequiredPropertyException( PROP_DB_DATASOURCE, "JDBCUserDatabase initialization error: " + e.getMessage() );
488        }
489
490        // Test connection by doing a quickie select
491        try( final Connection conn = m_ds.getConnection(); final PreparedStatement ps = conn.prepareStatement( m_findAll ) ) {
492        } catch( final SQLException e ) {
493            log.error( "DB connectivity error: " + e.getMessage() );
494            throw new WikiSecurityException("DB connectivity error: " + e.getMessage(), e );
495        }
496        log.info( "JDBCUserDatabase initialized from JNDI DataSource: {}", jndiName );
497
498        // Determine if the datasource supports commits
499        try( final Connection conn = m_ds.getConnection() ) {
500            final DatabaseMetaData dmd = conn.getMetaData();
501            if( dmd.supportsTransactions() ) {
502                m_supportsCommits = true;
503                conn.setAutoCommit( false );
504                log.info( "JDBCUserDatabase supports transactions. Good; we will use them." );
505            }
506        } catch( final SQLException e ) {
507            log.warn( "JDBCUserDatabase warning: user database doesn't seem to support transactions. Reason: {}", e.getMessage() );
508        }
509    }
510
511    /**
512     * @see org.apache.wiki.auth.user.UserDatabase#rename(String, String)
513     */
514    @Override
515    public void rename( final String loginName, final String newName ) throws DuplicateUserException, WikiSecurityException {
516        // Get the existing user; if not found, throws NoSuchPrincipalException
517        final UserProfile profile = findByLoginName( loginName );
518
519        // Get user with the proposed name; if found, it's a collision
520        try {
521            final UserProfile otherProfile = findByLoginName( newName );
522            if( otherProfile != null ) {
523                throw new DuplicateUserException( "security.error.cannot.rename", newName );
524            }
525        } catch( final NoSuchPrincipalException e ) {
526            // Good! That means it's safe to save using the new name
527        }
528
529        try( final Connection conn = m_ds.getConnection();
530             final PreparedStatement ps1 = conn.prepareStatement( m_renameProfile );
531             final PreparedStatement ps2 = conn.prepareStatement( m_renameRoles ) ) {
532            if( m_supportsCommits ) {
533                conn.setAutoCommit( false );
534            }
535
536            final Timestamp ts = new Timestamp( System.currentTimeMillis() );
537            final Date modDate = new Date( ts.getTime() );
538
539            // Change the login ID for the user record
540            ps1.setString( 1, newName );
541            ps1.setTimestamp( 2, ts );
542            ps1.setString( 3, loginName );
543            ps1.execute();
544
545            // Change the login ID for the role records
546            ps2.setString( 1, newName );
547            ps2.setString( 2, loginName );
548            ps2.execute();
549
550            // Set the profile name and mod time
551            profile.setLoginName( newName );
552            profile.setLastModified( modDate );
553
554            // Commit and close connection
555            if( m_supportsCommits ) {
556                conn.commit();
557            }
558        } catch( final SQLException e ) {
559            throw new WikiSecurityException( e.getMessage(), e );
560        }
561    }
562
563    /**
564     * @see org.apache.wiki.auth.user.UserDatabase#save(org.apache.wiki.auth.user.UserProfile)
565     */
566    @Override
567    public void save( final UserProfile profile ) throws WikiSecurityException {
568        final String initialRole = "Authenticated";
569
570        // Figure out which prepared statement to use & execute it
571        final String loginName = profile.getLoginName();
572        UserProfile existingProfile = null;
573
574        try {
575            existingProfile = findByLoginName( loginName );
576        } catch( final NoSuchPrincipalException e ) {
577            // Existing profile will be null
578        }
579
580        // Get a clean password from the passed profile.
581        // Blank password is the same as null, which means we re-use the existing one.
582        String password = profile.getPassword();
583        final String existingPassword = (existingProfile == null) ? null : existingProfile.getPassword();
584        if( NOTHING.equals( password ) ) {
585            password = null;
586        }
587        if( password == null ) {
588            password = existingPassword;
589        }
590
591        // If password changed, hash it before we save
592        if( !StringUtils.equals( password, existingPassword ) ) {
593            password = getHash( password );
594        }
595
596        try( final Connection conn = m_ds.getConnection();
597             final PreparedStatement ps1 = conn.prepareStatement( m_insertProfile );
598             final PreparedStatement ps2 = conn.prepareStatement( m_findRoles );
599             final PreparedStatement ps3 = conn.prepareStatement( m_insertRole );
600             final PreparedStatement ps4 = conn.prepareStatement( m_updateProfile ) ) {
601            if( m_supportsCommits ) {
602                conn.setAutoCommit( false );
603            }
604
605            final Timestamp ts = new Timestamp( System.currentTimeMillis() );
606            final Date modDate = new Date( ts.getTime() );
607            final java.sql.Date lockExpiry = profile.getLockExpiry() == null ? null : new java.sql.Date( profile.getLockExpiry().getTime() );
608            if( existingProfile == null ) {
609                // User is new: insert new user record
610                ps1.setString( 1, profile.getUid() );
611                ps1.setString( 2, profile.getEmail() );
612                ps1.setString( 3, profile.getFullname() );
613                ps1.setString( 4, password );
614                ps1.setString( 5, profile.getWikiName() );
615                ps1.setTimestamp( 6, ts );
616                ps1.setString( 7, profile.getLoginName() );
617                try {
618                    ps1.setString( 8, Serializer.serializeToBase64( profile.getAttributes() ) );
619                } catch ( final IOException e ) {
620                    throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e );
621                }
622                ps1.setTimestamp( 9, ts );
623                ps1.execute();
624
625                // Insert new role record
626                ps2.setString( 1, profile.getLoginName() );
627                int roles = 0;
628                try ( final ResultSet rs = ps2.executeQuery() ) {
629                    while ( rs.next() ) {
630                        roles++;
631                    }
632                }
633                
634                if( roles == 0 ) {
635                    ps3.setString( 1, profile.getLoginName() );
636                    ps3.setString( 2, initialRole );
637                    ps3.execute();
638                }
639
640                // Set the profile creation time
641                profile.setCreated( modDate );
642            } else {
643                // User exists: modify existing record
644                ps4.setString( 1, profile.getUid() );
645                ps4.setString( 2, profile.getEmail() );
646                ps4.setString( 3, profile.getFullname() );
647                ps4.setString( 4, password );
648                ps4.setString( 5, profile.getWikiName() );
649                ps4.setTimestamp( 6, ts );
650                ps4.setString( 7, profile.getLoginName() );
651                try {
652                    ps4.setString( 8, Serializer.serializeToBase64( profile.getAttributes() ) );
653                } catch ( final IOException e ) {
654                    throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e );
655                }
656                ps4.setDate( 9, lockExpiry );
657                ps4.setString( 10, profile.getLoginName() );
658                ps4.execute();
659            }
660            // Set the profile mod time
661            profile.setLastModified( modDate );
662
663            // Commit and close connection
664            if( m_supportsCommits ) {
665                conn.commit();
666            }
667        } catch( final SQLException e ) {
668            throw new WikiSecurityException( e.getMessage(), e );
669        }
670    }
671
672    /**
673     * Private method that returns the first {@link UserProfile} matching a
674     * named column's value. This method will also set the UID if it has not yet been set.     
675     * @param sql the SQL statement that should be prepared; it must have one parameter
676     * to set (either a String or a Long)
677     * @param index the value to match
678     * @return the resolved UserProfile
679     * @throws NoSuchPrincipalException problems accessing the database
680     */
681    private UserProfile findByPreparedStatement( final String sql, final Object index ) throws NoSuchPrincipalException
682    {
683        UserProfile profile = null;
684        boolean found = false;
685        boolean unique = true;
686        try( final Connection conn = m_ds.getConnection(); final PreparedStatement ps = conn.prepareStatement( sql ) ) {
687            if( m_supportsCommits ) {
688                conn.setAutoCommit( false );
689            }
690            
691            // Set the parameter to search by
692            if( index instanceof String ) {
693                ps.setString( 1, ( String )index );
694            } else if ( index instanceof Long ) {
695                ps.setLong( 1, ( Long )index );
696            } else {
697                throw new IllegalArgumentException( "Index type not recognized!" );
698            }
699            
700            // Go and get the record!
701            try( final ResultSet rs = ps.executeQuery() ) {
702                while ( rs.next() ) {
703                    if( profile != null ) {
704                        unique = false;
705                        break;
706                    }
707                    profile = newProfile();
708                    
709                    // Fetch the basic user attributes
710                    profile.setUid( rs.getString( m_uid ) );
711                    if ( profile.getUid() == null ) {
712                        profile.setUid( generateUid( this ) );
713                    }
714                    profile.setCreated( rs.getTimestamp( m_created ) );
715                    profile.setEmail( rs.getString( m_email ) );
716                    profile.setFullname( rs.getString( m_fullName ) );
717                    profile.setLastModified( rs.getTimestamp( m_modified ) );
718                    final Date lockExpiry = rs.getDate( m_lockExpiry );
719                    profile.setLockExpiry( rs.wasNull() ? null : lockExpiry );
720                    profile.setLoginName( rs.getString( m_loginName ) );
721                    profile.setPassword( rs.getString( m_password ) );
722                    
723                    // Fetch the user attributes
724                    final String rawAttributes = rs.getString( m_attributes );
725                    if ( rawAttributes != null ) {
726                        try {
727                            final Map<String,? extends Serializable> attributes = Serializer.deserializeFromBase64( rawAttributes );
728                            profile.getAttributes().putAll( attributes );
729                        } catch ( final IOException e ) {
730                            log.error( "Could not parse user profile attributes!", e );
731                        }
732                    }
733                    found = true;
734                }
735            }
736        } catch( final SQLException e ) {
737            throw new NoSuchPrincipalException( e.getMessage() );
738        }
739
740        if( !found ) {
741            throw new NoSuchPrincipalException( "Could not find profile in database!" );
742        }
743        if( !unique ) {
744            throw new NoSuchPrincipalException( "More than one profile in database!" );
745        }
746        return profile;
747    }
748
749}