| Code with Finding: |
class SocialNetworkDatabasePosts {
public static Boolean isFFAPostCreator(Connection conn, String username, int post) {
Boolean isCreator = null;
String query = "SELECT postedby FROM freeforall.posts " +
"WHERE pid = ?";
PreparedStatement pstmt = null;
ResultSet result = null;
try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, post);
result = pstmt.executeQuery();
if (result.next()) {
isCreator = new Boolean(username.equals(result.getString("postedby")));
}
} catch (SQLException e) {
isCreator = null;
} finally {
DBManager.closeResultSet(result);
DBManager.closePreparedStatement(pstmt);
}
return isCreator;
}
}
class SocialNetworkDatabasePosts {
/**
* Returns the list of posts within the Free For All board that the
* specified user can see.
*
* A User can see a post if they are:
* - the creator of the post (username == postedBy)
* - granted view privilege in PostsPrivileges
*
* Different from a regular board because you must check each post
* one by one to ensure that the user has the privilege for it.
*/
public static String getPostListFreeForAll(Connection conn, String username) {
String posts = "print Posts:;";
/*Retrieves all posts, joined with their most recent reply*/
Statement getPosts = null;
String getPostsFreeForAll = "SELECT pid, P.postedBy, P.datePosted, P.dateLastUpdated, MAX(R.dateReplied) " +
"FROM freeforall.posts AS P LEFT OUTER JOIN " +
"freeforall.replies as R USING (pid) " +
"GROUP BY pid ORDER BY P.dateLastUpdated DESC";
ResultSet postsResults = null;
/*Retrieves the privilege for a given post and user*/
PreparedStatement getPrivs = null;
String getPostPrivileges = "SELECT privilege " +
"FROM freeforall.postprivileges " +
"WHERE pid = ? AND username = ?";
ResultSet privsResult = null;
boolean sqlex = false;
try {
getPrivs = conn.prepareStatement(getPostPrivileges);
getPosts = conn.createStatement();
postsResults = getPosts.executeQuery(getPostsFreeForAll);
int pid;
String postedBy;
while (postsResults.next()) {
pid = postsResults.getInt("pid");
postedBy = postsResults.getString("P.postedBy");
if (!postedBy.equals(username)) {
getPrivs.setInt(1, pid);
getPrivs.setString(2, username);
privsResult = getPrivs.executeQuery();
/*Only expect one result set*/
if (privsResult.next()) { //user has view or viewpost priv
posts += "print \t" +
(privsResult.getString("privilege").equals("viewpost")? specialStrPostable : "") +
"Post#" + pid + "[" + postsResults.getString("P.postedBy") + "];";
if (postsResults.getTimestamp("MAX(R.dateReplied)") != null) {
posts += "print \t\t" +
"Most Recent Reply at " +
postsResults.getTimestamp("MAX(R.dateReplied)") + ";";
}
}
}
else { //the user is the creator of the post
posts += "print \t" + specialStrCreatedPost +
"Post#" + pid + "[" + postsResults.getString("P.postedBy") + "];";
if (postsResults.getTimestamp("MAX(R.dateReplied)") != null) {
posts += "print \t\t" +
"Most Recent Reply at " +
postsResults.getTimestamp("MAX(R.dateReplied)") + ";";
}
}
}
}
catch (SQLException e) {
e.printStackTrace();
sqlex = true;
}
finally {
DBManager.closeStatement(getPosts);
DBManager.closeResultSet(postsResults);
DBManager.closePreparedStatement(getPrivs);
DBManager.closeResultSet(privsResult);
}
if (posts.equals("print Posts:;") && !sqlex) {
return "print No posts for this board.";
}
else if (sqlex) {
return "print Error: Database Error while querying viewable posts. Contact an admin.";
}
else {
return posts;
}
}
}
class SocialNetworkDatabasePosts {
/** Gets a post from the designated board and region
* with the given post number.
* ASSUMES that the board, region, and post are all valid.
*/
public static String getPost(Connection conn, String username, String boardName,
String regionName, int postNum) {
String getOriginalPost = "";
String getReplies = "";
String postAndReplies = "";
/*No joining of results because of redundancy of data returned*/
if (boardName.equals("freeforall")) {
getOriginalPost = "SELECT * FROM freeforall.posts " +
"WHERE pid = ?";
getReplies = "SELECT * FROM freeforall.replies " +
"WHERE pid = ? ORDER BY dateReplied ASC";
}
else {
getOriginalPost = "SELECT * FROM " + boardName + ".posts " +
"WHERE pid = ? AND rname = ?";
getReplies = "SELECT * FROM " + boardName + ".replies " +
"WHERE pid = ? AND rname = ? ORDER BY dateReplied ASC";
}
PreparedStatement originalPost = null;
ResultSet postResult = null;
PreparedStatement replies = null;
ResultSet repliesResult = null;
boolean sqlex = false;
try {
originalPost = conn.prepareStatement(getOriginalPost);
replies = conn.prepareStatement(getReplies);
originalPost.setInt(1, postNum);
replies.setInt(1, postNum);
if (!boardName.equals("freeforall")) {
originalPost.setString(2, regionName);
replies.setString(2, regionName);
}
postResult = originalPost.executeQuery();
if (postResult.next()) { /*Only expect one post result*/
//Make sure the checksum is correct
if (!Arrays.equals(
Hash.generateChecksum((SharedKeyCrypto.decrypt(postResult.getString("content"))).getBytes("UTF8")),
CryptoUtil.decode(postResult.getString("checksum")))) {
postAndReplies +=
"print ----- Post# " + postNum + "[" + postResult.getString("postedBy") + "]----- " +
postResult.getTimestamp("datePosted").toString() + ";print \t" +
"Content could not be fetched -- Integrity Failure!" + ";";
}
else {
postAndReplies +=
"print ----- Post# " + postNum + "[" + postResult.getString("postedBy") + "]----- " +
postResult.getTimestamp("datePosted").toString() + ";print \t" +
SharedKeyCrypto.decrypt(postResult.getString("content")) + ";";
}
repliesResult = replies.executeQuery();
while (repliesResult.next()) { //Print out all replies
//for each reply, make sure the checksum is correct.
if(!Arrays.equals(
Hash.generateChecksum((SharedKeyCrypto.decrypt(repliesResult.getString("content"))).getBytes("UTF8")),
CryptoUtil.decode(repliesResult.getString("checksum")))) {
postAndReplies += "print ----- Reply[" + repliesResult.getString("repliedBy") + "] ----- " +
repliesResult.getTimestamp("dateReplied").toString() + ";print \t" +
"Content could not be fetched -- Integrity Failure!" + ";";
}
else {
postAndReplies += "print ----- Reply[" + repliesResult.getString("repliedBy") + "] ----- " +
repliesResult.getTimestamp("dateReplied").toString() + ";print \t" +
SharedKeyCrypto.decrypt(repliesResult.getString("content")) + ";";
}
}
}
// if there's no postResult, the post DNE.
}
catch (SQLException e) {
e.printStackTrace();
sqlex = true;
} catch (UnsupportedEncodingException e) {
// This should not happen.
}
if (postAndReplies.equals("") && !sqlex) {
return "print Error: Post does not exist. Refresh. If the problem persists, contact an admin.";
}
else if (sqlex) {
return "print Error: Database error while querying post and replies. Contact an admin.";
}
else return postAndReplies;
}
}
|
