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