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