001/* 002 * Copyright 2016 Anyware Services 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016package org.ametys.plugins.core.impl.user.directory; 017 018import java.sql.Connection; 019import java.sql.PreparedStatement; 020import java.sql.ResultSet; 021import java.sql.SQLException; 022import java.util.ArrayList; 023import java.util.Arrays; 024import java.util.Collection; 025import java.util.Collections; 026import java.util.HashMap; 027import java.util.LinkedHashMap; 028import java.util.List; 029import java.util.Map; 030 031import org.apache.avalon.framework.component.Component; 032import org.apache.avalon.framework.context.Context; 033import org.apache.avalon.framework.context.ContextException; 034import org.apache.avalon.framework.context.Contextualizable; 035import org.apache.avalon.framework.service.ServiceException; 036import org.apache.avalon.framework.service.ServiceManager; 037import org.apache.avalon.framework.service.Serviceable; 038import org.apache.commons.codec.digest.DigestUtils; 039import org.apache.commons.lang.RandomStringUtils; 040import org.apache.commons.lang.StringUtils; 041import org.apache.commons.lang3.ArrayUtils; 042import org.apache.excalibur.source.SourceResolver; 043 044import org.ametys.core.ObservationConstants; 045import org.ametys.core.datasource.ConnectionHelper; 046import org.ametys.core.observation.Event; 047import org.ametys.core.observation.ObservationManager; 048import org.ametys.core.script.SQLScriptHelper; 049import org.ametys.core.user.CurrentUserProvider; 050import org.ametys.core.user.InvalidModificationException; 051import org.ametys.core.user.User; 052import org.ametys.core.user.UserIdentity; 053import org.ametys.core.user.directory.ModifiableUserDirectory; 054import org.ametys.core.user.directory.NotUniqueUserException; 055import org.ametys.core.util.CachingComponent; 056import org.ametys.core.util.mail.SendMailHelper; 057import org.ametys.plugins.core.jdbc.JdbcParameterTypeExtensionPoint; 058import org.ametys.runtime.i18n.I18nizableText; 059import org.ametys.runtime.model.ElementDefinition; 060import org.ametys.runtime.model.ModelHelper; 061import org.ametys.runtime.model.ModelItem; 062import org.ametys.runtime.model.View; 063import org.ametys.runtime.model.type.ModelItemType; 064import org.ametys.runtime.model.type.ModelItemTypeConstants; 065import org.ametys.runtime.parameter.DefaultValidator; 066import org.ametys.runtime.parameter.Errors; 067import org.ametys.runtime.parameter.Validator; 068import org.ametys.runtime.plugin.PluginsManager; 069import org.ametys.runtime.plugin.component.PluginAware; 070 071/** 072 * Use a jdbc driver for getting the list of users, modifying them and also 073 * authenticate them.<br> 074 * Passwords need to be encrypted with MD5 and encoded in base64.<br> 075 */ 076public class JdbcUserDirectory extends CachingComponent<String, User> implements ModifiableUserDirectory, Component, Serviceable, Contextualizable, PluginAware 077{ 078 /** The base plugin (for i18n key) */ 079 protected static final String BASE_PLUGIN_NAME = "core"; 080 081 static final String[] __COLUMNS = new String[] {"login", "password", "firstname", "lastname", "email"}; 082 static final String[] __ORDERBY_COLUMNS = new String[] {"lastname", "firstname"}; 083 084 /** Name of the parameter holding the datasource id */ 085 private static final String __DATASOURCE_PARAM_NAME = "runtime.users.jdbc.datasource"; 086 /** Name of the parameter holding the table users' name */ 087 private static final String __USERS_TABLE_PARAM_NAME = "runtime.users.jdbc.table"; 088 089 private static final String __COLUMN_LOGIN = "login"; 090 private static final String __COLUMN_PASSWORD = "password"; 091 private static final String __COLUMN_FIRSTNAME = "firstname"; 092 private static final String __COLUMN_LASTNAME = "lastname"; 093 private static final String __COLUMN_EMAIL = "email"; 094 private static final String __COLUMN_SALT = "salt"; 095 096 private static final String __CACKE_KEY_PREFIX_FOR_LOGIN = "login$"; 097 private static final String __CACKE_KEY_PREFIX_FOR_EMAIL = "email$"; 098 099 /** The identifier of data source */ 100 protected String _dataSourceId; 101 /** The name of users' SQL table */ 102 protected String _userTableName; 103 104 /** Model */ 105 protected Map<String, ElementDefinition> _model; 106 107 /** Plugin name */ 108 protected String _pluginName; 109 110 /** The avalon service manager */ 111 protected ServiceManager _manager; 112 113 /** The avalon context */ 114 protected Context _context; 115 116 /** The cocoon source resolver */ 117 protected SourceResolver _sourceResolver; 118 119 private ObservationManager _observationManager; 120 private CurrentUserProvider _currentUserProvider; 121 122 private String _udModelId; 123 private Map<String, Object> _paramValues; 124 private String _populationId; 125 126 private String _label; 127 128 private String _id; 129 130 private boolean _lazyInitialized; 131 132 private JdbcParameterTypeExtensionPoint _jdbcParameterTypeExtensionPoint; 133 134 private View _view; 135 136 @Override 137 public void setPluginInfo(String pluginName, String featureName, String id) 138 { 139 _pluginName = pluginName; 140 } 141 142 @Override 143 public void contextualize(Context context) throws ContextException 144 { 145 _context = context; 146 } 147 148 @Override 149 public void service(ServiceManager manager) throws ServiceException 150 { 151 _manager = manager; 152 _sourceResolver = (SourceResolver) manager.lookup(SourceResolver.ROLE); 153 _jdbcParameterTypeExtensionPoint = (JdbcParameterTypeExtensionPoint) manager.lookup(JdbcParameterTypeExtensionPoint.ROLE); 154 } 155 156 public String getId() 157 { 158 return _id; 159 } 160 161 public String getFamilyId() 162 { 163 return JdbcUserDirectory.class.getName(); 164 } 165 166 public String getLabel() 167 { 168 return _label; 169 } 170 171 @Override 172 public void init(String id, String udModelId, Map<String, Object> paramValues, String label) 173 { 174 _id = id; 175 _udModelId = udModelId; 176 _paramValues = paramValues; 177 _label = label; 178 179 _userTableName = (String) paramValues.get(__USERS_TABLE_PARAM_NAME); 180 _dataSourceId = (String) paramValues.get(__DATASOURCE_PARAM_NAME); 181 182 _initModelParameters(); 183 } 184 185 private void _initModelParameters() 186 { 187 _model = new LinkedHashMap<>(); 188 189 I18nizableText invalidLoginText = new I18nizableText("plugin." + BASE_PLUGIN_NAME, "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_INVALID"); 190 Validator loginValidator = new DefaultValidator("^[a-zA-Z0-9_\\-\\.@]{3,64}$", invalidLoginText, true); 191 _initModelParameter(__COLUMN_LOGIN, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_LOGIN_DESCRIPTION", loginValidator); 192 193 _initModelParameter(__COLUMN_PASSWORD, ModelItemTypeConstants.PASSWORD_ELEMENT_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_PASSWORD_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_PASSWORD_DESCRIPTION", null); 194 195 _initModelParameter(__COLUMN_FIRSTNAME, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_FIRSTNAME_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_FIRSTNAME_DESCRIPTION", null); 196 197 _initModelParameter(__COLUMN_LASTNAME, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_LASTNAME_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_LASTNAME_DESCRIPTION", null); 198 199 I18nizableText invalidEmailText = new I18nizableText("plugin." + BASE_PLUGIN_NAME, "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_INVALID"); 200 Validator emailValidator = new DefaultValidator(SendMailHelper.EMAIL_VALIDATION_REGEXP, invalidEmailText, false); 201 _initModelParameter(__COLUMN_EMAIL, ModelItemTypeConstants.STRING_TYPE_ID, "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_LABEL", "PLUGINS_CORE_USERS_JDBC_FIELD_EMAIL_DESCRIPTION", emailValidator); 202 } 203 204 private void _initModelParameter(String name, String parameterType, String labelKey, String descriptionKey, Validator validator) 205 { 206 ModelItemType modelItemType = _jdbcParameterTypeExtensionPoint.getExtension(parameterType); 207 208 ElementDefinition parameter = new ElementDefinition<>(); 209 parameter.setPluginName(BASE_PLUGIN_NAME); 210 parameter.setType(modelItemType); 211 parameter.setName(name); 212 parameter.setLabel(new I18nizableText("plugin." + BASE_PLUGIN_NAME, labelKey)); 213 parameter.setDescription(new I18nizableText("plugin." + BASE_PLUGIN_NAME, descriptionKey)); 214 parameter.setValidator(validator != null ? validator : new DefaultValidator(null, true)); 215 216 _model.put(name, parameter); 217 } 218 219 /** 220 * Lazy lookup the {@link ObservationManager} 221 * @return the observation manager 222 */ 223 protected ObservationManager getObservationManager() 224 { 225 if (_observationManager == null) 226 { 227 try 228 { 229 _observationManager = (ObservationManager) _manager.lookup(ObservationManager.ROLE); 230 } 231 catch (ServiceException e) 232 { 233 // We may be in safe mode 234 if (PluginsManager.getInstance().isSafeMode()) 235 { 236 getLogger().debug("Unable to lookup ObservationManager component in safe mode", e); 237 } 238 else 239 { 240 getLogger().error("Unable to lookup ObservationManager component", e); 241 } 242 } 243 } 244 return _observationManager; 245 } 246 247 /** 248 * Lazy lookup the {@link CurrentUserProvider} 249 * @return the current user provider 250 */ 251 protected CurrentUserProvider getCurrentUserProvider() 252 { 253 if (_currentUserProvider == null) 254 { 255 try 256 { 257 _currentUserProvider = (CurrentUserProvider) _manager.lookup(CurrentUserProvider.ROLE); 258 } 259 catch (ServiceException e) 260 { 261 throw new RuntimeException("Unable to lookup CurrentUserProvider component", e); 262 } 263 } 264 return _currentUserProvider; 265 } 266 267 /** 268 * Get the connection to the database 269 * @return the SQL connection 270 */ 271 @SuppressWarnings("unchecked") 272 protected Connection getSQLConnection() 273 { 274 Connection connection = ConnectionHelper.getConnection(_dataSourceId); 275 276 if (!_lazyInitialized) 277 { 278 try 279 { 280 SQLScriptHelper.createTableIfNotExists(connection, _userTableName, "plugin:core://scripts/%s/jdbc_users.template.sql", _sourceResolver, 281 (Map) ArrayUtils.toMap(new String[][] {{"%TABLENAME%", _userTableName}})); 282 } 283 catch (Exception e) 284 { 285 getLogger().error("The tables requires by the " + this.getClass().getName() + " could not be created. A degraded behavior will occur", e); 286 } 287 288 _lazyInitialized = true; 289 } 290 291 return connection; 292 } 293 294 @Override 295 public void setPopulationId(String populationId) 296 { 297 _populationId = populationId; 298 } 299 300 @Override 301 public String getPopulationId() 302 { 303 return _populationId; 304 } 305 306 @Override 307 public Map<String, Object> getParameterValues() 308 { 309 return _paramValues; 310 } 311 312 @Override 313 public String getUserDirectoryModelId() 314 { 315 return _udModelId; 316 } 317 318 @SuppressWarnings("unchecked") 319 @Override 320 public Collection<User> getUsers() 321 { 322 return getUsers(Integer.MAX_VALUE, 0, Collections.EMPTY_MAP); 323 } 324 325 @Override 326 public List<User> getUsers(int count, int offset, Map<String, Object> parameters) 327 { 328 String pattern = StringUtils.defaultIfEmpty((String) parameters.get("pattern"), null); 329 int boundedCount = count >= 0 ? count : Integer.MAX_VALUE; 330 int boundedOffset = offset >= 0 ? offset : 0; 331 332 SelectUsersJdbcQueryExecutor<List<User>> queryExecutor = new SelectUsersJdbcQueryExecutor<List<User>>(pattern, boundedCount, boundedOffset) 333 { 334 @Override 335 protected List<User> processResultSet(ResultSet rs) throws SQLException 336 { 337 return _getUsersProcessResultSet(rs, __CACKE_KEY_PREFIX_FOR_LOGIN); 338 } 339 }; 340 341 return queryExecutor.run(); 342 } 343 344 @Override 345 public User getUser(String login) 346 { 347 if (isCacheEnabled()) 348 { 349 User user = getObjectFromCache(__CACKE_KEY_PREFIX_FOR_LOGIN + login); 350 if (user != null) 351 { 352 return user; 353 } 354 } 355 356 SelectUserJdbcQueryExecutor<User> queryExecutor = new SelectUserJdbcQueryExecutor<User>(login) 357 { 358 @Override 359 protected User processResultSet(ResultSet rs) throws SQLException 360 { 361 return _getUserProcessResultSet(rs, login, __CACKE_KEY_PREFIX_FOR_LOGIN); 362 } 363 }; 364 365 return queryExecutor.run(); 366 } 367 368 @Override 369 public User getUserByEmail(String email) throws NotUniqueUserException 370 { 371 if (isCacheEnabled()) 372 { 373 User user = getObjectFromCache(__CACKE_KEY_PREFIX_FOR_EMAIL + email); 374 if (user != null) 375 { 376 return user; 377 } 378 } 379 380 SelectUserJdbcQueryExecutor<List<User>> queryExecutor = new SelectUserJdbcQueryExecutor<List<User>>(email, __COLUMN_EMAIL) 381 { 382 @Override 383 protected List<User> processResultSet(ResultSet rs) throws SQLException 384 { 385 return _getUsersProcessResultSet(rs, __CACKE_KEY_PREFIX_FOR_EMAIL); 386 } 387 }; 388 389 List<User> users = queryExecutor.run(); 390 if (users.size() == 1) 391 { 392 return users.get(0); 393 } 394 else if (users.isEmpty()) 395 { 396 return null; 397 } 398 else 399 { 400 throw new NotUniqueUserException("Find " + users.size() + " users matching the email " + email); 401 } 402 403 } 404 405 @Override 406 public boolean checkCredentials(String login, String password) 407 { 408 boolean updateNeeded = false; 409 410 Connection con = null; 411 PreparedStatement stmt = null; 412 ResultSet rs = null; 413 try 414 { 415 // Connect to the database with connection pool 416 con = getSQLConnection(); 417 418 // Build request for authenticating the user 419 String sql = "SELECT " + __COLUMN_LOGIN + ", " + __COLUMN_PASSWORD + ", " + __COLUMN_SALT + " FROM " + _userTableName + " WHERE " + __COLUMN_LOGIN + " = ?"; 420 if (getLogger().isDebugEnabled()) 421 { 422 getLogger().debug(sql); 423 } 424 425 stmt = con.prepareStatement(sql); 426 stmt.setString(1, login); 427 428 // Do the request 429 rs = stmt.executeQuery(); 430 431 if (rs.next()) 432 { 433 String storedPassword = rs.getString(__COLUMN_PASSWORD); 434 String salt = rs.getString(__COLUMN_SALT); 435 436 if (salt == null && _isMD5Encrypted(storedPassword)) 437 { 438 String encryptedPassword = org.ametys.core.util.StringUtils.md5Base64(password); 439 440 if (encryptedPassword == null) 441 { 442 getLogger().error("Unable to encrypt password"); 443 return false; 444 } 445 446 if (storedPassword.equals(encryptedPassword)) 447 { 448 updateNeeded = true; 449 return true; 450 } 451 452 return false; 453 } 454 else 455 { 456 String encryptedPassword = DigestUtils.sha512Hex(salt + password); 457 458 if (encryptedPassword == null) 459 { 460 getLogger().error("Unable to encrypt password"); 461 return false; 462 } 463 464 return storedPassword.equalsIgnoreCase(encryptedPassword); 465 } 466 } 467 468 return false; 469 } 470 catch (SQLException e) 471 { 472 getLogger().error("Error during the connection to the database", e); 473 return false; 474 } 475 finally 476 { 477 // Close connections 478 ConnectionHelper.cleanup(rs); 479 ConnectionHelper.cleanup(stmt); 480 ConnectionHelper.cleanup(con); 481 482 if (updateNeeded) 483 { 484 _updateToSSHAPassword(login, password); 485 } 486 } 487 } 488 489 @Override 490 public void add(Map<String, String> userInformation) throws InvalidModificationException 491 { 492 Connection con = null; 493 PreparedStatement stmt = null; 494 495 if (getLogger().isDebugEnabled()) 496 { 497 getLogger().debug("Starting adding a new user"); 498 } 499 500 // Check the presence of all parameters 501 Map<String, Errors> errorFields = validate(userInformation); 502 503 if (errorFields.size() > 0) 504 { 505 throw new InvalidModificationException("The creation of user failed because of invalid parameter values", errorFields); 506 } 507 508 String login = userInformation.get("login"); 509 510 try 511 { 512 // Connect to the database with connection pool 513 con = getSQLConnection(); 514 515 stmt = createAddStatement(con, userInformation); 516 517 // Do the request and check the result 518 if (stmt.executeUpdate() != 1) 519 { 520 if (getLogger().isWarnEnabled()) 521 { 522 getLogger().warn("The user to remove '" + login + "' was not removed."); 523 } 524 throw new InvalidModificationException("Error no user inserted"); 525 } 526 527 if (getObservationManager() != null) 528 { 529 // Observation manager can be null in safe mode 530 Map<String, Object> eventParams = new HashMap<>(); 531 eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, _populationId)); 532 getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_ADDED, getCurrentUserProvider().getUser(), eventParams)); 533 } 534 } 535 catch (SQLException e) 536 { 537 getLogger().error("Error communication with database", e); 538 throw new InvalidModificationException("Error during the communication with the database", e); 539 } 540 finally 541 { 542 // Close connections 543 ConnectionHelper.cleanup(stmt); 544 ConnectionHelper.cleanup(con); 545 } 546 547 } 548 549 @Override 550 public Map<String, Errors> validate(Map<String, String> userInformation) 551 { 552 Map<String, Errors> errorFields = new HashMap<>(); 553 for (ElementDefinition parameter : _model.values()) 554 { 555 Object typedValue = parameter.getType().castValue(userInformation.get(parameter.getName())); 556 Errors errors = new Errors(); 557 558 List<I18nizableText> errorsList = ModelHelper.validateValue(parameter, typedValue); 559 for (I18nizableText error : errorsList) 560 { 561 errors.addError(error); 562 } 563 564 if (errors.hasErrors()) 565 { 566 if (getLogger().isDebugEnabled()) 567 { 568 getLogger().debug("The field '" + parameter.getName() + "' is not valid"); 569 } 570 errorFields.put(parameter.getName(), errors); 571 } 572 } 573 return errorFields; 574 } 575 576 @Override 577 public void update(Map<String, String> userInformation) throws InvalidModificationException 578 { 579 Connection con = null; 580 PreparedStatement stmt = null; 581 582 Map<String, Errors> errorFields = new HashMap<>(); 583 for (String id : userInformation.keySet()) 584 { 585 ElementDefinition parameter = _model.get(id); 586 if (parameter != null) 587 { 588 Object typedValue = parameter.getType().castValue(userInformation.get(parameter.getName())); 589 List<I18nizableText> errorsList = ModelHelper.validateValue(parameter, typedValue); 590 Errors errors = new Errors(); 591 for (I18nizableText error : errorsList) 592 { 593 errors.addError(error); 594 } 595 596 if (errors.hasErrors()) 597 { 598 if (getLogger().isDebugEnabled()) 599 { 600 getLogger().debug("The field '" + parameter.getName() + "' is not valid"); 601 } 602 errorFields.put(parameter.getName(), errors); 603 } 604 } 605 } 606 607 if (errorFields.size() > 0) 608 { 609 throw new InvalidModificationException("The modification of user failed because of invalid parameter values", errorFields); 610 } 611 612 String login = userInformation.get("login"); 613 if (StringUtils.isEmpty(login)) 614 { 615 throw new InvalidModificationException("Cannot update without login information"); 616 } 617 618 try 619 { 620 // Connect to the database with connection pool 621 con = getSQLConnection(); 622 623 stmt = createModifyStatement(con, userInformation); 624 625 // Do the request 626 if (stmt.executeUpdate() != 1) 627 { 628 throw new InvalidModificationException("Error. User '" + login + "' not updated"); 629 } 630 631 if (getObservationManager() != null) 632 { 633 // Observation manager can be null in safe mode 634 Map<String, Object> eventParams = new HashMap<>(); 635 eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, _populationId)); 636 getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_UPDATED, getCurrentUserProvider().getUser(), eventParams)); 637 } 638 639 if (isCacheEnabled()) 640 { 641 removeObjectFromCache(__CACKE_KEY_PREFIX_FOR_LOGIN + login); 642 } 643 } 644 catch (SQLException e) 645 { 646 getLogger().error("Error communication with database", e); 647 throw new InvalidModificationException("Error communication with database", e); 648 } 649 finally 650 { 651 // Close connections 652 ConnectionHelper.cleanup(stmt); 653 ConnectionHelper.cleanup(con); 654 } 655 } 656 657 @Override 658 public void remove(String login) throws InvalidModificationException 659 { 660 Connection con = null; 661 PreparedStatement stmt = null; 662 663 try 664 { 665 // Connect to the database with connection pool 666 con = getSQLConnection(); 667 668 // Build request for removing the user 669 String sqlRequest = "DELETE FROM " + _userTableName + " WHERE " + __COLUMN_LOGIN + " = ?"; 670 if (getLogger().isDebugEnabled()) 671 { 672 getLogger().debug(sqlRequest); 673 } 674 675 stmt = con.prepareStatement(sqlRequest); 676 stmt.setString(1, login); 677 678 // Do the request and check the result 679 if (stmt.executeUpdate() != 1) 680 { 681 throw new InvalidModificationException("Error user was not deleted"); 682 } 683 684 if (getObservationManager() != null) 685 { 686 // Observation manager can be null in safe mode 687 Map<String, Object> eventParams = new HashMap<>(); 688 eventParams.put(ObservationConstants.ARGS_USER, new UserIdentity(login, _populationId)); 689 getObservationManager().notify(new Event(ObservationConstants.EVENT_USER_DELETED, getCurrentUserProvider().getUser(), eventParams)); 690 } 691 692 if (isCacheEnabled()) 693 { 694 removeObjectFromCache(__CACKE_KEY_PREFIX_FOR_LOGIN + login); 695 } 696 } 697 catch (SQLException e) 698 { 699 throw new InvalidModificationException("Error during the communication with the database", e); 700 } 701 finally 702 { 703 // Close connections 704 ConnectionHelper.cleanup(stmt); 705 ConnectionHelper.cleanup(con); 706 } 707 } 708 709 public Collection< ? extends ModelItem> getModelItems() 710 { 711 return Collections.unmodifiableCollection(_model.values()); 712 } 713 714 /** 715 * Get the mandatory predicate to use when querying users by pattern. 716 * @param pattern The pattern to match, can be null. 717 * @return a {@link JdbcPredicate}, can be null. 718 */ 719 protected JdbcPredicate _getMandatoryPredicate(String pattern) 720 { 721 return null; 722 } 723 724 /** 725 * Get the pattern to match user login 726 * @param pattern the pattern 727 * @return the pattern to match user login 728 */ 729 protected String _getPatternToMatch(String pattern) 730 { 731 if (pattern != null) 732 { 733 return "%" + pattern + "%"; 734 } 735 return null; 736 } 737 738 /** 739 * Determines if the password is encrypted with MD5 algorithm 740 * @param password The encrypted password 741 * @return true if the password is encrypted with MD5 algorithm 742 */ 743 protected boolean _isMD5Encrypted(String password) 744 { 745 return password.length() == 24; 746 } 747 748 /** 749 * Generate a salt key and encrypt the password with the sha2 algorithm 750 * @param login The user login 751 * @param password The user pasword 752 */ 753 protected void _updateToSSHAPassword(String login, String password) 754 { 755 Connection con = null; 756 PreparedStatement stmt = null; 757 ResultSet rs = null; 758 759 try 760 { 761 con = getSQLConnection(); 762 763 String generateSaltKey = RandomStringUtils.randomAlphanumeric(48); 764 String newEncryptedPassword = DigestUtils.sha512Hex(generateSaltKey + password); 765 766 String sqlUpdate = "UPDATE " + _userTableName + " SET " + __COLUMN_PASSWORD + " = ?, " + __COLUMN_SALT + " = ? WHERE " + __COLUMN_LOGIN + " = ?"; 767 if (getLogger().isDebugEnabled()) 768 { 769 getLogger().debug(sqlUpdate); 770 } 771 772 stmt = con.prepareStatement(sqlUpdate); 773 stmt.setString(1, newEncryptedPassword); 774 stmt.setString(2, generateSaltKey); 775 stmt.setString(3, login); 776 777 stmt.execute(); 778 } 779 catch (SQLException e) 780 { 781 getLogger().error("Error during the connection to the database", e); 782 } 783 finally 784 { 785 // Close connections 786 ConnectionHelper.cleanup(rs); 787 ConnectionHelper.cleanup(stmt); 788 ConnectionHelper.cleanup(con); 789 } 790 } 791 792 /** 793 * Create Add statement 794 * @param con The sql connection 795 * @param userInformation the user informations 796 * @return The statement 797 * @throws SQLException if an error occurred 798 */ 799 protected PreparedStatement createAddStatement(Connection con, Map<String, String> userInformation) throws SQLException 800 { 801 String beginClause = "INSERT INTO " + _userTableName + " ("; 802 String middleClause = ") VALUES ("; 803 String endClause = ")"; 804 805 StringBuffer intoClause = new StringBuffer(); 806 StringBuffer valueClause = new StringBuffer(); 807 808 intoClause.append(__COLUMN_SALT); 809 valueClause.append("?"); 810 811 for (String column : __COLUMNS) 812 { 813 intoClause.append(", " + column); 814 valueClause.append(", ?"); 815 } 816 817 String sqlRequest = beginClause + intoClause.toString() + middleClause + valueClause + endClause; 818 if (getLogger().isDebugEnabled()) 819 { 820 getLogger().debug(sqlRequest); 821 } 822 823 PreparedStatement stmt = con.prepareStatement(sqlRequest); 824 825 int i = 1; 826 boolean clearText = !userInformation.containsKey("clearText") || !"false".equals(userInformation.get("clearText")) || !userInformation.containsKey(__COLUMN_SALT); 827 String generatedSaltKey = clearText ? RandomStringUtils.randomAlphanumeric(48) : userInformation.get(__COLUMN_SALT); 828 829 stmt.setString(i++, generatedSaltKey); 830 831 for (String column : __COLUMNS) 832 { 833 if ("password".equals(column)) 834 { 835 String encryptedPassword; 836 if (clearText) 837 { 838 encryptedPassword = DigestUtils.sha512Hex(generatedSaltKey + userInformation.get(column)); 839 if (encryptedPassword == null) 840 { 841 String message = "Cannot encode password"; 842 getLogger().error(message); 843 throw new SQLException(message); 844 } 845 } 846 else 847 { 848 encryptedPassword = userInformation.get(column); 849 } 850 stmt.setString(i++, encryptedPassword); 851 } 852 else 853 { 854 stmt.setString(i++, userInformation.get(column)); 855 } 856 } 857 858 return stmt; 859 } 860 861 /** 862 * Create statement to update database 863 * @param con The sql connection 864 * @param userInformation The user information 865 * @return The statement 866 * @throws SQLException if an error occurred 867 */ 868 protected PreparedStatement createModifyStatement(Connection con, Map<String, String> userInformation) throws SQLException 869 { 870 // Build request for editing the user 871 String beginClause = "UPDATE " + _userTableName + " SET "; 872 String endClause = " WHERE " + __COLUMN_LOGIN + " = ?"; 873 874 StringBuffer columnNames = new StringBuffer(""); 875 876 boolean passwordUpdate = false; 877 for (String id : userInformation.keySet()) 878 { 879 if (ArrayUtils.contains(__COLUMNS, id) && !"login".equals(id) && !("password".equals(id) && (userInformation.get(id) == null))) 880 { 881 if ("password".equals(id)) 882 { 883 passwordUpdate = true; 884 } 885 886 if (columnNames.length() > 0) 887 { 888 columnNames.append(", "); 889 } 890 columnNames.append(id + " = ?"); 891 } 892 } 893 894 if (passwordUpdate) 895 { 896 columnNames.append(", " + __COLUMN_SALT + " = ?"); 897 } 898 899 String sqlRequest = beginClause + columnNames.toString() + endClause; 900 if (getLogger().isDebugEnabled()) 901 { 902 getLogger().debug(sqlRequest); 903 } 904 905 PreparedStatement stmt = con.prepareStatement(sqlRequest); 906 _fillModifyStatement(stmt, userInformation); 907 908 return stmt; 909 } 910 911 /** 912 * Fill the statement with the user informations 913 * @param stmt The statement of the sql request 914 * @param userInformation the user informations 915 * @throws SQLException if an error occurred 916 */ 917 protected void _fillModifyStatement(PreparedStatement stmt, Map<String, String> userInformation) throws SQLException 918 { 919 int index = 1; 920 921 boolean clearText = !userInformation.containsKey("clearText") || !"false".equals(userInformation.get("clearText")) || !userInformation.containsKey(__COLUMN_SALT); 922 String generatedSaltKey = clearText ? RandomStringUtils.randomAlphanumeric(48) : userInformation.get(__COLUMN_SALT); 923 924 boolean passwordUpdate = false; 925 926 for (String id : userInformation.keySet()) 927 { 928 if (ArrayUtils.contains(__COLUMNS, id) && !"login".equals(id)) 929 { 930 if ("password".equals(id)) 931 { 932 if (userInformation.get(id) != null) 933 { 934 String encryptedPassword; 935 if (clearText) 936 { 937 encryptedPassword = DigestUtils.sha512Hex(generatedSaltKey + userInformation.get(id)); 938 if (encryptedPassword == null) 939 { 940 String message = "Cannot encrypt password"; 941 getLogger().error(message); 942 throw new SQLException(message); 943 } 944 } 945 else 946 { 947 encryptedPassword = userInformation.get(id); 948 } 949 stmt.setString(index++, encryptedPassword); 950 passwordUpdate = true; 951 } 952 } 953 else 954 { 955 stmt.setString(index++, userInformation.get(id)); 956 } 957 } 958 } 959 960 if (passwordUpdate) 961 { 962 stmt.setString(index++, generatedSaltKey); 963 } 964 965 stmt.setString(index++, userInformation.get("login")); 966 } 967 968 /** 969 * Populate the user list with the result set 970 * @param rs The result set 971 * @param cachePrefix the prefix to use for cache 972 * @return The user list 973 * @throws SQLException If an SQL exception occurs 974 */ 975 protected List<User> _getUsersProcessResultSet(ResultSet rs, String cachePrefix) throws SQLException 976 { 977 List<User> users = new ArrayList<>(); 978 979 while (rs.next()) 980 { 981 User user = null; 982 983 // Try to get in cache 984 if (isCacheEnabled()) 985 { 986 String login = rs.getString(__COLUMN_LOGIN); 987 user = getObjectFromCache(__CACKE_KEY_PREFIX_FOR_LOGIN + login); 988 } 989 990 // Or create from result set 991 if (user == null) 992 { 993 user = _createUserFromResultSet(rs); 994 995 if (isCacheEnabled()) 996 { 997 addObjectInCache(cachePrefix + user.getIdentity().getLogin(), user); 998 } 999 } 1000 1001 users.add(user); 1002 } 1003 1004 return users; 1005 } 1006 1007 /** 1008 * Create the user implementation from the result set of the request 1009 * 1010 * @param rs The result set where you can use get methods 1011 * @return The user refleting the current cursor position in the result set 1012 * @throws SQLException if an error occurred 1013 */ 1014 protected User _createUserFromResultSet(ResultSet rs) throws SQLException 1015 { 1016 String login = rs.getString(__COLUMN_LOGIN); 1017 String lastName = rs.getString(__COLUMN_LASTNAME); 1018 String firstName = rs.getString(__COLUMN_FIRSTNAME); 1019 String email = rs.getString(__COLUMN_EMAIL); 1020 1021 return new User(new UserIdentity(login, _populationId), lastName, firstName, email, this); 1022 } 1023 1024 /** 1025 * Retrieve an user from a result set 1026 * @param rs The result set 1027 * @param login The user login 1028 * @param cachePrefix the prefix to use for cache 1029 * @return The retrieved user or null if not found 1030 * @throws SQLException If an SQL Exception occurs 1031 */ 1032 protected User _getUserProcessResultSet(ResultSet rs, String login, String cachePrefix) throws SQLException 1033 { 1034 if (rs.next()) 1035 { 1036 // Retrieve information of the user 1037 User user = _createUserFromResultSet(rs); 1038 1039 if (isCacheEnabled()) 1040 { 1041 addObjectInCache(cachePrefix + login, user); 1042 } 1043 1044 return user; 1045 } 1046 else 1047 { 1048 // no user with this login in the database 1049 return null; 1050 } 1051 } 1052 1053 @Override 1054 public View getView() 1055 { 1056 if (_view == null) 1057 { 1058 _view = new View(this, __COLUMNS); 1059 } 1060 return _view; 1061 } 1062 1063 // ------------------------------ 1064 // INNER CLASSE 1065 // ------------------------------ 1066 /** 1067 * An internal query executor. 1068 * @param <T> The type of the queried object 1069 */ 1070 protected abstract class AbstractJdbcQueryExecutor<T> 1071 { 1072 /** 1073 * Main function, run the query process. Will not throw exception. Use 1074 * runWithException to catch non SQL exception thrown by 1075 * {@link #processResultSet(ResultSet)} 1076 * @return The queried object or null 1077 */ 1078 @SuppressWarnings("synthetic-access") 1079 public T run() 1080 { 1081 try 1082 { 1083 return runWithException(); 1084 } 1085 catch (Exception e) 1086 { 1087 getLogger().error("Exception during a query execution", e); 1088 throw new RuntimeException("Exception during a query execution", e); 1089 } 1090 } 1091 1092 /** 1093 * Main function, run the query process. 1094 * @return The queried object or null 1095 * @throws Exception All non SQLException will be thrown 1096 */ 1097 @SuppressWarnings("synthetic-access") 1098 public T runWithException() throws Exception 1099 { 1100 Connection connection = null; 1101 PreparedStatement stmt = null; 1102 ResultSet rs = null; 1103 1104 try 1105 { 1106 connection = getSQLConnection(); 1107 1108 String sql = getSqlQuery(connection); 1109 1110 if (getLogger().isDebugEnabled()) 1111 { 1112 getLogger().debug("Executing SQL query: " + sql); 1113 } 1114 1115 stmt = prepareStatement(connection, sql); 1116 rs = executeQuery(stmt); 1117 1118 return processResultSet(rs); 1119 } 1120 catch (SQLException e) 1121 { 1122 getLogger().error("Error during the communication with the database", e); 1123 throw new RuntimeException("Error during the communication with the database", e); 1124 } 1125 finally 1126 { 1127 ConnectionHelper.cleanup(rs); 1128 ConnectionHelper.cleanup(stmt); 1129 ConnectionHelper.cleanup(connection); 1130 } 1131 } 1132 1133 /** 1134 * Must return the SQL query to execute 1135 * @param connection The pool connection 1136 * @return The SQL query 1137 */ 1138 protected abstract String getSqlQuery(Connection connection); 1139 1140 /** 1141 * Prepare the statement to execute 1142 * @param connection The pool connection 1143 * @param sql The SQL query 1144 * @return The prepared statement, ready to be executed 1145 * @throws SQLException If an SQL Exception occurs 1146 */ 1147 protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException 1148 { 1149 return connection.prepareStatement(sql); 1150 } 1151 1152 /** 1153 * Execute the prepared statement and retrieves the result set. 1154 * @param stmt The prepared statement 1155 * @return The result set 1156 * @throws SQLException If an SQL Exception occurs 1157 */ 1158 protected ResultSet executeQuery(PreparedStatement stmt) throws SQLException 1159 { 1160 return stmt.executeQuery(); 1161 } 1162 1163 /** 1164 * Process the result set 1165 * @param rs The result set 1166 * @return The queried object or null 1167 * @throws SQLException If an SQL exception occurs 1168 * @throws Exception Other exception will be thrown when using {@link #runWithException()} 1169 */ 1170 protected T processResultSet(ResultSet rs) throws SQLException, Exception 1171 { 1172 return null; 1173 } 1174 } 1175 1176 /** 1177 * Query executor in order to select an user 1178 * @param <T> The type of the queried object 1179 */ 1180 protected class SelectUserJdbcQueryExecutor<T> extends AbstractJdbcQueryExecutor<T> 1181 { 1182 /** The user login */ 1183 protected String _value; 1184 /** The search column */ 1185 protected String _searchColumn; 1186 1187 /** 1188 * The constructor 1189 * @param value The strict value to search for 1190 */ 1191 protected SelectUserJdbcQueryExecutor(String value) 1192 { 1193 _value = value; 1194 _searchColumn = __COLUMN_LOGIN; 1195 } 1196 1197 /** 1198 * The constructor 1199 * @param value The strict value to search for 1200 * @param searchColumn The name of search column 1201 */ 1202 protected SelectUserJdbcQueryExecutor(String value, String searchColumn) 1203 { 1204 _value = value; 1205 _searchColumn = searchColumn; 1206 } 1207 1208 @Override 1209 protected String getSqlQuery(Connection connection) 1210 { 1211 // Build SQL request 1212 StringBuilder selectClause = new StringBuilder(); 1213 for (String id : __COLUMNS) 1214 { 1215 if (selectClause.length() > 0) 1216 { 1217 selectClause.append(", "); 1218 } 1219 selectClause.append(id); 1220 } 1221 1222 StringBuilder sql = new StringBuilder("SELECT "); 1223 sql.append(selectClause).append(" FROM ").append(_userTableName); 1224 sql.append(" WHERE ").append(_searchColumn).append(" = ?"); 1225 1226 return sql.toString(); 1227 } 1228 1229 @Override 1230 protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException 1231 { 1232 PreparedStatement stmt = super.prepareStatement(connection, sql); 1233 1234 stmt.setString(1, _value); 1235 return stmt; 1236 } 1237 } 1238 1239 /** 1240 * Query executor in order to select users 1241 * @param <T> The type of the queried object 1242 */ 1243 protected class SelectUsersJdbcQueryExecutor<T> extends AbstractJdbcQueryExecutor<T> 1244 { 1245 /** The pattern to match (none if null) */ 1246 protected String _pattern; 1247 /** The maximum number of users to select */ 1248 protected int _length; 1249 /** The offset to start with, first is 0 */ 1250 protected int _offset; 1251 1252 /** The mandatory predicate to use when querying users by pattern */ 1253 protected JdbcPredicate _mandatoryPredicate; 1254 /** The pattern to match, extracted from the pattern */ 1255 protected String _patternToMatch; 1256 1257 /** 1258 * The constructor 1259 * @param pattern The pattern to match (none if null). 1260 * @param length The maximum number of users to select. 1261 * @param offset The offset to start with, first is 0. 1262 */ 1263 protected SelectUsersJdbcQueryExecutor(String pattern, int length, int offset) 1264 { 1265 _pattern = pattern; 1266 _length = length; 1267 _offset = offset; 1268 } 1269 1270 @Override 1271 protected String getSqlQuery(Connection connection) 1272 { 1273 // Build SQL request 1274 StringBuilder selectClause = new StringBuilder(); 1275 for (String column : __COLUMNS) 1276 { 1277 if (selectClause.length() > 0) 1278 { 1279 selectClause.append(", "); 1280 } 1281 selectClause.append(column); 1282 } 1283 1284 StringBuilder sql = new StringBuilder("SELECT "); 1285 sql.append(selectClause).append(" FROM ").append(_userTableName); 1286 1287 // Add the pattern 1288 _mandatoryPredicate = _getMandatoryPredicate(_pattern); 1289 if (_mandatoryPredicate != null) 1290 { 1291 sql.append(" WHERE ").append(_mandatoryPredicate.getPredicate()); 1292 } 1293 1294 _patternToMatch = _getPatternToMatch(_pattern); 1295 if (_patternToMatch != null) 1296 { 1297 if (ConnectionHelper.DATABASE_DERBY.equals(ConnectionHelper.getDatabaseType(connection))) 1298 { 1299 // The LIKE operator in Derby is case sensitive 1300 sql.append(_mandatoryPredicate != null ? " AND (" : " WHERE ") 1301 .append("UPPER(").append(__COLUMN_LOGIN).append(") LIKE UPPER(?) OR ") 1302 .append("UPPER(").append(__COLUMN_LASTNAME).append(") LIKE UPPER(?) OR ") 1303 .append("UPPER(").append(__COLUMN_FIRSTNAME).append(") LIKE UPPER(?)"); 1304 } 1305 else 1306 { 1307 sql.append(_mandatoryPredicate != null ? " AND (" : " WHERE ") 1308 .append(__COLUMN_LOGIN).append(" LIKE ? OR ") 1309 .append(__COLUMN_LASTNAME).append(" LIKE ? OR ") 1310 .append(__COLUMN_FIRSTNAME).append(" LIKE ?"); 1311 } 1312 1313 if (_mandatoryPredicate != null) 1314 { 1315 sql.append(')'); 1316 } 1317 } 1318 1319 StringBuilder orderByClause = new StringBuilder(); 1320 for (String column : __ORDERBY_COLUMNS) 1321 { 1322 orderByClause.append(orderByClause.length() == 0 ? " ORDER BY " : ", "); 1323 orderByClause.append(column); 1324 } 1325 1326 sql.append(orderByClause); 1327 1328 // Add length filters 1329 sql = _addQuerySize(_length, _offset, connection, selectClause, sql); 1330 1331 return sql.toString(); 1332 } 1333 1334 @SuppressWarnings("synthetic-access") 1335 private StringBuilder _addQuerySize(int length, int offset, Connection con, StringBuilder selectClause, StringBuilder sql) 1336 { 1337 // Do not add anything if not necessary 1338 if (length == Integer.MAX_VALUE && offset == 0) 1339 { 1340 return sql; 1341 } 1342 1343 String dbType = ConnectionHelper.getDatabaseType(con); 1344 1345 if (ConnectionHelper.DATABASE_MYSQL.equals(dbType) || ConnectionHelper.DATABASE_POSTGRES.equals(dbType) || ConnectionHelper.DATABASE_HSQLDB.equals(dbType)) 1346 { 1347 sql.append(" LIMIT " + length + " OFFSET " + offset); 1348 return sql; 1349 } 1350 else if (ConnectionHelper.DATABASE_ORACLE.equals(dbType)) 1351 { 1352 return new StringBuilder("select " + selectClause.toString() + " from (select rownum r, " + selectClause.toString() + " from (" + sql.toString() 1353 + ")) where r BETWEEN " + (offset + 1) + " AND " + (offset + length)); 1354 } 1355 else if (ConnectionHelper.DATABASE_DERBY.equals(dbType)) 1356 { 1357 return new StringBuilder("select ").append(selectClause) 1358 .append(" from (select ROW_NUMBER() OVER () AS ROWNUM, ").append(selectClause.toString()) 1359 .append(" from (").append(sql.toString()).append(") AS TR ) AS TRR where ROWNUM BETWEEN ") 1360 .append(offset + 1).append(" AND ").append(offset + length); 1361 } 1362 else if (getLogger().isWarnEnabled()) 1363 { 1364 getLogger().warn("The request will not have the limit and offset set, since its type is unknown"); 1365 } 1366 1367 return sql; 1368 } 1369 1370 @Override 1371 protected PreparedStatement prepareStatement(Connection connection, String sql) throws SQLException 1372 { 1373 PreparedStatement stmt = super.prepareStatement(connection, sql); 1374 1375 int i = 1; 1376 // Value the parameters if there is a pattern 1377 if (_mandatoryPredicate != null) 1378 { 1379 for (String value : _mandatoryPredicate.getValues()) 1380 { 1381 stmt.setString(i++, value); 1382 } 1383 } 1384 1385 if (_patternToMatch != null) 1386 { 1387 // One for the login, one for the lastname. 1388 stmt.setString(i++, _patternToMatch); 1389 stmt.setString(i++, _patternToMatch); 1390 // FIXME 1391 //if (_parameters.containsKey("firstname")) 1392 //{ 1393 stmt.setString(i++, _patternToMatch); 1394 //} 1395 } 1396 1397 return stmt; 1398 } 1399 } 1400 1401 /** 1402 * Class representing a SQL predicate (to use in a WHERE or HAVING clause), 1403 * with optional string parameters. 1404 */ 1405 public class JdbcPredicate 1406 { 1407 1408 /** The predicate string with optional "?" placeholders. */ 1409 protected String _predicate; 1410 /** The predicate parameter values. */ 1411 protected List<String> _predicateParamValues; 1412 1413 /** 1414 * Build a JDBC predicate. 1415 * @param predicate the predicate string. 1416 * @param values the parameter values. 1417 */ 1418 public JdbcPredicate(String predicate, String... values) 1419 { 1420 this(predicate, Arrays.asList(values)); 1421 } 1422 1423 /** 1424 * Build a JDBC predicate. 1425 * @param predicate the predicate string. 1426 * @param values the parameter values. 1427 */ 1428 public JdbcPredicate(String predicate, List<String> values) 1429 { 1430 this._predicate = predicate; 1431 this._predicateParamValues = values; 1432 } 1433 1434 /** 1435 * Get the predicate. 1436 * @return the predicate 1437 */ 1438 public String getPredicate() 1439 { 1440 return _predicate; 1441 } 1442 1443 /** 1444 * Set the predicate. 1445 * @param predicate the predicate to set 1446 */ 1447 public void setPredicate(String predicate) 1448 { 1449 this._predicate = predicate; 1450 } 1451 1452 /** 1453 * Get the parameter values. 1454 * @return the parameter values. 1455 */ 1456 public List<String> getValues() 1457 { 1458 return _predicateParamValues; 1459 } 1460 1461 /** 1462 * Set the parameter values. 1463 * @param values the parameter values to set. 1464 */ 1465 public void setValues(List<String> values) 1466 { 1467 this._predicateParamValues = values; 1468 } 1469 } 1470 1471}