Moved to use of prepared statements for JDBC connector.
[java-idp.git] / src / edu / internet2 / middleware / shibboleth / aa / attrresolv / provider / JDBCDataConnector.java
1 /*
2  * Copyright (c) 2003 National Research Council of Canada
3  *
4  * Permission is hereby granted, free of charge, to any person 
5  * obtaining a copy of this software and associated documentation 
6  * files (the "Software"), to deal in the Software without 
7  * restriction, including without limitation the rights to use, 
8  * copy, modify, merge, publish, distribute, sublicense, and/or 
9  * sell copies of the Software, and to permit persons to whom the 
10  * Software is furnished to do so, subject to the following conditions:
11  *
12  * The above copyright notice and this permission notice shall be 
13  * included in all copies or substantial portions of the Software.
14  *
15  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 
16  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 
17  * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 
18  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 
19  * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
20  * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
21  * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 
22  * OTHER DEALINGS IN THE SOFTWARE.
23  *
24  */
25
26 package edu.internet2.middleware.shibboleth.aa.attrresolv.provider;
27
28 import java.io.PrintWriter;
29 import java.lang.reflect.Constructor;
30 import java.security.Principal;
31 import java.sql.Connection;
32 import java.sql.PreparedStatement;
33 import java.sql.ResultSet;
34 import java.sql.ResultSetMetaData;
35 import java.sql.SQLException;
36
37 import javax.naming.directory.Attributes;
38 import javax.naming.directory.BasicAttribute;
39 import javax.naming.directory.BasicAttributes;
40 import javax.sql.DataSource;
41
42 import org.apache.commons.dbcp.ConnectionFactory;
43 import org.apache.commons.dbcp.DriverManagerConnectionFactory;
44 import org.apache.commons.dbcp.PoolableConnectionFactory;
45 import org.apache.commons.dbcp.PoolingDataSource;
46 import org.apache.commons.pool.impl.GenericObjectPool;
47 import org.apache.commons.pool.impl.StackKeyedObjectPoolFactory;
48 import org.apache.log4j.Logger;
49 import org.apache.log4j.Priority;
50 import org.w3c.dom.Element;
51 import org.w3c.dom.Node;
52 import org.w3c.dom.NodeList;
53
54 import edu.internet2.middleware.shibboleth.aa.attrresolv.AttributeResolver;
55 import edu.internet2.middleware.shibboleth.aa.attrresolv.DataConnectorPlugIn;
56 import edu.internet2.middleware.shibboleth.aa.attrresolv.Dependencies;
57 import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolutionPlugInException;
58
59 /*
60  * Built at the Canada Institute for Scientific and Technical Information (CISTI 
61  * <ahref="http://www.cisti-icist.nrc-cnrc.gc.ca/">http://www.cisti-icist.nrc-cnrc.gc.ca/</a>, 
62  * the National Research Council Canada 
63  * (NRC <a href="http://www.nrc-cnrc.gc.ca/">http://www.nrc-cnrc.gc.ca/</a>)
64  * by David Dearman, COOP student from Dalhousie University,
65  * under the direction of Glen Newton, Head research (IT)
66  * <ahref="mailto:glen.newton@nrc-cnrc.gc.ca">glen.newton@nrc-cnrc.gc.ca</a>. 
67  */
68
69 /**
70  * Data Connector that uses JDBC to access user attributes stored in databases.
71  *
72  * @author David Dearman (dearman@cs.dal.ca)
73  * @author Walter Hoehn (wassa@columbia.edu)
74  * @author Scott Cantor
75  */
76
77 public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConnectorPlugIn {
78
79         private static Logger log = Logger.getLogger(JDBCDataConnector.class.getName());
80         protected String searchVal;
81         protected DataSource dataSource;
82         protected JDBCAttributeExtractor extractor;
83         protected JDBCStatementCreator statementCreator;
84
85         public JDBCDataConnector(Element element) throws ResolutionPlugInException {
86
87                 super(element);
88
89                 //Get the query string
90                 NodeList queryNodes = element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Query");
91                 Node tnode = queryNodes.item(0).getFirstChild();
92                 if (tnode != null && tnode.getNodeType() == Node.TEXT_NODE) {
93                         searchVal = tnode.getNodeValue();
94                 }
95                 if (searchVal == null || searchVal.equals("")) {
96                         log.error("Database query must be specified.");
97                         throw new ResolutionPlugInException("Database query must be specified.");
98                 }
99
100                 //Load the supplied JDBC driver
101                 String dbDriverName = element.getAttribute("dbDriver");
102                 if (dbDriverName != null && (!dbDriverName.equals(""))) {
103                         loadDriver(dbDriverName);
104                 }
105
106                 //Load site-specific implementation classes     
107                 setupAttributeExtractor(
108                         (Element) element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "AttributeExtractor").item(
109                                 0));
110                 setupStatementCreator(
111                         (Element) element.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "StatementCreator").item(0));
112
113                 //Initialize a pooling Data Source
114                 int maxActive = 0;
115                 int maxIdle = 0;
116                 try {
117                         if (element.getAttribute("maxActive") != null) {
118                                 maxActive = Integer.parseInt(element.getAttribute("maxActive"));
119                         }
120                         if (element.getAttribute("maxIdle") != null) {
121                                 maxIdle = Integer.parseInt(element.getAttribute("maxIdle"));
122                         }
123                 } catch (NumberFormatException e) {
124                         log.error("Malformed pooling limits: using defaults.");
125                 }
126                 if (element.getAttribute("dbURL") == null || element.getAttribute("dbURL").equals("")) {
127                         log.error("JDBC connection requires a dbURL property");
128                         throw new ResolutionPlugInException("JDBCDataConnection requires a \"dbURL\" property");
129                 }
130                 setupDataSource(element.getAttribute("dbURL"), maxActive, maxIdle);
131         }
132
133         /**
134          * Initialize a Pooling Data Source
135          */
136         private void setupDataSource(String dbURL, int maxActive, int maxIdle) throws ResolutionPlugInException {
137
138                 GenericObjectPool objectPool = new GenericObjectPool(null);
139
140                 if (maxActive > 0) {
141                         objectPool.setMaxActive(maxActive);
142                 }
143                 if (maxIdle > 0) {
144                         objectPool.setMaxIdle(maxIdle);
145                 }
146
147                 objectPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
148
149                 ConnectionFactory connFactory = null;
150                 PoolableConnectionFactory poolConnFactory = null;
151
152                 try {
153                         connFactory = new DriverManagerConnectionFactory(dbURL, null);
154                         log.debug("Connection factory initialized.");
155                 } catch (Exception ex) {
156                         log.error(
157                                 "Connection factory couldn't be initialized, ensure database URL, username and password are correct.");
158                         throw new ResolutionPlugInException("Connection facotry couldn't be initialized: " + ex.getMessage());
159                 }
160
161                 try {
162                         new StackKeyedObjectPoolFactory();
163                         poolConnFactory =
164                                 new PoolableConnectionFactory(
165                                         connFactory,
166                                         objectPool,
167                                         new StackKeyedObjectPoolFactory(),
168                                         null,
169                                         false,
170                                         true);
171                 } catch (Exception ex) {
172                         log.debug("Poolable connection factory error");
173                 }
174
175                 dataSource = new PoolingDataSource(objectPool);
176                 log.info("Data Source initialized.");
177                 try {
178                         dataSource.setLogWriter(
179                                 new Log4jPrintWriter(Logger.getLogger(JDBCDataConnector.class.getName() + ".Pool"), Priority.DEBUG));
180                 } catch (SQLException e) {
181                         log.error("Coudn't setup logger for database connection pool.");
182                 }
183         }
184
185         /**
186          * Instantiate an Attribute Extractor, using the default if none was configured
187          */
188         private void setupAttributeExtractor(Element config) throws ResolutionPlugInException {
189
190                 String className = null;
191                 if (config != null) {
192                         className = config.getAttribute("class");
193                 }
194                 if (className == null || className.equals("")) {
195                         log.debug("Using default Attribute Extractor.");
196                         className = DefaultAE.class.getName();
197                 }
198                 try {
199                         Class aeClass = Class.forName(className);
200                         extractor = (JDBCAttributeExtractor) aeClass.newInstance();
201                         log.debug("Attribute Extractor implementation loaded.");
202
203                 } catch (ClassNotFoundException e) {
204                         log.error("The supplied Attribute Extractor class could not be found: " + e);
205                         throw new ResolutionPlugInException(
206                                 "The supplied Attribute Extractor class could not be found: " + e.getMessage());
207                 } catch (Exception e) {
208                         log.error("Unable to instantiate Attribute Extractor implementation: " + e);
209                         throw new ResolutionPlugInException(
210                                 "Unable to instantiate Attribute Extractor implementation: " + e.getMessage());
211                 }
212         }
213
214         /**
215          * Instantiate a Statement Creator, using the default if none was configured
216          */
217         private void setupStatementCreator(Element config) throws ResolutionPlugInException {
218
219                 String scClassName = null;
220                 if (config != null) {
221                         scClassName = config.getAttribute("class");
222                 }
223                 if (scClassName == null || scClassName.equals("")) {
224                         log.debug("Using default Statement Creator.");
225                         scClassName = DefaultStatementCreator.class.getName();
226                 }
227                 try {
228                         Class scClass = Class.forName(scClassName);
229
230                         Class[] params = new Class[1];
231                         params[0] = Class.forName("org.w3c.dom.Element");
232                         try {
233                                 Constructor implementorConstructor = scClass.getConstructor(params);
234                                 Object[] args = new Object[1];
235                                 args[0] = config;
236                                 log.debug("Initializing Statement Creator of type (" + scClass.getName() + ").");
237                                 statementCreator = (JDBCStatementCreator) implementorConstructor.newInstance(args);
238                         } catch (NoSuchMethodException nsme) {
239                                 log.debug(
240                                         "Implementation constructor does have a parameterized constructor, attempting to load default.");
241                                 statementCreator = (JDBCStatementCreator) scClass.newInstance();
242                         }
243
244                         log.debug("Statement Creator implementation loaded.");
245
246                 } catch (ClassNotFoundException e) {
247                         log.error("The supplied Statement Creator class could not be found: " + e);
248                         throw new ResolutionPlugInException(
249                                 "The supplied Statement Creator class could not be found: " + e.getMessage());
250                 } catch (Exception e) {
251                         log.error("Unable to instantiate Statement Creator implementation: " + e);
252                         throw new ResolutionPlugInException(
253                                 "Unable to instantiate Statement Creator implementation: " + e.getMessage());
254                 }
255         }
256
257         public Attributes resolve(Principal principal, String requester, Dependencies depends)
258                 throws ResolutionPlugInException {
259
260                 log.debug("Resolving connector: (" + getId() + ")");
261
262                 //Retrieve a connection from the connection pool
263                 Connection conn = null;
264                 try {
265                         conn = dataSource.getConnection();
266                         log.debug("Connection retrieved from pool");
267                 } catch (Exception e) {
268                         log.error("Unable to fetch a connection from the pool");
269                         throw new ResolutionPlugInException("Unable to fetch a connection from the pool: " + e.getMessage());
270                 }
271                 if (conn == null) {
272                         log.error("Pool didn't return a propertly initialized connection.");
273                         throw new ResolutionPlugInException("Pool didn't return a propertly initialized connection.");
274                 }
275
276                 //Setup and execute a (pooled) prepared statement
277                 ResultSet rs = null;
278                 try {
279                         PreparedStatement preparedStatement = conn.prepareStatement(searchVal);
280                         statementCreator.create(preparedStatement, principal, requester, depends);
281                         rs = preparedStatement.executeQuery();
282                         preparedStatement.close();
283
284                         if (!rs.next()) {
285                                 return new BasicAttributes();
286                         }
287
288                 } catch (JDBCStatementCreatorException e) {
289                         log.error("An ERROR occured while constructing the query");
290                         throw new ResolutionPlugInException("An ERROR occured while constructing the query: " + e.getMessage());
291                 } catch (SQLException e) {
292                         log.error("An ERROR occured while executing the query");
293                         throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
294                 }
295
296                 //Extract attributes from the ResultSet
297                 try {
298                         return extractor.extractAttributes(rs);
299
300                 } catch (JDBCAttributeExtractorException e) {
301                         log.error("An ERROR occured while extracting attributes from result set");
302                         throw new ResolutionPlugInException(
303                                 "An ERROR occured while extracting attributes from result set: " + e.getMessage());
304                 } finally {
305                         try {
306                                 rs.close();
307                         } catch (SQLException e) {
308                                 log.error("An error occured while closing the result set: " + e);
309                                 throw new ResolutionPlugInException("An error occured while closing the result set: " + e);
310                         }
311
312                         try {
313                                 conn.close();
314                         } catch (SQLException e) {
315                                 log.error("An error occured while closing the database connection: " + e);
316                                 throw new ResolutionPlugInException("An error occured while closing the database connection: " + e);
317                         }
318                 }
319         }
320
321         /** 
322          * Loads the driver used to access the database
323          * @param driver The driver used to access the database
324          * @throws ResolutionPlugInException If there is a failure to load the driver
325          */
326         public void loadDriver(String driver) throws ResolutionPlugInException {
327                 try {
328                         Class.forName(driver).newInstance();
329                         log.debug("Loading JDBC driver: " + driver);
330                 } catch (Exception e) {
331                         log.error("An error loading database driver: " + e);
332                         throw new ResolutionPlugInException(
333                                 "An IllegalAccessException occured while loading database driver: " + e.getMessage());
334                 }
335                 log.debug("Driver loaded.");
336         }
337
338         private class Log4jPrintWriter extends PrintWriter {
339
340                 private Priority level;
341                 private Logger logger;
342                 private StringBuffer text = new StringBuffer("");
343
344                 private Log4jPrintWriter(Logger logger, org.apache.log4j.Priority level) {
345                         super(System.err);
346                         this.level = level;
347                         this.logger = logger;
348                 }
349
350                 public void close() {
351                         flush();
352                 }
353
354                 public void flush() {
355                         if (!text.toString().equals("")) {
356                                 logger.log(level, text.toString());
357                                 text.setLength(0);
358                         }
359                 }
360
361                 public void print(boolean b) {
362                         text.append(b);
363                 }
364
365                 public void print(char c) {
366                         text.append(c);
367                 }
368
369                 public void print(char[] s) {
370                         text.append(s);
371                 }
372
373                 public void print(double d) {
374                         text.append(d);
375                 }
376
377                 public void print(float f) {
378                         text.append(f);
379                 }
380
381                 public void print(int i) {
382                         text.append(i);
383                 }
384
385                 public void print(long l) {
386                         text.append(l);
387                 }
388
389                 public void print(Object obj) {
390                         text.append(obj);
391                 }
392
393                 public void print(String s) {
394                         text.append(s);
395                 }
396
397                 public void println() {
398                         if (!text.toString().equals("")) {
399                                 logger.log(level, text.toString());
400                                 text.setLength(0);
401                         }
402                 }
403
404                 public void println(boolean x) {
405                         text.append(x);
406                         logger.log(level, text.toString());
407                         text.setLength(0);
408                 }
409
410                 public void println(char x) {
411                         text.append(x);
412                         logger.log(level, text.toString());
413                         text.setLength(0);
414                 }
415
416                 public void println(char[] x) {
417                         text.append(x);
418                         logger.log(level, text.toString());
419                         text.setLength(0);
420                 }
421
422                 public void println(double x) {
423                         text.append(x);
424                         logger.log(level, text.toString());
425                         text.setLength(0);
426                 }
427
428                 public void println(float x) {
429                         text.append(x);
430                         logger.log(level, text.toString());
431                         text.setLength(0);
432                 }
433
434                 public void println(int x) {
435                         text.append(x);
436                         logger.log(level, text.toString());
437                         text.setLength(0);
438                 }
439
440                 public void println(long x) {
441                         text.append(x);
442                         logger.log(level, text.toString());
443                         text.setLength(0);
444                 }
445
446                 public void println(Object x) {
447                         text.append(x);
448                         logger.log(level, text.toString());
449                         text.setLength(0);
450                 }
451
452                 public void println(String x) {
453                         text.append(x);
454                         logger.log(level, text.toString());
455                         text.setLength(0);
456                 }
457         }
458 }
459 /**
460  * The default attribute extractor. 
461  */
462
463 class DefaultAE implements JDBCAttributeExtractor {
464
465         private static Logger log = Logger.getLogger(DefaultAE.class.getName());
466
467         /**
468          * Method of extracting the attributes from the supplied result set.
469          *
470          * @param ResultSet The result set from the query which contains the attributes
471          * @return BasicAttributes as objects containing all the attributes
472          * @throws JDBCAttributeExtractorException If there is a complication in retrieving the attributes
473          */
474         public BasicAttributes extractAttributes(ResultSet rs) throws JDBCAttributeExtractorException {
475                 BasicAttributes attributes = new BasicAttributes();
476
477                 try {
478                         ResultSetMetaData rsmd = rs.getMetaData();
479                         int numColumns = rsmd.getColumnCount();
480                         log.debug("Number of returned columns: " + numColumns);
481
482                         for (int i = 1; i <= numColumns; i++) {
483                                 String columnName = rsmd.getColumnName(i);
484                                 String columnType = rsmd.getColumnTypeName(i);
485                                 Object columnValue = rs.getObject(columnName);
486                                 log.debug(
487                                         "("
488                                                 + i
489                                                 + ". ColumnType = "
490                                                 + columnType
491                                                 + ") "
492                                                 + columnName
493                                                 + " -> "
494                                                 + (columnValue != null ? columnValue.toString() : "(null)"));
495                                 attributes.put(new BasicAttribute(columnName, columnValue));
496                         }
497                 } catch (SQLException e) {
498                         log.error("An ERROR occured while retrieving result set meta data");
499                         throw new JDBCAttributeExtractorException(
500                                 "An ERROR occured while retrieving result set meta data: " + e.getMessage());
501                 }
502
503                 // Check for multiple rows.
504                 try {
505                         if (rs.next())
506                                 throw new JDBCAttributeExtractorException("Query returned more than one row.");
507                 } catch (SQLException e) {
508                         //TODO don't squelch this error!!!
509                 }
510
511                 return attributes;
512         }
513 }
514
515 class DefaultStatementCreator implements JDBCStatementCreator {
516
517         private static Logger log = Logger.getLogger(DefaultStatementCreator.class.getName());
518
519         public void create(
520                 PreparedStatement preparedStatement,
521                 Principal principal,
522                 String requester,
523                 Dependencies depends)
524                 throws JDBCStatementCreatorException {
525
526                 try {
527                         log.debug("Creating prepared statement.  Substituting principal: (" + principal.getName() + ")");
528                         preparedStatement.setString(1, principal.getName());
529                 } catch (SQLException e) {
530                         log.error("Encountered an error while creating prepared statement: " + e);
531                         throw new JDBCStatementCreatorException(
532                                 "Encountered an error while creating prepared statement: " + e.getMessage());
533                 }
534         }
535 }
536
537
538
539
540