| 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;
}
}
}
|
