| Code with Finding: |
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;
}
}
}
class SocialNetworkDatabaseBoards {
public static ArrayList<String> getBoardAdmins(Connection conn, String board) {
if (board.equals("freeforall")) {
return null;
}
ArrayList<String> admins = new ArrayList<String>();
String query = "SELECT * FROM main.boardadmins WHERE bname = ?";
PreparedStatement pstmt = null;
ResultSet adminResults = null;
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, board);
adminResults = pstmt.executeQuery(query);
while (adminResults.next()) {
admins.add(adminResults.getString("username"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBManager.closePreparedStatement(pstmt);
DBManager.closeResultSet(adminResults);
}
return admins;
}
}
|
