portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From woon...@apache.org
Subject svn commit: r1726722 - /portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java
Date Tue, 26 Jan 2016 03:38:08 GMT
Author: woonsan
Date: Tue Jan 26 03:38:08 2016
New Revision: 1726722

URL: http://svn.apache.org/viewvc?rev=1726722&view=rev
Log:
convert sql statement manipulations to prepared statements to avoid any potential vulnerability

Modified:
    portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java

Modified: portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java
URL: http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java?rev=1726722&r1=1726721&r2=1726722&view=diff
==============================================================================
--- portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java
(original)
+++ portals/jetspeed-2/portal/trunk/components/jetspeed-security/src/main/java/org/apache/jetspeed/security/spi/impl/JetspeedPrincipalLookupManagerAbstract.java
Tue Jan 26 03:38:08 2016
@@ -22,7 +22,12 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import javax.swing.text.Segment;
 
 import org.apache.jetspeed.security.JetspeedPrincipal;
 import org.apache.jetspeed.security.JetspeedPrincipalAssociationType;
@@ -50,6 +55,11 @@ public abstract class JetspeedPrincipalL
 
 	static final Logger log = LoggerFactory.getLogger(JetspeedPrincipalLookupManagerAbstract.class);
 
+    private static final String PARAM_PLACEHOLDER_PREFIX = "@@paramPlaceHolder";
+    private static final String PARAM_PLACEHOLDER_SUFFIX = "@@";
+    private static final Pattern PARAM_PLACEHOLDER_PATTERN = Pattern
+            .compile("(" + PARAM_PLACEHOLDER_PREFIX + "\\d+" + PARAM_PLACEHOLDER_SUFFIX +
")");
+
 	/*
 	 * (non-Javadoc)
 	 * 
@@ -58,31 +68,29 @@ public abstract class JetspeedPrincipalL
 	 * getPrincipals(org.apache.jetspeed.security.JetspeedPrincipalQueryContext)
 	 */
 	public JetspeedPrincipalResultList getPrincipals(JetspeedPrincipalQueryContext queryContext)
{
-		String baseSqlStr = this.generateBaseSql(queryContext);
-
-		// create paging sql statement if possible for database based paging
-		String sqlStr = getPagingSql(baseSqlStr, queryContext);
-
 		int numberOfRecords = 0;
 		ArrayList<JetspeedPrincipal> results = new ArrayList<JetspeedPrincipal>();
 		
         Connection conn = null;
-		PreparedStatement pstmt = null;
-		ResultSet rs = null;
+
+        PreparedStatement pstmtForPaging = null;
+        ResultSet rsForPaging = null;
+
+        PreparedStatement pstmtForCount = null;
+        ResultSet rsForCount = null;
 		
 		try {
 			conn = PersistenceBrokerFactory.defaultPersistenceBroker().serviceConnectionManager().getConnection();
+            PreparedStatement [] pstmts = createPagingPreparedStatementAndCountPreparedStatement(conn,
queryContext);
+            pstmtForPaging = pstmts[0];
+            pstmtForCount = pstmts[1];
 
-			pstmt = conn.prepareStatement(sqlStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
-			// pstmt = conn.prepareStatement(sqlStr,
-			// ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
-			pstmt.setFetchSize((int) (queryContext.getOffset() + queryContext.getLength()));
-			rs = pstmt.executeQuery();
-			boolean hasRecords = rs.next();
+			rsForPaging = pstmtForPaging.executeQuery();
+			boolean hasRecords = rsForPaging.next();
 
 			if (hasRecords) {
 				// scroll the result set to the offset
-				scrollToOffset(conn, rs, queryContext.getOffset());
+				scrollToOffset(conn, rsForPaging, queryContext.getOffset());
 				for (int i = 0; i < queryContext.getLength(); i++) {
 					// now materialize the ResultSet into a JetspeedPrincipal
 					RowReader rr = PersistenceBrokerFactory.defaultPersistenceBroker().getClassDescriptor(
@@ -90,38 +98,31 @@ public abstract class JetspeedPrincipalL
 					Map<Object, Object> row = new HashMap<Object, Object>();
 					// TODO: optimize, just retrieve the id from the DB and setup
 					// a JetspeedPrincipal template on that.
-					rr.readObjectArrayFrom(rs, row);
+					rr.readObjectArrayFrom(rsForPaging, row);
 					PersistentJetspeedPrincipal p = (PersistentJetspeedPrincipal) rr.readObjectFrom(row);
 					QueryByCriteria query = new QueryByCriteria(p);
 					p = (PersistentJetspeedPrincipal) PersistenceBrokerFactory.defaultPersistenceBroker()
 							.getObjectByQuery(query);
 					results.add(p);
-					if (!rs.next()) {
+					if (!rsForPaging.next()) {
 						break;
 					}
 				}
 				
-				rs.close();
-                rs = null;
-                pstmt.close();
-                pstmt = null;
-
-				// get the total number of results effected by the query
-				int fromPos = baseSqlStr.toUpperCase().indexOf(" FROM ");
-				if (fromPos >= 0) {
-					baseSqlStr = "select count(SECURITY_PRINCIPAL.PRINCIPAL_ID)" + baseSqlStr.substring(fromPos);
-				}
-				// strip ORDER BY clause
-				int orderPos = baseSqlStr.toUpperCase().indexOf(" ORDER BY ");
-				if (orderPos >= 0) {
-					baseSqlStr = baseSqlStr.substring(0, orderPos);
+				rsForPaging.close();
+				rsForPaging = null;
+                pstmtForPaging.close();
+                pstmtForPaging = null;
+
+				rsForCount = pstmtForCount.executeQuery();
+				while (rsForCount.next()) {
+					numberOfRecords += rsForCount.getInt(1);
 				}
 
-				pstmt = conn.prepareStatement(baseSqlStr);
-				rs = pstmt.executeQuery();
-				while (rs.next()) {
-					numberOfRecords += rs.getInt(1);
-				}
+				rsForCount.close();
+				rsForCount = null;
+				pstmtForCount.close();
+				pstmtForCount = null;
 			}
 		} catch (SQLException e) {
 			log.error("Error reading principal.", e);
@@ -130,21 +131,41 @@ public abstract class JetspeedPrincipalL
 		} catch (LookupException e) {
 			log.error("Error reading principal.", e);
 		} finally {
-            if(rs != null) 
+            if(rsForPaging != null) 
             {
                 try 
                 {
-                    rs.close();
+                    rsForPaging.close();
                 }
                 catch (Exception ignore) 
                 {
                 }
             }
-            if(pstmt != null) 
+            if(pstmtForPaging != null) 
             {
                 try 
                 {
-                    pstmt.close();
+                    pstmtForPaging.close();
+                }
+                catch (Exception ignore) 
+                {
+                }
+            }
+            if(rsForCount != null) 
+            {
+                try 
+                {
+                    rsForCount.close();
+                }
+                catch (Exception ignore) 
+                {
+                }
+            }
+            if(pstmtForCount != null) 
+            {
+                try 
+                {
+                    pstmtForCount.close();
                 }
                 catch (Exception ignore) 
                 {
@@ -165,13 +186,247 @@ public abstract class JetspeedPrincipalL
 		return new JetspeedPrincipalResultList(results, numberOfRecords);
 	}
 
-	/**
+    private String putParamPlaceHolder(Map<String, Object> paramPlaceHolders, Object
value) {
+        String paramPlaceHolderName = PARAM_PLACEHOLDER_PREFIX + paramPlaceHolders.size()
+ PARAM_PLACEHOLDER_SUFFIX;
+        paramPlaceHolders.put(paramPlaceHolderName, value);
+        return paramPlaceHolderName;
+    }
+
+    private PreparedStatement[] createPagingPreparedStatementAndCountPreparedStatement(Connection
conn,
+            JetspeedPrincipalQueryContext queryContext) throws SQLException {
+        String _paramPlaceHolderName = null;
+        Map<String, Object> _paramPlaceHolders = new HashMap<String, Object>();
+
+        String attributeConstraint = null;
+        String fromPart = "SECURITY_PRINCIPAL";
+
+        if (queryContext.getSecurityAttributes() != null) {
+            int cnt = 1;
+
+            for (Map.Entry<String, String> attribute : queryContext.getSecurityAttributes().entrySet())
{
+                if (attributeConstraint == null) {
+                    _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, attribute.getKey());
+                    attributeConstraint = " a" + cnt + ".PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID
AND a" + cnt
+                            + ".ATTR_NAME = " + _paramPlaceHolderName;
+
+                    _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, attribute.getValue());
+                    attributeConstraint += " AND a" + cnt + ".ATTR_VALUE LIKE " + _paramPlaceHolderName;
+                } else {
+                    _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, attribute.getKey());
+                    attributeConstraint += " AND a" + cnt + ".PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID
AND a" + cnt
+                            + ".ATTR_NAME = " + _paramPlaceHolderName;
+
+                    _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, attribute.getValue());
+                    attributeConstraint += " AND a" + cnt + ".ATTR_VALUE LIKE " + _paramPlaceHolderName;
+                }
+
+                fromPart += ", SECURITY_ATTRIBUTE a" + cnt;
+                cnt++;
+            }
+        }
+
+        String constraint = null;
+
+        if (queryContext.getNameFilter() != null && queryContext.getNameFilter().length()
> 0) {
+            _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders,
+                    queryContext.getNameFilter().replace('*', '%'));
+            constraint = "SECURITY_PRINCIPAL.PRINCIPAL_NAME LIKE " + _paramPlaceHolderName;
+        }
+
+        // find principals that are member of one or many roles
+        // the principal must be member in all supplied roles.
+        String roleConstraints = null;
+
+        if (queryContext.getAssociatedRoles() != null && queryContext.getAssociatedRoles().size()
> 0
+                && queryContext.getAssociatedRoles().get(0).length() > 0) {
+            int cnt = 1;
+
+            for (String roleName : queryContext.getAssociatedRoles()) {
+                _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, roleName);
+
+                if (roleConstraints == null) {
+                    roleConstraints = "r" + cnt + ".ASSOC_NAME = '" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' " + "' AND r" + cnt + ".TO_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='role' AND r" + cnt
+                            + ".FROM_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                } else {
+                    roleConstraints = " AND r" + cnt + ".ASSOC_NAME='" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' AND r" + cnt + ".TO_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='role' AND r" + cnt
+                            + ".FROM_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                }
+            }
+
+            fromPart += ", SECURITY_PRINCIPAL_ASSOC r" + cnt + ", SECURITY_PRINCIPAL rp"
+ cnt;
+            cnt++;
+        }
+
+        // find principals that are member of one or many groups
+        // the principal must be member in all supplied groups.
+        String groupConstraints = null;
+
+        if (queryContext.getAssociatedGroups() != null && queryContext.getAssociatedGroups().size()
> 0
+                && queryContext.getAssociatedGroups().get(0).length() > 0) {
+            int cnt = 1;
+
+            for (String groupName : queryContext.getAssociatedGroups()) {
+                _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, groupName);
+
+                if (groupConstraints == null) {
+                    groupConstraints = "r" + cnt + ".ASSOC_NAME='" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' AND r" + cnt + ".TO_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='group' AND r" + cnt
+                            + ".FROM_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                } else {
+                    groupConstraints = " AND r" + cnt + ".ASSOC_NAME='" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' AND r" + cnt + ".TO_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='group' AND r" + cnt
+                            + ".FROM_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                }
+            }
+
+            fromPart += ", SECURITY_PRINCIPAL_ASSOC r" + cnt + ", SECURITY_PRINCIPAL rp"
+ cnt;
+            cnt++;
+        }
+
+        // find principals that contain one or many users
+        // the principal must contain all supplied users.
+        String userConstraints = null;
+
+        if (queryContext.getAssociatedUsers() != null && queryContext.getAssociatedUsers().size()
> 0) {
+            int cnt = 1;
+
+            for (String userName : queryContext.getAssociatedGroups()) {
+                _paramPlaceHolderName = putParamPlaceHolder(_paramPlaceHolders, userName);
+
+                if (userConstraints == null) {
+                    userConstraints = "r" + cnt + ".ASSOC_NAME='" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' AND r" + cnt + ".FROM_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='user' AND r" + cnt + ".TO_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                } else {
+                    userConstraints = " AND r" + cnt + ".ASSOC_NAME='" + JetspeedPrincipalAssociationType.IS_MEMBER_OF
+                            + "' AND r" + cnt + ".FROM_PRINCIPAL_ID=rp" + cnt + ".PRINCIPAL_ID
AND rp" + cnt
+                            + ".PRINCIPAL_NAME LIKE " + _paramPlaceHolderName + " AND rp"
+ cnt
+                            + ".PRINCIPAL_TYPE='group' AND r" + cnt
+                            + ".TO_PRINCIPAL_ID=SECURITY_PRINCIPAL.PRINCIPAL_ID";
+                }
+            }
+
+            fromPart += ", SECURITY_PRINCIPAL_ASSOC r" + cnt + ", SECURITY_PRINCIPAL rp"
+ cnt;
+            cnt++;
+        }
+
+        if (attributeConstraint != null) {
+            if (constraint != null) {
+                constraint += " AND " + attributeConstraint;
+            } else {
+                constraint = attributeConstraint;
+            }
+        }
+
+        if (roleConstraints != null) {
+            if (constraint != null) {
+                constraint += " AND " + roleConstraints;
+            } else {
+                constraint = roleConstraints;
+            }
+        }
+
+        if (groupConstraints != null) {
+            if (constraint != null) {
+                constraint += " AND " + groupConstraints;
+            } else {
+                constraint = groupConstraints;
+            }
+        }
+
+        if (userConstraints != null) {
+            if (constraint != null) {
+                constraint += " AND " + userConstraints;
+            } else {
+                constraint = userConstraints;
+            }
+        }
+
+        String baseSqlStr = "SELECT SECURITY_PRINCIPAL.* from " + fromPart
+                + " WHERE SECURITY_PRINCIPAL.PRINCIPAL_TYPE='" + queryContext.getJetspeedPrincipalType()
+                + "' AND SECURITY_PRINCIPAL.DOMAIN_ID=" + queryContext.getSecurityDomain();
+
+        if (constraint != null) {
+            baseSqlStr += " AND " + constraint;
+        }
+
+        if (queryContext.getOrder() != null && queryContext.getOrder().equalsIgnoreCase("desc"))
{
+            baseSqlStr += " ORDER BY SECURITY_PRINCIPAL.PRINCIPAL_NAME DESC";
+        } else {
+            baseSqlStr += " ORDER BY SECURITY_PRINCIPAL.PRINCIPAL_NAME";
+        }
+
+        StringBuilder preparedSqlBuilder = new StringBuilder(baseSqlStr.length());
+        Matcher matcher;
+        char [] sqlChars = baseSqlStr.toCharArray();
+        List<Object> parameters = new ArrayList<Object>(_paramPlaceHolders.size());
+        Segment segment = new Segment(sqlChars, 0, sqlChars.length);
+
+        for (matcher = PARAM_PLACEHOLDER_PATTERN.matcher(segment); matcher.find(); ) {
+            preparedSqlBuilder.append(segment.subSequence(0, matcher.start())).append('?');
+            _paramPlaceHolderName = matcher.group(1);
+            parameters.add(_paramPlaceHolders.get(_paramPlaceHolderName));
+            segment = (Segment) segment.subSequence(matcher.end(), segment.length());
+            matcher.reset(segment);
+        }
+
+        preparedSqlBuilder.append(segment);
+
+        String preparedSqlStr = preparedSqlBuilder.toString();
+        String sql = getPagingSql(preparedSqlStr, queryContext);
+
+        PreparedStatement pstmtForPaging = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
+                ResultSet.CONCUR_READ_ONLY);
+        for (int i = 0; i < parameters.size(); i++) {
+            pstmtForPaging.setString(i + 1, (String) parameters.get(i));
+        }
+        pstmtForPaging.setFetchSize((int) (queryContext.getOffset() + queryContext.getLength()));
+
+        String countSql = convertToCountQueryStatement(preparedSqlStr);
+
+        PreparedStatement pstmtForCount = conn.prepareStatement(countSql);
+        for (int i = 0; i < parameters.size(); i++) {
+            pstmtForCount.setString(i + 1, (String) parameters.get(i));
+        }
+
+        return new PreparedStatement[] { pstmtForPaging, pstmtForCount };
+    }
+
+    private String convertToCountQueryStatement(String baseSqlStr) {
+        // get the total number of results effected by the query
+        int fromPos = baseSqlStr.toUpperCase().indexOf(" FROM ");
+        String countSql = "SELECT count(SECURITY_PRINCIPAL.PRINCIPAL_ID)" + baseSqlStr.substring(fromPos);
+
+        // strip ORDER BY clause
+        int orderByPos = countSql.toUpperCase().indexOf(" ORDER BY ");
+
+        if (orderByPos >= 0) {
+            countSql = countSql.substring(0, orderByPos);
+        }
+
+        return countSql;
+    }
+
+    /**
 	 * Generate the base SQL syntax for selecting principals. This must not
 	 * contain any database specifics.
 	 * 
 	 * @param queryContext
 	 * @return
+	 * @deprecated Never use this method due to vulnerable SQL statement manipulation.
 	 */
+    @Deprecated
 	protected String generateBaseSql(JetspeedPrincipalQueryContext queryContext) {
 		String attributeConstraint = null;
 		String fromPart = "SECURITY_PRINCIPAL";



---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org


Mime
View raw message