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 NoSuchPrincipalException, 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 { 397 while ( rs.next() ) { 398 final String wikiName = rs.getString( m_wikiName ); 399 if( wikiName == null ) { 400 log.warn( "Detected null wiki name in XMLUserDataBase. Check your user database." ); 401 } else { 402 final Principal principal = new WikiPrincipal( wikiName, WikiPrincipal.WIKI_NAME ); 403 principals.add( principal ); 404 } 405 } 406 } catch( final SQLException e ) { 407 throw new WikiSecurityException( e.getMessage(), e ); 408 } 409 410 return principals.toArray( new Principal[0] ); 411 } 412 413 /** 414 * @see org.apache.wiki.auth.user.UserDatabase#initialize(org.apache.wiki.api.core.Engine, java.util.Properties) 415 */ 416 @Override 417 public void initialize( final Engine engine, final Properties props ) throws NoRequiredPropertyException, WikiSecurityException { 418 final String jndiName = props.getProperty( PROP_DB_DATASOURCE, DEFAULT_DB_JNDI_NAME ); 419 try { 420 final Context initCtx = new InitialContext(); 421 final Context ctx = (Context) initCtx.lookup( "java:comp/env" ); 422 m_ds = (DataSource) ctx.lookup( jndiName ); 423 424 // Prepare the SQL selectors 425 final String userTable = props.getProperty( PROP_DB_TABLE, DEFAULT_DB_TABLE ); 426 m_email = props.getProperty( PROP_DB_EMAIL, DEFAULT_DB_EMAIL ); 427 m_fullName = props.getProperty( PROP_DB_FULL_NAME, DEFAULT_DB_FULL_NAME ); 428 m_lockExpiry = props.getProperty( PROP_DB_LOCK_EXPIRY, DEFAULT_DB_LOCK_EXPIRY ); 429 m_loginName = props.getProperty( PROP_DB_LOGIN_NAME, DEFAULT_DB_LOGIN_NAME ); 430 m_password = props.getProperty( PROP_DB_PASSWORD, DEFAULT_DB_PASSWORD ); 431 m_uid = props.getProperty( PROP_DB_UID, DEFAULT_DB_UID ); 432 m_wikiName = props.getProperty( PROP_DB_WIKI_NAME, DEFAULT_DB_WIKI_NAME ); 433 m_created = props.getProperty( PROP_DB_CREATED, DEFAULT_DB_CREATED ); 434 m_modified = props.getProperty( PROP_DB_MODIFIED, DEFAULT_DB_MODIFIED ); 435 m_attributes = props.getProperty( PROP_DB_ATTRIBUTES, DEFAULT_DB_ATTRIBUTES ); 436 437 m_findAll = "SELECT * FROM " + userTable; 438 m_findByEmail = "SELECT * FROM " + userTable + " WHERE " + m_email + "=?"; 439 m_findByFullName = "SELECT * FROM " + userTable + " WHERE " + m_fullName + "=?"; 440 m_findByLoginName = "SELECT * FROM " + userTable + " WHERE " + m_loginName + "=?"; 441 m_findByUid = "SELECT * FROM " + userTable + " WHERE " + m_uid + "=?"; 442 m_findByWikiName = "SELECT * FROM " + userTable + " WHERE " + m_wikiName + "=?"; 443 444 // The user insert SQL prepared statement 445 m_insertProfile = "INSERT INTO " + userTable + " (" 446 + m_uid + "," 447 + m_email + "," 448 + m_fullName + "," 449 + m_password + "," 450 + m_wikiName + "," 451 + m_modified + "," 452 + m_loginName + "," 453 + m_attributes + "," 454 + m_created 455 + ") VALUES (?,?,?,?,?,?,?,?,?)"; 456 457 // The user update SQL prepared statement 458 m_updateProfile = "UPDATE " + userTable + " SET " 459 + m_uid + "=?," 460 + m_email + "=?," 461 + m_fullName + "=?," 462 + m_password + "=?," 463 + m_wikiName + "=?," 464 + m_modified + "=?," 465 + m_loginName + "=?," 466 + m_attributes + "=?," 467 + m_lockExpiry + "=? " 468 + "WHERE " + m_loginName + "=?"; 469 470 // Prepare the role insert SQL 471 final String roleTable = props.getProperty( PROP_DB_ROLE_TABLE, DEFAULT_DB_ROLE_TABLE ); 472 final String role = props.getProperty( PROP_DB_ROLE, DEFAULT_DB_ROLE ); 473 m_insertRole = "INSERT INTO " + roleTable + " (" + m_loginName + "," + role + ") VALUES (?,?)"; 474 m_findRoles = "SELECT * FROM " + roleTable + " WHERE " + m_loginName + "=?"; 475 476 // Prepare the user delete SQL 477 m_deleteUserByLoginName = "DELETE FROM " + userTable + " WHERE " + m_loginName + "=?"; 478 479 // Prepare the role delete SQL 480 m_deleteRoleByLoginName = "DELETE FROM " + roleTable + " WHERE " + m_loginName + "=?"; 481 482 // Prepare the rename user/roles SQL 483 m_renameProfile = "UPDATE " + userTable + " SET " + m_loginName + "=?," + m_modified + "=? WHERE " + m_loginName 484 + "=?"; 485 m_renameRoles = "UPDATE " + roleTable + " SET " + m_loginName + "=? WHERE " + m_loginName + "=?"; 486 } catch( final NamingException e ) { 487 log.error( "JDBCUserDatabase initialization error: " + e.getMessage() ); 488 throw new NoRequiredPropertyException( PROP_DB_DATASOURCE, "JDBCUserDatabase initialization error: " + e.getMessage() ); 489 } 490 491 // Test connection by doing a quickie select 492 try( final Connection conn = m_ds.getConnection(); final PreparedStatement ps = conn.prepareStatement( m_findAll ) ) { 493 } catch( final SQLException e ) { 494 log.error( "DB connectivity error: " + e.getMessage() ); 495 throw new WikiSecurityException("DB connectivity error: " + e.getMessage(), e ); 496 } 497 log.info( "JDBCUserDatabase initialized from JNDI DataSource: " + jndiName ); 498 499 // Determine if the datasource supports commits 500 try( final Connection conn = m_ds.getConnection() ) { 501 final DatabaseMetaData dmd = conn.getMetaData(); 502 if( dmd.supportsTransactions() ) { 503 m_supportsCommits = true; 504 conn.setAutoCommit( false ); 505 log.info( "JDBCUserDatabase supports transactions. Good; we will use them." ); 506 } 507 } catch( final SQLException e ) { 508 log.warn( "JDBCUserDatabase warning: user database doesn't seem to support transactions. Reason: " + e.getMessage() ); 509 } 510 } 511 512 /** 513 * @see org.apache.wiki.auth.user.UserDatabase#rename(String, String) 514 */ 515 @Override 516 public void rename( final String loginName, final String newName ) throws NoSuchPrincipalException, DuplicateUserException, WikiSecurityException { 517 // Get the existing user; if not found, throws NoSuchPrincipalException 518 final UserProfile profile = findByLoginName( loginName ); 519 520 // Get user with the proposed name; if found, it's a collision 521 try { 522 final UserProfile otherProfile = findByLoginName( newName ); 523 if( otherProfile != null ) { 524 throw new DuplicateUserException( "security.error.cannot.rename", newName ); 525 } 526 } catch( final NoSuchPrincipalException e ) { 527 // Good! That means it's safe to save using the new name 528 } 529 530 try( final Connection conn = m_ds.getConnection(); 531 final PreparedStatement ps1 = conn.prepareStatement( m_renameProfile ); 532 final PreparedStatement ps2 = conn.prepareStatement( m_renameRoles ) ) 533 { 534 if( m_supportsCommits ) { 535 conn.setAutoCommit( false ); 536 } 537 538 final Timestamp ts = new Timestamp( System.currentTimeMillis() ); 539 final Date modDate = new Date( ts.getTime() ); 540 541 // Change the login ID for the user record 542 ps1.setString( 1, newName ); 543 ps1.setTimestamp( 2, ts ); 544 ps1.setString( 3, loginName ); 545 ps1.execute(); 546 547 // Change the login ID for the role records 548 ps2.setString( 1, newName ); 549 ps2.setString( 2, loginName ); 550 ps2.execute(); 551 552 // Set the profile name and mod time 553 profile.setLoginName( newName ); 554 profile.setLastModified( modDate ); 555 556 // Commit and close connection 557 if( m_supportsCommits ) { 558 conn.commit(); 559 } 560 } catch( final SQLException e ) { 561 throw new WikiSecurityException( e.getMessage(), e ); 562 } 563 } 564 565 /** 566 * @see org.apache.wiki.auth.user.UserDatabase#save(org.apache.wiki.auth.user.UserProfile) 567 */ 568 @Override 569 public void save( final UserProfile profile ) throws WikiSecurityException { 570 final String initialRole = "Authenticated"; 571 572 // Figure out which prepared statement to use & execute it 573 final String loginName = profile.getLoginName(); 574 UserProfile existingProfile = null; 575 576 try { 577 existingProfile = findByLoginName( loginName ); 578 } catch( final NoSuchPrincipalException e ) { 579 // Existing profile will be null 580 } 581 582 // Get a clean password from the passed profile. 583 // Blank password is the same as null, which means we re-use the existing one. 584 String password = profile.getPassword(); 585 final String existingPassword = (existingProfile == null) ? null : existingProfile.getPassword(); 586 if( NOTHING.equals( password ) ) { 587 password = null; 588 } 589 if( password == null ) { 590 password = existingPassword; 591 } 592 593 // If password changed, hash it before we save 594 if( !StringUtils.equals( password, existingPassword ) ) { 595 password = getHash( password ); 596 } 597 598 try( final Connection conn = m_ds.getConnection(); 599 final PreparedStatement ps1 = conn.prepareStatement( m_insertProfile ); 600 final PreparedStatement ps2 = conn.prepareStatement( m_findRoles ); 601 final PreparedStatement ps3 = conn.prepareStatement( m_insertRole ); 602 final PreparedStatement ps4 = conn.prepareStatement( m_updateProfile ) ) 603 { 604 if( m_supportsCommits ) { 605 conn.setAutoCommit( false ); 606 } 607 608 final Timestamp ts = new Timestamp( System.currentTimeMillis() ); 609 final Date modDate = new Date( ts.getTime() ); 610 final java.sql.Date lockExpiry = profile.getLockExpiry() == null ? null : new java.sql.Date( profile.getLockExpiry().getTime() ); 611 if( existingProfile == null ) 612 { 613 // User is new: insert new user record 614 ps1.setString( 1, profile.getUid() ); 615 ps1.setString( 2, profile.getEmail() ); 616 ps1.setString( 3, profile.getFullname() ); 617 ps1.setString( 4, password ); 618 ps1.setString( 5, profile.getWikiName() ); 619 ps1.setTimestamp( 6, ts ); 620 ps1.setString( 7, profile.getLoginName() ); 621 try { 622 ps1.setString( 8, Serializer.serializeToBase64( profile.getAttributes() ) ); 623 } catch ( final IOException e ) { 624 throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e ); 625 } 626 ps1.setTimestamp( 9, ts ); 627 ps1.execute(); 628 629 // Insert new role record 630 ps2.setString( 1, profile.getLoginName() ); 631 int roles = 0; 632 try ( final ResultSet rs = ps2.executeQuery() ) { 633 while ( rs.next() ) { 634 roles++; 635 } 636 } 637 638 if( roles == 0 ) { 639 ps3.setString( 1, profile.getLoginName() ); 640 ps3.setString( 2, initialRole ); 641 ps3.execute(); 642 } 643 644 // Set the profile creation time 645 profile.setCreated( modDate ); 646 } else { 647 // User exists: modify existing record 648 ps4.setString( 1, profile.getUid() ); 649 ps4.setString( 2, profile.getEmail() ); 650 ps4.setString( 3, profile.getFullname() ); 651 ps4.setString( 4, password ); 652 ps4.setString( 5, profile.getWikiName() ); 653 ps4.setTimestamp( 6, ts ); 654 ps4.setString( 7, profile.getLoginName() ); 655 try 656 { 657 ps4.setString( 8, Serializer.serializeToBase64( profile.getAttributes() ) ); 658 } 659 catch ( final IOException e ) 660 { 661 throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e ); 662 } 663 ps4.setDate( 9, lockExpiry ); 664 ps4.setString( 10, profile.getLoginName() ); 665 ps4.execute(); 666 } 667 // Set the profile mod time 668 profile.setLastModified( modDate ); 669 670 // Commit and close connection 671 if( m_supportsCommits ) { 672 conn.commit(); 673 } 674 } 675 catch( final SQLException e ) 676 { 677 throw new WikiSecurityException( e.getMessage(), e ); 678 } 679 } 680 681 /** 682 * Private method that returns the first {@link UserProfile} matching a 683 * named column's value. This method will also set the UID if it has not yet been set. 684 * @param sql the SQL statement that should be prepared; it must have one parameter 685 * to set (either a String or a Long) 686 * @param index the value to match 687 * @return the resolved UserProfile 688 * @throws SQLException 689 */ 690 private UserProfile findByPreparedStatement( final String sql, final Object index ) throws NoSuchPrincipalException 691 { 692 UserProfile profile = null; 693 boolean found = false; 694 boolean unique = true; 695 try( final Connection conn = m_ds.getConnection(); final PreparedStatement ps = conn.prepareStatement( sql ) ) { 696 if( m_supportsCommits ) { 697 conn.setAutoCommit( false ); 698 } 699 700 // Set the parameter to search by 701 if ( index instanceof String ) { 702 ps.setString( 1, (String)index ); 703 } else if ( index instanceof Long ) { 704 ps.setLong( 1, ( (Long)index).longValue() ); 705 } else { 706 throw new IllegalArgumentException( "Index type not recognized!" ); 707 } 708 709 // Go and get the record! 710 try ( final ResultSet rs = ps.executeQuery() ) { 711 while ( rs.next() ) { 712 if( profile != null ) { 713 unique = false; 714 break; 715 } 716 profile = newProfile(); 717 718 // Fetch the basic user attributes 719 profile.setUid( rs.getString( m_uid ) ); 720 if ( profile.getUid() == null ) { 721 profile.setUid( generateUid( this ) ); 722 } 723 profile.setCreated( rs.getTimestamp( m_created ) ); 724 profile.setEmail( rs.getString( m_email ) ); 725 profile.setFullname( rs.getString( m_fullName ) ); 726 profile.setLastModified( rs.getTimestamp( m_modified ) ); 727 final Date lockExpiry = rs.getDate( m_lockExpiry ); 728 profile.setLockExpiry( rs.wasNull() ? null : lockExpiry ); 729 profile.setLoginName( rs.getString( m_loginName ) ); 730 profile.setPassword( rs.getString( m_password ) ); 731 732 // Fetch the user attributes 733 final String rawAttributes = rs.getString( m_attributes ); 734 if ( rawAttributes != null ) { 735 try { 736 final Map<String,? extends Serializable> attributes = Serializer.deserializeFromBase64( rawAttributes ); 737 profile.getAttributes().putAll( attributes ); 738 } catch ( final IOException e ) { 739 log.error( "Could not parse user profile attributes!", e ); 740 } 741 } 742 found = true; 743 } 744 } 745 } catch( final SQLException e ) { 746 throw new NoSuchPrincipalException( e.getMessage() ); 747 } 748 749 if( !found ) { 750 throw new NoSuchPrincipalException( "Could not find profile in database!" ); 751 } 752 if( !unique ) { 753 throw new NoSuchPrincipalException( "More than one profile in database!" ); 754 } 755 return profile; 756 } 757 758}