Review

finding-2

database/SocialNetworkDatabaseBoards.java

getBoardList(Connection, String)

class SocialNetworkDatabaseBoards {
	/**
	 * Gets a list of boards that the user has permission to view.
	 * For Admins: Must be within the "admin" list of the board
	 * For Users: Must be within the "RegionPrivileges" list of the board for one region
	 * A user has permission to view a board if it has at least one
	 * region where it has view permissions within that board.
	 * */
	public static String getBoardList(Connection conn, String username) {
		String boardlist = "print Boards:;print \tfreeforall;";

		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet boards = null;
		ResultSet privResult = null;
		boolean sqlex = false;
		try {
			String getRegionPrivs, getRegionAdmins;
			String role = DatabaseAdmin.getUserRole(conn, username);
			
			if (role.equals("admin") || role.equals("sa")) { // an admin
				getRegionAdmins = "SELECT * FROM main.boardadmins WHERE username = ?";
				pstmt = conn.prepareStatement(getRegionAdmins);
				pstmt.setString(1, username);
				privResult = pstmt.executeQuery();
				while (privResult.next()) { // returns true if there is a result set.
					boardlist += "print \t" + privResult.getString("bname") + ";";
				}
				privResult.close();
				pstmt.close();
				privResult = null;
				pstmt = null;
			}
			else if (!role.equals("")) {
				stmt = conn.createStatement();

				/*First, get a list of all the boards*/
				String allBoards = "SELECT bname FROM main.boards";
				boards = stmt.executeQuery(allBoards);

				while (boards.next()) {
					/* For each Board ID, check its RegionPrivileges to see
					 * if there exists one tuple with username = this user
					 */
					String bname = boards.getString("bname");
					getRegionPrivs = "SELECT privilege FROM " 
						+ bname + ".regionprivileges WHERE username = ?";
					pstmt = conn.prepareStatement(getRegionPrivs);
					pstmt.setString(1, username);
					privResult = pstmt.executeQuery();
					if (privResult.next()) { // returns true if there is a result set.
						boardlist += "print \t" + bname + ";";
					}
					privResult.close();
					pstmt.close();
					privResult = null;
					pstmt = null;
				}
			}
			else { //there was an sql exception when getting the role.
				sqlex = true;
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			sqlex = true;
		}
		finally {
			DBManager.closeStatement(stmt);
			DBManager.closePreparedStatement(pstmt);
			DBManager.closeResultSet(boards);
		}
		if (boardlist.equals("print Boards:;print freeforall") && !sqlex) {
			return boardlist;
		}
		else if (sqlex) {
			return "print Error: Database Error while querying viewable boards. Contact an admin.";
		}
		else {
			return boardlist;
		}
	}

}