001: /*
002: JSPWiki - a JSP-based WikiWiki clone.
003:
004: Copyright (C) 2001-2007 JSPWiki Development Group
005:
006: This program is free software; you can redistribute it and/or modify
007: it under the terms of the GNU Lesser General Public License as published by
008: the Free Software Foundation; either version 2.1 of the License, or
009: (at your option) any later version.
010:
011: This program is distributed in the hope that it will be useful,
012: but WITHOUT ANY WARRANTY; without even the implied warranty of
013: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: GNU Lesser General Public License for more details.
015:
016: You should have received a copy of the GNU Lesser General Public License
017: along with this program; if not, write to the Free Software
018: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020: package com.ecyrd.jspwiki.auth.user;
021:
022: import java.security.Principal;
023: import java.sql.*;
024: import java.util.Date;
025: import java.util.HashSet;
026: import java.util.Properties;
027: import java.util.Set;
028:
029: import javax.naming.Context;
030: import javax.naming.InitialContext;
031: import javax.naming.NamingException;
032: import javax.sql.DataSource;
033:
034: import com.ecyrd.jspwiki.NoRequiredPropertyException;
035: import com.ecyrd.jspwiki.TextUtil;
036: import com.ecyrd.jspwiki.WikiEngine;
037: import com.ecyrd.jspwiki.auth.NoSuchPrincipalException;
038: import com.ecyrd.jspwiki.auth.WikiPrincipal;
039: import com.ecyrd.jspwiki.auth.WikiSecurityException;
040:
041: /**
042: * <p>Implementation of UserDatabase that persists {@link DefaultUserProfile}
043: * objects to a JDBC DataSource, as might typically be provided by a web
044: * container. This implementation looks up the JDBC DataSource using JNDI.
045: * The JNDI name of the datasource, backing table and mapped columns used
046: * by this class are configured via settings in <code>jspwiki.properties</code>.</p>
047: * <p>Configurable properties are these:</p>
048: * <table>
049: * <tr>
050: * <thead>
051: * <th>Property</th>
052: * <th>Default</th>
053: * <th>Definition</th>
054: * <thead>
055: * </tr>
056: * <tr>
057: * <td><code>jspwiki.userdatabase.datasource</code></td>
058: * <td><code>jdbc/UserDatabase</code></td>
059: * <td>The JNDI name of the DataSource</td>
060: * </tr>
061: * <tr>
062: * <td><code>jspwiki.userdatabase.table</code></td>
063: * <td><code>users</code></td>
064: * <td>The table that stores the user profiles</td>
065: * </tr>
066: * <tr>
067: * <td><code>jspwiki.userdatabase.created</code></td>
068: * <td><code>created</code></td>
069: * <td>The column containing the profile's creation timestamp</td>
070: * </tr>
071: * <tr>
072: * <td><code>jspwiki.userdatabase.email</code></td>
073: * <td><code>email</code></td>
074: * <td>The column containing the user's e-mail address</td>
075: * </tr>
076: * <tr>
077: * <td><code>jspwiki.userdatabase.fullName</code></td>
078: * <td><code>full_name</code></td>
079: * <td>The column containing the user's full name</td>
080: * </tr>
081: * <tr>
082: * <td><code>jspwiki.userdatabase.loginName</code></td>
083: * <td><code>login_name</code></td>
084: * <td>The column containing the user's login id</td>
085: * </tr>
086: * <tr>
087: * <td><code>jspwiki.userdatabase.password</code></td>
088: * <td><code>password</code></td>
089: * <td>The column containing the user's password</td>
090: * </tr>
091: * <tr>
092: * <td><code>jspwiki.userdatabase.modified</code></td>
093: * <td><code>modified</code></td>
094: * <td>The column containing the profile's last-modified timestamp</td>
095: * </tr>
096: * <tr>
097: * <td><code>jspwiki.userdatabase.wikiName</code></td>
098: * <td><code>wiki_name</code></td>
099: * <td>The column containing the user's wiki name</td>
100: * </tr>
101: * <tr>
102: * <td><code>jspwiki.userdatabase.roleTable</code></td>
103: * <td><code>roles</code></td>
104: * <td>The table that stores user roles. When a new user is created,
105: * a new record is inserted containing user's initial role. The
106: * table will have an ID column whose name and values correspond
107: * to the contents of the user table's login name column. It will
108: * also contain a role column (see next row).</td>
109: * </tr>
110: * <tr>
111: * <td><code>jspwiki.userdatabase.role</code></td>
112: * <td><code>role</code></td>
113: * <td>The column in the role table that stores user roles. When a new user
114: * is created, this column will be populated with the value
115: * <code>Authenticated</code>. Once created, JDBCUserDatabase does not
116: * use this column again; it is provided strictly for the convenience
117: * of container-managed authentication services.</td>
118: * </tr>
119: * <tr>
120: * <td><code>jspwiki.userdatabase.hashPrefix</code></td>
121: * <td><code>true</code></td>
122: * <td>Whether or not to prepend a prefix for the hash algorithm, <em>e.g.</em>,
123: * <code>{SHA}</code>.</td>
124: * </tr>
125: * </table>
126: * <p>This class hashes passwords using SHA-1. All of the underying SQL commands used by this class are implemented using
127: * prepared statements, so it is immune to SQL injection attacks.</p>
128: * <p>This class is typically used in conjunction with a web container's JNDI resource
129: * factory. For example, Tomcat versions 4 and higher provide a basic JNDI factory
130: * for registering DataSources. To give JSPWiki access to the JNDI resource named
131: * by <code></code>, you would declare the datasource resource similar to this:</p>
132: * <blockquote><code><Context ...><br/>
133: * ...<br/>
134: * <Resource name="jdbc/UserDatabase" auth="Container"<br/>
135: * type="javax.sql.DataSource" username="dbusername" password="dbpassword"<br/>
136: * driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL:database"<br/>
137: * maxActive="8" maxIdle="4"/><br/>
138: * ...<br/>
139: * </Context></code></blockquote>
140: * <p>JDBC driver JARs should be added to Tomcat's <code>common/lib</code> directory.
141: * For more Tomcat 5.5 JNDI configuration examples,
142: * see <a href="http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html">
143: * http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html</a>.</p>
144: * <p>JDBCUserDatabase commits changes as transactions if the back-end database supports them.
145: * If the database supports transactions, user profile changes are saved
146: * to permanent storage only when the {@link #commit()} method is called. If the database does <em>not</em>
147: * support transactions, then changes are made immediately (during the {@link #save(UserProfile)}
148: * method), and the {@linkplain #commit()} method no-ops. Thus, callers should always call the
149: * {@linkplain #commit()} method after saving a profile to guarantee that changes are applied.</p>
150: * @author Andrew R. Jaquith
151: * @since 2.3
152: */
153: public class JDBCUserDatabase extends AbstractUserDatabase {
154:
155: private static final String NOTHING = "";
156:
157: public static final String DEFAULT_DB_CREATED = "created";
158:
159: public static final String DEFAULT_DB_EMAIL = "email";
160:
161: public static final String DEFAULT_DB_FULL_NAME = "full_name";
162:
163: public static final String DEFAULT_DB_HASH_PREFIX = "true";
164:
165: public static final String DEFAULT_DB_JNDI_NAME = "jdbc/UserDatabase";
166:
167: public static final String DEFAULT_DB_MODIFIED = "modified";
168:
169: public static final String DEFAULT_DB_ROLE = "role";
170:
171: public static final String DEFAULT_DB_ROLE_TABLE = "roles";
172:
173: public static final String DEFAULT_DB_TABLE = "users";
174:
175: public static final String DEFAULT_DB_LOGIN_NAME = "login_name";
176:
177: public static final String DEFAULT_DB_PASSWORD = "password";
178:
179: public static final String DEFAULT_DB_WIKI_NAME = "wiki_name";
180:
181: public static final String PROP_DB_CREATED = "jspwiki.userdatabase.created";
182:
183: public static final String PROP_DB_EMAIL = "jspwiki.userdatabase.email";
184:
185: public static final String PROP_DB_FULL_NAME = "jspwiki.userdatabase.fullName";
186:
187: public static final String PROP_DB_DATASOURCE = "jspwiki.userdatabase.datasource";
188:
189: public static final String PROP_DB_HASH_PREFIX = "jspwiki.userdatabase.hashPrefix";
190:
191: public static final String PROP_DB_LOGIN_NAME = "jspwiki.userdatabase.loginName";
192:
193: public static final String PROP_DB_MODIFIED = "jspwiki.userdatabase.modified";
194:
195: public static final String PROP_DB_PASSWORD = "jspwiki.userdatabase.password";
196:
197: public static final String PROP_DB_ROLE = "jspwiki.userdatabase.role";
198:
199: public static final String PROP_DB_ROLE_TABLE = "jspwiki.userdatabase.roleTable";
200:
201: public static final String PROP_DB_TABLE = "jspwiki.userdatabase.table";
202:
203: public static final String PROP_DB_WIKI_NAME = "jspwiki.userdatabase.wikiName";
204:
205: private DataSource m_ds = null;
206: private String m_deleteUserByLoginName = null;
207: private String m_deleteRoleByLoginName = null;
208: private String m_findByEmail = null;
209: private String m_findByFullName = null;
210: private String m_findByLoginName = null;
211: private String m_findByWikiName = null;
212: private String m_renameProfile = null;
213: private String m_renameRoles = null;
214: private String m_updateProfile = null;
215: private String m_findAll = null;
216: private String m_findRoles = null;
217: private String m_initialRole = "Authenticated";
218: private String m_insertProfile = null;
219: private String m_insertRole = null;
220: private String m_userTable = null;
221: private String m_email = null;
222: private String m_fullName = null;
223: private boolean m_hashPrefix = true;
224: private String m_loginName = null;
225: private String m_password = null;
226: private String m_role = null;
227: private String m_roleTable = null;
228: private String m_wikiName = null;
229: private String m_created = null;
230: private String m_modified = null;
231: private boolean m_sharedWithContainer = false;
232: private boolean m_supportsCommits = false;
233:
234: /**
235: * Looks up and deletes the first {@link UserProfile} in the user database
236: * that matches a profile having a given login name. If the user database
237: * does not contain a user with a matching attribute, throws a
238: * {@link NoSuchPrincipalException}. This method is intended to be atomic;
239: * results cannot be partially committed. If the commit fails, it should
240: * roll back its state appropriately. Implementing classes that persist
241: * to the file system may wish to make this method <code>synchronized</code>.
242: * @param loginName the login name of the user profile that shall be deleted
243: */
244: public void deleteByLoginName(String loginName)
245: throws NoSuchPrincipalException, WikiSecurityException {
246: // Get the existing user; if not found, throws NoSuchPrincipalException
247: findByLoginName(loginName);
248: Connection conn = null;
249:
250: try {
251: // Open the database connection
252: conn = m_ds.getConnection();
253: if (m_supportsCommits) {
254: conn.setAutoCommit(false);
255: }
256:
257: PreparedStatement ps;
258: // Delete user record
259: ps = conn.prepareStatement(m_deleteUserByLoginName);
260: ps.setString(1, loginName);
261: ps.execute();
262: ps.close();
263:
264: // Delete role record
265: ps = conn.prepareStatement(m_deleteRoleByLoginName);
266: ps.setString(1, loginName);
267: ps.execute();
268: ps.close();
269:
270: // Commit and close connection
271: if (m_supportsCommits) {
272: conn.commit();
273: }
274: } catch (SQLException e) {
275: throw new WikiSecurityException(e.getMessage());
276: } finally {
277: try {
278: conn.close();
279: } catch (Exception e) {
280: }
281: }
282: }
283:
284: /**
285: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#findByEmail(java.lang.String)
286: */
287: public UserProfile findByEmail(String index)
288: throws NoSuchPrincipalException {
289: return findByPreparedStatement(m_findByEmail, index);
290: }
291:
292: /**
293: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#findByFullName(java.lang.String)
294: */
295: public UserProfile findByFullName(String index)
296: throws NoSuchPrincipalException {
297: return findByPreparedStatement(m_findByFullName, index);
298: }
299:
300: /**
301: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#findByLoginName(java.lang.String)
302: */
303: public UserProfile findByLoginName(String index)
304: throws NoSuchPrincipalException {
305: return findByPreparedStatement(m_findByLoginName, index);
306: }
307:
308: /**
309: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#findByWikiName(String)
310: */
311: public UserProfile findByWikiName(String index)
312: throws NoSuchPrincipalException {
313: return findByPreparedStatement(m_findByWikiName, index);
314: }
315:
316: /**
317: * Returns all WikiNames that are stored in the UserDatabase
318: * as an array of WikiPrincipal objects. If the database does not
319: * contain any profiles, this method will return a zero-length
320: * array.
321: * @return the WikiNames
322: */
323: public Principal[] getWikiNames() throws WikiSecurityException {
324: Set principals = new HashSet();
325: Connection conn = null;
326: try {
327: conn = m_ds.getConnection();
328: PreparedStatement ps = conn.prepareStatement(m_findAll);
329: ResultSet rs = ps.executeQuery();
330: while (rs.next()) {
331: String wikiName = rs.getString(m_wikiName);
332: if (wikiName == null) {
333: log
334: .warn("Detected null wiki name in XMLUserDataBase. Check your user database.");
335: } else {
336: Principal principal = new WikiPrincipal(wikiName,
337: WikiPrincipal.WIKI_NAME);
338: principals.add(principal);
339: }
340: }
341: ps.close();
342: } catch (SQLException e) {
343: throw new WikiSecurityException(e.getMessage());
344: } finally {
345: try {
346: conn.close();
347: } catch (Exception e) {
348: }
349: }
350:
351: return (Principal[]) principals
352: .toArray(new Principal[principals.size()]);
353: }
354:
355: /**
356: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#initialize(com.ecyrd.jspwiki.WikiEngine,
357: * java.util.Properties)
358: */
359: public void initialize(WikiEngine engine, Properties props)
360: throws NoRequiredPropertyException {
361: String jndiName = props.getProperty(PROP_DB_DATASOURCE,
362: DEFAULT_DB_JNDI_NAME);
363: try {
364: Context initCtx = new InitialContext();
365: Context ctx = (Context) initCtx.lookup("java:comp/env");
366: m_ds = (DataSource) ctx.lookup(jndiName);
367:
368: // Prepare the SQL selectors
369: m_userTable = props.getProperty(PROP_DB_TABLE,
370: DEFAULT_DB_TABLE);
371: m_email = props
372: .getProperty(PROP_DB_EMAIL, DEFAULT_DB_EMAIL);
373: m_fullName = props.getProperty(PROP_DB_FULL_NAME,
374: DEFAULT_DB_FULL_NAME);
375: m_hashPrefix = Boolean.valueOf(
376: props.getProperty(PROP_DB_HASH_PREFIX,
377: DEFAULT_DB_HASH_PREFIX)).booleanValue();
378: m_loginName = props.getProperty(PROP_DB_LOGIN_NAME,
379: DEFAULT_DB_LOGIN_NAME);
380: m_password = props.getProperty(PROP_DB_PASSWORD,
381: DEFAULT_DB_PASSWORD);
382: m_wikiName = props.getProperty(PROP_DB_WIKI_NAME,
383: DEFAULT_DB_WIKI_NAME);
384: m_created = props.getProperty(PROP_DB_CREATED,
385: DEFAULT_DB_CREATED);
386: m_modified = props.getProperty(PROP_DB_MODIFIED,
387: DEFAULT_DB_MODIFIED);
388:
389: m_findAll = "SELECT * FROM " + m_userTable;
390: m_findByEmail = "SELECT * FROM " + m_userTable + " WHERE "
391: + m_email + "=?";
392: m_findByFullName = "SELECT * FROM " + m_userTable
393: + " WHERE " + m_fullName + "=?";
394: m_findByLoginName = "SELECT * FROM " + m_userTable
395: + " WHERE " + m_loginName + "=?";
396: m_findByWikiName = "SELECT * FROM " + m_userTable
397: + " WHERE " + m_wikiName + "=?";
398:
399: // Prepare the user isert/update SQL
400: m_insertProfile = "INSERT INTO " + m_userTable + " ("
401: + m_email + "," + m_fullName + "," + m_password
402: + "," + m_wikiName + "," + m_modified + ","
403: + m_loginName + "," + m_created
404: + ") VALUES (?,?,?,?,?,?,?)";
405: m_updateProfile = "UPDATE " + m_userTable + " SET "
406: + m_email + "=?," + m_fullName + "=?," + m_password
407: + "=?," + m_wikiName + "=?," + m_modified
408: + "=? WHERE " + m_loginName + "=?";
409:
410: // Prepare the role insert SQL
411: m_roleTable = props.getProperty(PROP_DB_ROLE_TABLE,
412: DEFAULT_DB_ROLE_TABLE);
413: m_role = props.getProperty(PROP_DB_ROLE, DEFAULT_DB_ROLE);
414: m_insertRole = "INSERT INTO " + m_roleTable + " ("
415: + m_loginName + "," + m_role + ") VALUES (?,?)";
416: m_findRoles = "SELECT * FROM " + m_roleTable + " WHERE "
417: + m_loginName + "=?";
418:
419: // Prepare the user delete SQL
420: m_deleteUserByLoginName = "DELETE FROM " + m_userTable
421: + " WHERE " + m_loginName + "=?";
422:
423: // Prepare the role delete SQL
424: m_deleteRoleByLoginName = "DELETE FROM " + m_roleTable
425: + " WHERE " + m_loginName + "=?";
426:
427: // Prepare the rename user/roles SQL
428: m_renameProfile = "UPDATE " + m_userTable + " SET "
429: + m_loginName + "=?," + m_modified + "=? WHERE "
430: + m_loginName + "=?";
431: m_renameRoles = "UPDATE " + m_roleTable + " SET "
432: + m_loginName + "=? WHERE " + m_loginName + "=?";
433:
434: // Set the "share users with container flag"
435: m_sharedWithContainer = TextUtil.isPositive(props
436: .getProperty(PROP_SHARED_WITH_CONTAINER, "false"));
437: } catch (NamingException e) {
438: log.error("JDBCUserDatabase initialization error: "
439: + e.getMessage());
440: throw new NoRequiredPropertyException(PROP_DB_DATASOURCE,
441: "JDBCUserDatabase initialization error: "
442: + e.getMessage());
443: }
444:
445: // Test connection by doing a quickie select
446: Connection conn = null;
447: try {
448: conn = m_ds.getConnection();
449: PreparedStatement ps = conn.prepareStatement(m_findAll);
450: ps.executeQuery();
451: ps.close();
452: } catch (SQLException e) {
453: log.error("JDBCUserDatabase initialization error: "
454: + e.getMessage());
455: throw new NoRequiredPropertyException(PROP_DB_DATASOURCE,
456: "JDBCUserDatabase initialization error: "
457: + e.getMessage());
458: } finally {
459: try {
460: conn.close();
461: } catch (Exception e) {
462: }
463: }
464: log.info("JDBCUserDatabase initialized from JNDI DataSource: "
465: + jndiName);
466:
467: // Determine if the datasource supports commits
468: try {
469: conn = m_ds.getConnection();
470: DatabaseMetaData dmd = conn.getMetaData();
471: if (dmd.supportsTransactions()) {
472: m_supportsCommits = true;
473: conn.setAutoCommit(false);
474: log
475: .info("JDBCUserDatabase supports transactions. Good; we will use them.");
476: }
477: } catch (SQLException e) {
478: log
479: .warn("JDBCUserDatabase warning: user database doesn't seem to support transactions. Reason: "
480: + e.getMessage());
481: throw new NoRequiredPropertyException(PROP_DB_DATASOURCE,
482: "JDBCUserDatabase initialization error: "
483: + e.getMessage());
484: } finally {
485: try {
486: conn.close();
487: } catch (Exception e) {
488: }
489: }
490: }
491:
492: /**
493: * Determines whether the user database shares user/password data with the
494: * web container; returns <code>true</code> if the JSPWiki property
495: * <code>jspwiki.userdatabase.isSharedWithContainer</code> is <code>true</code>.
496: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#isSharedWithContainer()
497: */
498: public boolean isSharedWithContainer() {
499: return m_sharedWithContainer;
500: }
501:
502: /**
503: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#rename(String, String)
504: */
505: public void rename(String loginName, String newName)
506: throws NoSuchPrincipalException, DuplicateUserException,
507: WikiSecurityException {
508: // Get the existing user; if not found, throws NoSuchPrincipalException
509: UserProfile profile = findByLoginName(loginName);
510:
511: // Get user with the proposed name; if found, it's a collision
512: try {
513: UserProfile otherProfile = findByLoginName(newName);
514: if (otherProfile != null) {
515: throw new DuplicateUserException(
516: "Cannot rename: the login name '" + newName
517: + "' is already taken.");
518: }
519: } catch (NoSuchPrincipalException e) {
520: // Good! That means it's safe to save using the new name
521: }
522:
523: Connection conn = null;
524: try {
525: // Open the database connection
526: conn = m_ds.getConnection();
527: if (m_supportsCommits) {
528: conn.setAutoCommit(false);
529: }
530:
531: Timestamp ts = new Timestamp(System.currentTimeMillis());
532: Date modDate = new Date(ts.getTime());
533:
534: // Change the login ID for the user record
535: PreparedStatement ps = conn
536: .prepareStatement(m_renameProfile);
537: ps.setString(1, newName);
538: ps.setTimestamp(2, ts);
539: ps.setString(3, loginName);
540: ps.execute();
541: ps.close();
542:
543: // Change the login ID for the role records
544: ps = conn.prepareStatement(m_renameRoles);
545: ps.setString(1, newName);
546: ps.setString(2, loginName);
547: ps.execute();
548: ps.close();
549:
550: // Set the profile name and mod time
551: profile.setLoginName(newName);
552: profile.setLastModified(modDate);
553:
554: // Commit and close connection
555: if (m_supportsCommits) {
556: conn.commit();
557: }
558: } catch (SQLException e) {
559: throw new WikiSecurityException(e.getMessage());
560: } finally {
561: try {
562: conn.close();
563: } catch (Exception e) {
564: }
565: }
566: }
567:
568: /**
569: * @see com.ecyrd.jspwiki.auth.user.UserDatabase#save(com.ecyrd.jspwiki.auth.user.UserProfile)
570: */
571: public void save(UserProfile profile) throws WikiSecurityException {
572: // Figure out which prepared statement to use & execute it
573: String loginName = profile.getLoginName();
574: PreparedStatement ps = null;
575: UserProfile existingProfile = null;
576: try {
577: existingProfile = findByLoginName(loginName);
578: } catch (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: String existingPassword = (existingProfile == null) ? null
586: : existingProfile.getPassword();
587: if (NOTHING.equals(password)) {
588: password = null;
589: }
590: if (password == null) {
591: password = existingPassword;
592: }
593:
594: // If password changed, hash it before we save
595: if (!password.equals(existingPassword)) {
596: password = m_hashPrefix ? SHA_PREFIX + getHash(password)
597: : getHash(password);
598: }
599:
600: Connection conn = null;
601: try {
602: // Open the database connection
603: conn = m_ds.getConnection();
604: if (m_supportsCommits) {
605: conn.setAutoCommit(false);
606: }
607:
608: Timestamp ts = new Timestamp(System.currentTimeMillis());
609: Date modDate = new Date(ts.getTime());
610: if (existingProfile == null) {
611: // User is new: insert new user record
612: ps = conn.prepareStatement(m_insertProfile);
613: ps.setString(1, profile.getEmail());
614: ps.setString(2, profile.getFullname());
615: ps.setString(3, password);
616: ps.setString(4, profile.getWikiName());
617: ps.setTimestamp(5, ts);
618: ps.setString(6, profile.getLoginName());
619: ps.setTimestamp(7, ts);
620: ps.execute();
621: ps.close();
622:
623: // Insert role record if no roles yet
624: if (m_sharedWithContainer) {
625: ps = conn.prepareStatement(m_findRoles);
626: ps.setString(1, profile.getLoginName());
627: ResultSet rs = ps.executeQuery();
628: int roles = 0;
629: while (rs.next()) {
630: roles++;
631: }
632: ps.close();
633: if (roles == 0) {
634: ps = conn.prepareStatement(m_insertRole);
635: ps.setString(1, profile.getLoginName());
636: ps.setString(2, m_initialRole);
637: ps.execute();
638: ps.close();
639: }
640: }
641:
642: // Set the profile creation time
643: profile.setCreated(modDate);
644: } else {
645: // User exists: modify existing record
646: ps = conn.prepareStatement(m_updateProfile);
647: ps.setString(1, profile.getEmail());
648: ps.setString(2, profile.getFullname());
649: ps.setString(3, password);
650: ps.setString(4, profile.getWikiName());
651: ps.setTimestamp(5, ts);
652: ps.setString(6, profile.getLoginName());
653: ps.execute();
654: ps.close();
655: }
656: // Set the profile mod time
657: profile.setLastModified(modDate);
658:
659: // Commit and close connection
660: if (m_supportsCommits) {
661: conn.commit();
662: }
663: } catch (SQLException e) {
664: throw new WikiSecurityException(e.getMessage());
665: } finally {
666: try {
667: conn.close();
668: } catch (Exception e) {
669: }
670: }
671: }
672:
673: /**
674: *
675: * @param rs
676: * @return
677: * @throws SQLException
678: */
679: private UserProfile findByPreparedStatement(String sql, String index)
680: throws NoSuchPrincipalException {
681: UserProfile profile = null;
682: boolean found = false;
683: boolean unique = true;
684: Connection conn = null;
685: try {
686: // Open the database connection
687: conn = m_ds.getConnection();
688: if (m_supportsCommits) {
689: conn.setAutoCommit(false);
690: }
691:
692: PreparedStatement ps = conn.prepareStatement(sql);
693: ps.setString(1, index);
694: ResultSet rs = ps.executeQuery();
695: while (rs.next()) {
696: if (profile != null) {
697: unique = false;
698: break;
699: }
700: profile = new DefaultUserProfile();
701: profile.setCreated(rs.getTimestamp(m_created));
702: profile.setEmail(rs.getString(m_email));
703: profile.setFullname(rs.getString(m_fullName));
704: profile.setLastModified(rs.getTimestamp(m_modified));
705: profile.setLoginName(rs.getString(m_loginName));
706: profile.setPassword(rs.getString(m_password));
707: found = true;
708: }
709: ps.close();
710: } catch (SQLException e) {
711: throw new NoSuchPrincipalException(e.getMessage());
712: } finally {
713: try {
714: conn.close();
715: } catch (Exception e) {
716: }
717: }
718:
719: if (!found) {
720: throw new NoSuchPrincipalException(
721: "Could not find profile in database!");
722: }
723: if (!unique) {
724: throw new NoSuchPrincipalException(
725: "More than one profile in database!");
726: }
727: return profile;
728:
729: }
730:
731: }
|