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><Context ...><br/> 156 * ...<br/> 157 * <Resource name="jdbc/UserDatabase" auth="Container"<br/> 158 * type="javax.sql.DataSource" username="dbusername" password="dbpassword"<br/> 159 * driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL:database"<br/> 160 * maxActive="8" maxIdle="4"/><br/> 161 * ...<br/> 162 * </Context></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}