    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}