| Code with Finding: |
class SocialNetworkDatabasePosts {
/**
* Verifies whether a post exists in the given board (and region).
*/
public static Boolean postExists(Connection conn, String boardName, String regionName, int postNum) {
PreparedStatement pstmt = null;
ResultSet postResult = null;
String getPost = "";
if (boardName.equals("freeforall")) {
getPost = "SELECT * FROM freeforall.posts " +
"WHERE pid = ?";
}
else {
getPost = "SELECT * FROM " + boardName + ".posts " +
"WHERE pid = ? AND rname = ?";
}
Boolean postExists = null;
try {
pstmt = conn.prepareStatement(getPost);
pstmt.setInt(1, postNum);
if (!boardName.equals("freeforall")) {
pstmt.setString(2, regionName);
}
postResult = pstmt.executeQuery();
postExists = new Boolean(postResult.next());
}
catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getSQLState());
}
finally {
DBManager.closePreparedStatement(pstmt);
}
return postExists;
}
}
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 {
/**
* Inserts the reply for the given post.
* Updates the originating post's dateLastUpdated
* Assumes the board, the region, and the post are valid.
*/
public static String createReply(Connection conn, String username, String contentRaw,
String boardName, String regionName, int postNum) {
String content = SharedKeyCrypto.encrypt(contentRaw);
PreparedStatement createPstmt = null;
String createReply = "";
PreparedStatement getDatePstmt = null;
String getDate = "";
ResultSet dateResult = null;
PreparedStatement updateDatePstmt = null;
String updateDate = "";
if (boardName.equals("freeforall")) {
createReply = "INSERT INTO freeforall.replies " +
"VALUES (?, null, ?, NOW(), ?, ?)";
getDate = "SELECT MAX(dateReplied) FROM freeforall.replies " +
"WHERE pid = ?";
updateDate = "UPDATE freeforall.posts SET dateLastUpdated = ? " +
"WHERE pid = ?";
}
else {
createReply = "INSERT INTO " + boardName + ".replies " +
"VALUES (?, ?, null, ?, NOW(), ?, ?)";
getDate = "SELECT MAX(dateReplied) FROM " + boardName + ".replies " +
"WHERE pid = ? AND rname = ?";
updateDate = "UPDATE " + boardName + ".posts SET dateLastUpdated = ? " +
"WHERE pid = ? AND rname = ?";
}
boolean successInsert = false;
boolean successUpdate = false;
boolean sqlex = false;
try {
conn.setAutoCommit(false);
createPstmt = conn.prepareStatement(createReply);
//calculate a checksum for the content
byte[] contentBytes = null;
try {
contentBytes = contentRaw.getBytes("UTF8");
} catch (UnsupportedEncodingException e) {//should not happen
}
String checksum = CryptoUtil.encode(Hash.generateChecksum(contentBytes));
Arrays.fill(contentBytes, (byte)0x00);
if (boardName.equals("freeforall")) {
createPstmt.setInt(1, postNum);
createPstmt.setString(2, username);
createPstmt.setString(3, content);
createPstmt.setString(4, checksum);
}
else {
createPstmt.setString(1 , regionName);
createPstmt.setInt(2, postNum);
createPstmt.setString(3, username);
createPstmt.setString(4, content);
createPstmt.setString(5, checksum);
}
successInsert = (createPstmt.executeUpdate() == 1);
if (successInsert) {
/* Get the timestamp of the most recent reply!*/
getDatePstmt = conn.prepareStatement(getDate);
getDatePstmt.setInt(1, postNum);
if (!boardName.equals("freeforall")) {
getDatePstmt.setString(2, regionName);
}
dateResult = getDatePstmt.executeQuery();
if (dateResult.next()) {//only expect one result, the max.
/*Update the record with this time*/
updateDatePstmt = conn.prepareStatement(updateDate);
updateDatePstmt.setTimestamp(1, dateResult.getTimestamp("MAX(dateReplied)"));
updateDatePstmt.setInt(2, postNum);
if (!boardName.equals("freeforall")) {
updateDatePstmt.setString(3, regionName);
}
successUpdate = (updateDatePstmt.executeUpdate() == 1);
if (successUpdate) {
conn.commit();
}
else {
conn.rollback();
}
}
else {
conn.rollback();
}
}
else {
conn.rollback();
}
}
catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getSQLState());
DBManager.rollback(conn);
sqlex = true;
}
finally {
DBManager.trueAutoCommit(conn);
DBManager.closePreparedStatement(createPstmt);
DBManager.closePreparedStatement(getDatePstmt);
DBManager.closePreparedStatement(updateDatePstmt);
DBManager.closeResultSet(dateResult);
}
if (!successInsert || !successUpdate || sqlex) {
return "print Error: Database error while inserting reply. Contact an admin";
}
else if (successInsert && successUpdate) {
return "print Reply successfully added. Refresh the post to view";
}
else {
return "print Error: Reply could not be uploaded. If this problem persists, contact an admin";
}
}
}
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;
}
}
|
