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