dbProcs.Getter.java Source code

Introduction

Here is the source code for dbProcs.Getter.java

Source

package dbProcs;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Locale;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.owasp.esapi.ESAPI;
import org.owasp.esapi.Encoder;

import utils.ScoreboardStatus;

/** 
 * Used to retrieve information from the Database
 * <br/><br/>
 * This file is part of the Security Shepherd Project.
 * 
 * The Security Shepherd project is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.<br/>
 * 
 * The Security Shepherd project is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.<br/>
 * 
 * You should have received a copy of the GNU General Public License
 * along with the Security Shepherd project.  If not, see <http://www.gnu.org/licenses/>. 
 *  @author Mark               
 */
public class Getter {
    private static org.apache.log4j.Logger log = Logger.getLogger(Getter.class);
    /**
     * Used for scoreboards / progress bars
     */
    private static int widthOfUnitBar = 11; //px
    private static int fieldTrainingCap = 45;

    private static int privateCap = 80;

    private static int corporalCap = 105;

    private static int sergeantCap = 130;

    private static int lieutenantCap = 145;

    private static int majorCap = 175;

    private static int admiralCap = 999; //everything above Major is Admiral

    /**
     * This method hashes the user submitted password and sends it to the database.
     * The database does the rest of the work, including Brute Force prevention.
     * @param userName The submitted user name to be used in authentication process
     * @param password The submitted password in plain text to be used in authentication
     * @return A string array made up of nothing or information to be consumed by the initiating authentication process.
     */

    public static String[] authUser(String ApplicationRoot, String userName, String password) {
        String[] result = null;
        log.debug("$$$ Getter.authUser $$$");
        log.debug("userName = " + userName);

        boolean userFound = false;
        boolean goOn = false;
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            //See if user Exists
            CallableStatement callstmt = conn.prepareCall("call userFind(?)");
            log.debug("Gathering userFind ResultSet");
            callstmt.setString(1, userName);
            ResultSet userFind = callstmt.executeQuery();
            log.debug("Opening Result Set from userFind");
            try {
                userFind.next();
                log.debug("User Found"); //User found if a row is in the database, this line will not work if the result set is empty
                userFound = true;
            } catch (Exception e) {
                log.debug("User did not exist");
                userFound = false;
            }
            if (userFound) {
                //Authenticate User
                callstmt = conn.prepareCall("call authUser(?, ?)");
                log.debug("Gathering authUser ResultSet");
                callstmt.setString(1, userName);
                callstmt.setString(2, password);
                ResultSet loginAttempt = callstmt.executeQuery();
                log.debug("Opening Result Set from authUser");
                try {
                    loginAttempt.next();
                    goOn = true; //Valid password for user submitted
                } catch (SQLException e) {
                    //... Outer Catch has preference to this one for some reason... This code is never reached!
                    // But I'll leave it here just in case. That includes the else block if goOn is false
                    log.debug("Incorrect Credentials");
                    goOn = false;
                }
                if (goOn) {
                    //ResultSet Not Empty => Credentials Correct
                    result = new String[5];
                    result[0] = loginAttempt.getString(1); //Id
                    result[1] = loginAttempt.getString(2); //userName
                    result[2] = loginAttempt.getString(3); //role
                    result[4] = loginAttempt.getString(6); //classId
                    if (loginAttempt.getBoolean(5)) //Checking for temp password flag, if true, index View will prompt to change
                        result[3] = "true";
                    else
                        result[3] = "false";
                    if (!result[1].equals(userName)) //If somehow this functionality has been compromised to sign in as other users, this will limit the expoitability. But the method is sql injection safe, so it should be ok
                    {
                        log.fatal("User Name used (" + userName + ") and User Name retrieved (" + result[1]
                                + ") were not the Same. Nulling Result");
                        result = null;
                    } else {
                        log.debug("User '" + userName + "' has logged in");
                        //Before finishing, check if user had a badlogin history, if so, Clear it
                        if (loginAttempt.getInt(4) > 0) {
                            log.debug("Clearing Bad Login History");
                            callstmt = conn.prepareCall("call userBadLoginReset(?)");
                            callstmt.setString(1, result[0]);
                            callstmt.execute();
                            log.debug("userBadLoginReset executed!");
                        }
                    }
                    //User has logged in, or a Authentication Bypass was detected... You never know! Better safe than sorry   
                    return result;
                }
            }
        } catch (SQLException e) {
            log.error("Login Failure: " + e.toString());
            result = null;
            //Lagging Response
        }
        Database.closeConnection(conn);
        log.debug("$$$ End authUser $$$");
        return result;
    }

    /**
     * Used to determine if a user has completed a module already
     * @param ApplicationRoot The current running context of an application
     * @param moduleId The module identifier 
     * @param userId The user identifier
     * @return The module name of the module IF the user has not completed AND the user has previously opened the challenge. 
     */
    public static String checkPlayerResult(String ApplicationRoot, String moduleId, String userId) {
        log.debug("*** Getter.checkPlayerResult ***");

        String result = null;
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            log.debug("Preparing userCheckResult call");
            CallableStatement callstmnt = conn.prepareCall("call userCheckResult(?, ?)");
            callstmnt.setString(1, moduleId);
            callstmnt.setString(2, userId);
            log.debug("Executing userCheckResult");
            ResultSet resultSet = callstmnt.executeQuery();
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.debug("userCheckResult Failure: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END checkPlayerResult ***");
        return result;
    }

    /**
     * Used to decipher whether or not a user exists as a player
     * @param userId The user identifier of the player to be found
     * @return A boolean reflecting the state of existence of the player
     */
    public static boolean findPlayerById(String ApplicationRoot, String userId) {
        log.debug("*** Getter.findPlayerById ***");
        boolean userFound = false;
        //Get connection
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call playerFindById(?)");
            log.debug("Gathering playerFindById ResultSet");
            callstmt.setString(1, userId);
            ResultSet userFind = callstmt.executeQuery();
            log.debug("Opening Result Set from playerFindById");
            userFind.next(); //This will throw an exception if player not found
            log.debug("Player Found: " + userFind.getString(1)); //This line will not execute if player not found
            userFound = true;
        } catch (Exception e) {
            log.error("Player did not exist: " + e.toString());
            userFound = false;
        }
        Database.closeConnection(conn);
        log.debug("*** END findPlayerById ***");
        return userFound;
    }

    /**
     * Used to gather all module information for internal functionality. This method is used in creating View's or in control class operations
     * @param ApplicationRoot The current runing context of the application
     * @return An ArrayList of String arrays that contain the module identifier, module name, module type and module category of each module in the core database.
     */
    public static ArrayList<String[]> getAllModuleInfo(String ApplicationRoot) {
        log.debug("*** Getter.getAllModuleInfo ***");
        ArrayList<String[]> modules = new ArrayList<String[]>();

        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetAll()");
            log.debug("Gathering moduleGetAll ResultSet");
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetAll");
            int i = 0;
            while (resultSet.next()) {
                String[] result = new String[4];
                i++;
                result[0] = resultSet.getString(1); //moduleId
                result[1] = resultSet.getString(2); //moduleName
                result[2] = resultSet.getString(3); //moduleType
                result[3] = resultSet.getString(4); //mdouleCategory
                modules.add(result);
            }
            log.debug("Returning Array list with " + i + " entries.");
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getAllModuleInfo ***");
        return modules;
    }

    /**
     * Returns HTML menu for challenges. Challenges are only referenced by their id, 
     * The user will have to go through another servlet to get the module's View address
     * @param ApplicationRoot The current running context of the application
     * @return HTML menu for challenges   
     */
    public static String getChallenges(String ApplicationRoot, String userId, Locale lang) {
        log.debug("*** Getter.getChallenges ***");
        String output = new String();
        //Getting Translated Level Names
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
        //Encoder to prevent XSS
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleAllInfo(?, ?)");
            callstmt.setString(1, "challenge");
            callstmt.setString(2, userId);
            log.debug("Gathering moduleAllInfo ResultSet");
            ResultSet challenges = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleAllInfo");
            String challengeCategory = new String();
            int rowNumber = 0; // Identifies the first row, ie the start of the list. This is slightly different output to every other row
            while (challenges.next()) {
                if (!challengeCategory.equalsIgnoreCase(challenges.getString(2))) {
                    challengeCategory = challenges.getString(2);
                    //log.debug("New Category Detected: " + challengeCategory);
                    if (rowNumber > 0) //output prepared for Every row after row 1
                        output += "</ul></li><li><a href='javascript:;' class='challengeHeader' >"
                                + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                                + "</a><ul class='challengeList' style='display: none;'>";
                    else //output prepared for First row in entire challenge
                        output += "<li><a href='javascript:;' class='challengeHeader'>"
                                + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                                + "</a><ul class='challengeList' style='display: none;'>";
                    //log.debug("Compiling Challenge Category - " + challengeCategory);
                }
                output += "<li>"; //Starts next LI element
                if (challenges.getString(4) != null) {
                    output += "<img src='css/images/completed.png'/>"; //Completed marker
                } else {
                    output += "<img src='css/images/uncompleted.png'/>"; //Incomplete marker
                }
                //Final out put compilation
                output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(challenges.getString(3))
                        + "' href='javascript:;'>"
                        + encoder.encodeForHTML(bundle.getString(challenges.getString(1))) + "</a>";
                output += "</li>";
                rowNumber++;
            }
            //Check if output is empty
            if (output.isEmpty()) {
                output = "<li><a href='javascript:;'>No challenges found</a></li>";
            } else {
                log.debug("Appending End tags");
                output += "</ul></li>";
            }
        } catch (Exception e) {
            log.error("Challenge Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getChallenges() ***");
        return output;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @return The amount of classes currently existing in the database
     */
    public static int getClassCount(String ApplicationRoot) {
        int result = 0;
        ResultSet resultSet = null;
        log.debug("*** Getter.getClassCount ***");
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call classCount()");
            log.debug("Gathering classCount ResultSet");
            resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from classCount");
            resultSet.next();
            result = resultSet.getInt(1);
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = 0;
        }
        Database.closeConnection(conn);
        log.debug("*** END getClassCount");
        return result;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @return Result set containing class info in the order classId, className and then classYear
     */
    public static ResultSet getClassInfo(String ApplicationRoot) {
        ResultSet result = null;
        log.debug("*** Getter.getClassInfo (All Classes) ***");
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call classesGetData()");
            log.debug("Gathering classesGetData ResultSet");
            result = callstmt.executeQuery();
            log.debug("Returning Result Set from classesGetData");
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        log.debug("*** END getClassInfo");
        return result;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @param classId The identifier of the class
     * @return String Array with Class information with the format of {name, year}
     */
    public static String[] getClassInfo(String ApplicationRoot, String classId) {
        String[] result = new String[2];
        log.debug("*** Getter.getClassInfo (Single Class) ***");
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call classFind(?)");
            callstmt.setString(1, classId);
            log.debug("Gathering classFind ResultSet");
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from classFind");
            resultSet.next();
            result[0] = resultSet.getString(1);//Name
            result[1] = resultSet.getString(2);//Year
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        log.debug("*** END getClassInfo");
        return result;
    }

    /**
     * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other. URLs are contained in IFRAME tags
     * @param ApplicationRoot The current running context of the application
     * @param classId Identifier of the class to populate the forum with
     * @param moduleId The module in which to return the forum for
     * @param bundle Strings Package for the Language Local of the user making the request
     * @return A HTML table of a Class's CSRF Submissions for a specific module
     */
    public static String getCsrfForumWithIframe(String ApplicationRoot, String classId, String moduleId,
            ResourceBundle bundle) {
        log.debug("*** Getter.getCsrfForum ***");
        log.debug("Getting stored messages from class: " + classId);
        Encoder encoder = ESAPI.encoder();
        String htmlOutput = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            if (classId != null) {
                CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)");
                log.debug("Gathering resultMessageByClass ResultSet");
                callstmt.setString(1, classId);
                callstmt.setString(2, moduleId);
                ResultSet resultSet = callstmt.executeQuery();
                log.debug("resultMessageByClass executed");

                //Table Header
                htmlOutput = "<table><tr><th>" + bundle.getString("forum.userName") + "</th><th>"
                        + bundle.getString("forum.message") + "</th></tr>";

                log.debug("Opening Result Set from resultMessageByClass");
                int counter = 0;
                while (resultSet.next()) {
                    counter++;
                    //Table content
                    htmlOutput += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1))
                            + "</td><td><iframe sandbox=\"allow-scripts allow-forms\" src=\""
                            + encoder.encodeForHTMLAttribute(resultSet.getString(2)) + "\"></iframe></td></tr>";
                }
                if (counter > 0)
                    log.debug("Added a " + counter + " row table");
                else
                    log.debug("No results from query");
                //Table end
                htmlOutput += "</table>";
            } else {
                log.error("User with Null Class detected");
                htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
            }
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            htmlOutput = "<p>" + bundle.getString("error.occurred ") + "</p>";
        } catch (Exception e) {
            log.fatal("Could not return CSRF Forum: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getCsrfForum ***");
        return htmlOutput;
    }

    /**
     * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other. URLs are contained in IMG tags
     * @param ApplicationRoot The current running context of the application
     * @param classId Identifier of the class to populate the forum with
     * @param moduleId The module in which to return the forum for
     * @param bundle The strings package for the language of the user
     * @return A HTML table of a Class's CSRF Submissions for a specific module
     */
    public static String getCsrfForumWithImg(String ApplicationRoot, String classId, String moduleId,
            ResourceBundle bundle) {
        log.debug("*** Getter.getCsrfForum ***");
        log.debug("Getting stored messages from class: " + classId);
        Encoder encoder = ESAPI.encoder();
        String htmlOutput = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            if (classId != null) {
                CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)");
                log.debug("Gathering resultMessageByClass ResultSet");
                callstmt.setString(1, classId);
                callstmt.setString(2, moduleId);
                ResultSet resultSet = callstmt.executeQuery();
                log.debug("resultMessageByClass executed");

                //Table Header
                htmlOutput = "<table><tr><th>" + bundle.getString("forum.userName") + "</th><th>"
                        + bundle.getString("forum.image") + "</th></tr>";

                log.debug("Opening Result Set from resultMessageByClass");
                int counter = 0;
                while (resultSet.next()) {
                    counter++;
                    //Table content
                    htmlOutput += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1))
                            + "</td><td><img src=\"" + encoder.encodeForHTMLAttribute(resultSet.getString(2))
                            + "\"/></td></tr>";
                }
                if (counter > 0)
                    log.debug("Added a " + counter + " row table");
                else
                    log.debug("No results from query");
                //Table end
                htmlOutput += "</table>";
            } else {
                log.error("User with Null Class detected");
                htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
            }
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            htmlOutput = "<p>" + bundle.getString("error.occurred") + "</p>";
        } catch (Exception e) {
            log.fatal("Could not return CSRF Forum: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getCsrfForum ***");
        return htmlOutput;
    }

    /**
     * Used to present a modules feedback, including averages and raw results.
     * @param applicationRoot The current running context of the application.
     * @param moduleId The module identifier
     * @return A HTML table of the feedback for a specific module
     */
    public static String getFeedback(String applicationRoot, String moduleId) {
        log.debug("*** Getter.getFeedback ***");

        String result = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            log.debug("Preparing userUpdateResult call");
            CallableStatement callstmnt = conn.prepareCall("call moduleFeedback(?)");
            callstmnt.setString(1, moduleId);
            log.debug("Executing moduleFeedback");
            ResultSet resultSet = callstmnt.executeQuery();
            int resultAmount = 0;
            int before = 0;
            int after = 0;
            int difficulty = 0;
            boolean color = true;
            while (resultSet.next()) {
                if (resultSet.getString(1) != null) {
                    resultAmount++;
                    difficulty += resultSet.getInt(3);
                    before += resultSet.getInt(4);
                    after += resultSet.getInt(5);
                    result += "<tr ";
                    if (color) //Alternate row color
                    {
                        color = !color;
                        result += "BGCOLOR='A878EF'";
                    } else {
                        color = !color;
                        result += "BGCOLOR='D4BCF7'";
                    }
                    //A row off information
                    result += "><td>" + encoder.encodeForHTML(resultSet.getString(1)) + "</td><td>"
                            + encoder.encodeForHTML(resultSet.getString(2)) + "</td><td>" + resultSet.getInt(3)
                            + "</td><td>" + resultSet.getInt(4) + "</td><td>" + resultSet.getInt(5) + "</td><td>"
                            + encoder.encodeForHTML(resultSet.getString(6)) + "</td></tr>";
                }
            }
            if (resultAmount > 0)//Table header
                result = "<table><tr><th>Player</th><th>Time</th><th>Difficulty</th><th>Before</th><th>After</th><th>Comments</th></tr>"
                        + "<tr><td>Average</td><td></td><td>" + difficulty / resultAmount + "</td><td>"
                        + before / resultAmount + "</td><td>" + after / resultAmount + "</td><td></td></tr>"
                        + result + "<table>";
            else // If empty, Blank output
                result = new String();
        } catch (SQLException e) {
            log.error("moduleFeedback Failure: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getFeedback ***");
        return result;
    }

    /**
     * This method prepares the incremental module menu. This is when Security Shepherd is in "Game Mode".
     * Users are presented with one uncompleted module at a time. This method also returns a script to be executed every time the menu is chanegd.
     * This is script defines the animation and operations to be carried out when the menu is interacted with
     * @param ApplicationRoot The running context of the application.
     * @param userId The user identifier of the user.
     * @param csrfToken The cross site request forgery token
     * @return A HTML menu of a users current module progress and a script for interaction with this menu
     */
    public static String getIncrementalModules(String ApplicationRoot, String userId, String lang,
            String csrfToken) {
        log.debug("*** Getter.getIncrementalChallenges ***");
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);

        Locale.setDefault(new Locale("en"));
        Locale locale = new Locale(lang);
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", locale);
        ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);

        try {
            CallableStatement callstmt = conn.prepareCall("call moduleIncrementalInfo(?)");
            callstmt.setString(1, userId);
            log.debug("Gathering moduleIncrementalInfo ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleIncrementalInfo");
            boolean lastRow = false;
            boolean completedModules = false;

            //Preparing first Category header; "Completed"
            output = "<li><a id='completedList' href='javascript:;'><div class='menuButton'>"
                    + bundle.getString("getter.button.completed") + "</div></a>\n"
                    + "<ul id='theCompletedList' style='display: none;' class='levelList'>";

            while (modules.next() && !lastRow) {
                //For each row, prepair the modules the users can select
                if (modules.getString(4) != null) //If not Last Row
                {
                    completedModules = true;
                    output += "<li>";
                    output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                            + "' href='javascript:;'>"
                            + encoder.encodeForHTML(levelNames.getString(modules.getString(1))) + "</a>";
                    output += "</li>";
                } else {
                    lastRow = true;
                    //Last Row - Highlighed Next Challenge
                    if (completedModules) {
                        output += "</ul></li><li>";
                    } else {
                        //NO completed modules, so dont show any...
                        output = new String();
                    }

                    //Second category - Uncompleted
                    output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                            + "' href='javascript:;'>" + "<div class='menuButton'>"
                            + bundle.getString("getter.button.nextChallenge") + "</div>" + "</a>";
                    output += "</li>";
                }
            }

            if (!lastRow) //If true, then the user has completed all challenges
            {
                output += "<h2 id='uncompletedList'><a href='javascript:;'>"
                        + bundle.getString("getter.button.finished") + "</a></h2>\n" + "</li>";
            }
            if (output.isEmpty()) //If this method has gone so far without any output, create a error message
            {
                output = "<li><a href='javascript:;'>" + bundle.getString("getter.button.noModulesFound")
                        + "</a></li>";
            } else //final tags to ensure valid HTML
            {
                log.debug("Appending End tags");
                //output += "</ul></li>"; //Commented Out to prevent Search Box being pushed into Footer
            }

            //This is the script for menu interaction
            output += "<script>applyMenuButtonActionsCtfMode('" + encoder.encodeForHTML(csrfToken) + "', \""
                    + encoder.encodeForHTML(bundle.getString("generic.text.sorryError")) + "\");</script>";
        } catch (Exception e) {
            log.error("Challenge Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getIncrementalChallenges() ***");
        return output;
    }

    /**
     * This method prepares the incremental module menu. This is when Security Shepherd is in "Game Mode".
     * Users are presented with one uncompleted module at a time. This method does not return the JS script describing how the menu used should work
     * @param ApplicationRoot The running context of the application.
     * @param userId The user identifier of the user.
     * @param csrfToken The cross site request forgery token
     * @return A HTML menu of a users current module progress and a script for interaction with this menu
     */
    public static String getIncrementalModulesWithoutScript(String ApplicationRoot, String userId, String lang,
            String csrfToken) {
        log.debug("*** Getter.getIncrementalChallengesWithoutScript ***");
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);

        Locale.setDefault(new Locale("en"));
        Locale locale = new Locale(lang);
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", locale);
        ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);

        try {
            CallableStatement callstmt = conn.prepareCall("call moduleIncrementalInfo(?)");
            callstmt.setString(1, userId);
            log.debug("Gathering moduleIncrementalInfo ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleIncrementalInfo");
            boolean lastRow = false;
            boolean completedModules = false;

            //Preparing first Category header; "Completed"
            output = "<li><a id='completedList' href='javascript:;'><div class='menuButton'>"
                    + bundle.getString("getter.button.completed") + "</div></a>\n"
                    + "<ul id='theCompletedList' style='display: none;' class='levelList'>";

            while (modules.next() && !lastRow) {
                //For each row, prepair the modules the users can select
                if (modules.getString(4) != null) //If not Last Row
                {
                    completedModules = true;
                    output += "<li>";
                    output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                            + "' href='javascript:;'>"
                            + encoder.encodeForHTML(levelNames.getString(modules.getString(1))) + "</a>";
                    output += "</li>";
                } else {
                    lastRow = true;
                    //Last Row - Highlighed Next Challenge
                    if (completedModules) {
                        output += "</ul></li><li>";
                    } else {
                        //NO completed modules, so dont show any...
                        output = new String();
                    }

                    //Second category - Uncompleted
                    output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(modules.getString(3))
                            + "' href='javascript:;'>" + "<div class='menuButton'>"
                            + bundle.getString("getter.button.nextChallenge") + "</div>" + "</a>";
                    output += "</li>";
                }
            }

            if (!lastRow) //If true, then the user has completed all challenges
            {
                output += "<h2 id='uncompletedList'><a href='javascript:;'>"
                        + bundle.getString("getter.button.finished") + "</a></h2>\n" + "</li>";
            }
            if (output.isEmpty()) //If this method has gone so far without any output, create a error message
            {
                output = "<li><a href='javascript:;'>" + bundle.getString("getter.button.noModulesFound")
                        + "</a></li>";
            } else //final tags to ensure valid HTML
            {
                log.debug("Appending End tags");
                //output += "</ul></li>"; //Commented Out to prevent Search Box being pushed into Footer
            }
        } catch (Exception e) {
            log.error("Challenge Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getIncrementalChallengesWithoutScript() ***");
        return output;
    }

    /**
     * Use to return the current progress of a class in JSON format with information like userid, user name and score
     * @param applicationRoot The current running context of the application
     * @param classId The identifier of the class to use in lookup
     * @return A JSON representation of a class's score in the order {id, username, userTitle, score, scale, place, order, 
     * goldmedalcount, goldDisplay, silverMedalCount, silverDisplay, bronzeDisplay, bronzeMedalCount}
     */
    @SuppressWarnings("unchecked")
    public static String getJsonScore(String applicationRoot, String classId) {
        log.debug("classId: " + classId);
        String result = new String();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            Encoder encoder = ESAPI.encoder();
            //Returns User's: Name, # of Completed modules and Score
            CallableStatement callstmnt = null;
            if (ScoreboardStatus.getScoreboardClass().isEmpty() && !ScoreboardStatus.isClassSpecificScoreboard())
                callstmnt = conn.prepareCall("call totalScoreboard()"); //Open Scoreboard not based on a class
            else {
                callstmnt = conn.prepareCall("call classScoreboard(?)"); //Class Scoreboard based on classId
                callstmnt.setString(1, classId);
            }
            //log.debug("Executing classScoreboard");
            ResultSet resultSet = callstmnt.executeQuery();
            JSONArray json = new JSONArray();
            JSONObject jsonInner = new JSONObject();
            int resultAmount = 0;
            int prevPlace = 0;
            int prevScore = 0;
            int prevGold = 0;
            int prevSilver = 0;
            int prevBronze = 0;
            float baseBarScale = 0; //
            float tieBreaker = 0;
            while (resultSet.next()) //For each user in a class
            {
                resultAmount++;
                jsonInner = new JSONObject();
                if (resultSet.getString(1) != null) {
                    int place = resultAmount;
                    int score = resultSet.getInt(3);
                    int goldMedals = resultSet.getInt(4);
                    int silverMedals = resultSet.getInt(5);
                    int bronzeMedals = resultSet.getInt(6);
                    if (resultAmount == 1) //First Place is Returned First, so this will be the biggest bar on the scoreboard
                    {
                        int highscore = score;
                        //log.debug("Current Highscore Listing is " + highscore);
                        //Use the high score to scale the width of the bars for the whole scoreboard
                        float maxBarScale = 1.02f; //High Score bar will have a scale of 1 //This will get used when a scale is added to the scoreboard
                        baseBarScale = highscore * maxBarScale;
                        //setting up variables for Tie Scenario Placings
                        prevPlace = 1;
                        prevScore = score;
                    } else {
                        //Does this score line match the one before (Score and Medals)? if so the place shouldnt change
                        if (score == prevScore && goldMedals == prevGold && silverMedals == prevSilver
                                && bronzeMedals == prevBronze) {
                            place = prevPlace;
                            tieBreaker = tieBreaker + 0.01f;
                        } else {
                            prevScore = score;
                            prevPlace = place;
                            prevGold = goldMedals;
                            prevSilver = silverMedals;
                            prevBronze = bronzeMedals;
                            tieBreaker = 0;
                        }
                    }
                    String displayMedal = new String("display: inline;");
                    String goldDisplayStyle = new String("display: none;");
                    String silverDisplayStyle = new String("display: none;");
                    String bronzeDisplayStyle = new String("display: none;");
                    if (goldMedals > 0)
                        goldDisplayStyle = displayMedal;
                    if (silverMedals > 0)
                        silverDisplayStyle = displayMedal;
                    if (bronzeMedals > 0)
                        bronzeDisplayStyle = displayMedal;

                    int barScale = (int) ((score * 100) / baseBarScale); //bar scale is the percentage the bar should be of the row's context (Highest Possible is depends on scale set in maxBarScale. eg: maxBarScale = 1.1 would mean the max scale would be 91% for a single row)

                    String userMedalString = new String();
                    if (goldMedals > 0 || silverMedals > 0 || bronzeMedals > 0) {
                        userMedalString += " holding ";
                        if (goldMedals > 0)
                            userMedalString += goldMedals + " gold";
                        if (silverMedals > 0) {
                            if (goldMedals > 0) //Medals Before, puncuate
                            {
                                if (bronzeMedals > 0) //more medals after silver? Comma
                                {
                                    userMedalString += ", ";
                                } else //Say And
                                {
                                    userMedalString += " and ";
                                }
                            }
                            userMedalString += silverMedals + " silver";
                        }
                        if (bronzeMedals > 0) {
                            if (goldMedals > 0 || silverMedals > 0) //Medals Before?
                            {
                                userMedalString += " and ";
                            }
                            userMedalString += bronzeMedals + " bronze";
                        }
                        //Say Medal(s) at the end of the string
                        userMedalString += " medal";
                        if (goldMedals + silverMedals + bronzeMedals > 1)
                            userMedalString += "s";
                    }

                    jsonInner.put("id", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Id
                    jsonInner.put("username", new String(encoder.encodeForHTML(resultSet.getString(2)))); //User Name
                    jsonInner.put("userTitle", new String(encoder.encodeForHTML(resultSet.getString(2)) + " with "
                            + score + " points" + userMedalString)); //User name encoded for title attribute
                    jsonInner.put("score", new Integer(score)); //Score
                    jsonInner.put("scale", barScale); //Scale of score bar
                    jsonInner.put("place", place); //Place on board
                    jsonInner.put("order", (place + tieBreaker)); //Order on board
                    jsonInner.put("goldMedalCount", new Integer(goldMedals));
                    jsonInner.put("goldDisplay", goldDisplayStyle);
                    jsonInner.put("silverMedalCount", new Integer(silverMedals));
                    jsonInner.put("silverDisplay", silverDisplayStyle);
                    jsonInner.put("bronzeMedalCount", new Integer(bronzeMedals));
                    jsonInner.put("bronzeDisplay", bronzeDisplayStyle);
                    //log.debug("Adding: " + jsonInner.toString());
                    json.add(jsonInner);
                }
            }
            if (resultAmount > 0)
                result = json.toString();
            else
                result = new String();
        } catch (SQLException e) {
            log.error("getJsonScore Failure: " + e.toString());
            result = null;
        } catch (Exception e) {
            log.error("getJsonScore Unexpected Failure: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        //log.debug("*** END getJsonScore ***");
        return result;
    }

    /**
     * Used to gather a menu of lessons for a user, including markers for each lesson they have completed or not completed
     * @param ApplicationRoot The current running context of the application
     * @param userId Identifier of the user
     * @return HTML lesson menu for Open Floor Plan.
     */
    public static String getLessons(String ApplicationRoot, String userId, Locale lang) {
        log.debug("*** Getter.getLesson ***");
        //Getting Translated Level Names
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            //Get the lesson modules
            CallableStatement callstmt = conn.prepareCall("call lessonInfo(?)");
            callstmt.setString(1, userId);
            log.debug("Gathering lessonInfo ResultSet for user " + userId);
            ResultSet lessons = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleAllInfo");
            while (lessons.next()) {
                //log.debug("Adding " + lessons.getString(1));
                output += "<li>";
                //Markers for completion
                if (lessons.getString(4) != null) {
                    output += "<img src='css/images/completed.png'/>";
                } else {
                    output += "<img src='css/images/uncompleted.png'/>";
                }
                //Prepare lesson output
                output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(lessons.getString(3))
                        + "' href='javascript:;'>" + encoder.encodeForHTML(bundle.getString(lessons.getString(1)))
                        + "</a>";
                output += "</li>";
            }
            //If no output has been found, return an error message
            if (output.isEmpty()) {
                output = "<li><a href='javascript:;'>No lessons found</a></li>";
            } else {
                log.debug("Lesson List returned");
            }
        } catch (Exception e) {
            log.error("lesson Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getLesson() ***");
        return output;
    }

    /**
     * This method returns the address of a module based on the module identifier submitted. 
     * If user has not accessed this level before, they are put down as starting the level at this time.
     * If the level is a client side attack, or other issues that cannot be abused to return a result key (like XSS, CSRF or network sniffing)
     * the address is of the core server. Otherwise the modules sit on the vulnerable application server
     * @param ApplicationRoot The current running context of the application
     * @param moduleId Identifier of the module the to return
     * @param userId The identifier of the user that wants to get the module
     * @return The module address
     */
    public static String getModuleAddress(String ApplicationRoot, String moduleId, String userId) {
        log.debug("*** Getter.getModuleAddress ***");
        String output = new String();
        String type = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetHash(?, ?)");
            callstmt.setString(1, moduleId);
            callstmt.setString(2, userId);
            log.debug("Gathering moduleGetHash ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetHash");
            modules.next(); //Exception thrown if no hash was found
            //Set Type. Used to ensure the URL points at the correct directory
            if (modules.getString(3).equalsIgnoreCase("challenge")) {
                type = "challenges";
            } else {
                type = "lessons";
            }
            output = type + "/" + modules.getString(1) + ".jsp";
        } catch (Exception e) {
            log.error("Module Hash Retrieval: " + e.toString());
            log.error("moduleID = " + moduleId);
            log.error("userID = " + userId);
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleAddress() ***");
        return output;
    }

    /**
     * Retrieves the module category based on the moduleId submitted
     * @param ApplicationRoot The current running context of the application
     * @param moduleId The id of the module that 
     * @return
     */
    public static String getModuleCategory(String ApplicationRoot, String moduleId) {
        log.debug("*** Getter.getModuleResult ***");
        String theCategory = null;
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            PreparedStatement prepstmt = conn
                    .prepareStatement("SELECT moduleCategory FROM modules WHERE moduleId = ?");
            prepstmt.setString(1, moduleId);
            ResultSet moduleFind = prepstmt.executeQuery();
            moduleFind.next();
            theCategory = moduleFind.getString(1);
        } catch (Exception e) {
            log.error("Module did not exist: " + e.toString());
            theCategory = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleCategory ***");
        return theCategory;
    }

    /**
     * @param applicationRoot The current running context of the application.
     * @param moduleId The identifier of a module
     * @return The hash of the module specified
     */
    public static String getModuleHash(String applicationRoot, String moduleId) {
        log.debug("*** Getter.getModuleHash ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetHashById(?)");
            log.debug("Gathering moduleGetHash ResultSet");
            callstmt.setString(1, moduleId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetHash");
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.error("Could not execute moduleGetHash: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleHash ***");
        return result;
    }

    /**
     * Convert module hash to ID
     * @param ApplicationRoot The current running context of the application
     * @param moduleHash The module hash to use for look up
     * @return The identifier of the module with the module hash of the moduleHash parameter
     */
    public static String getModuleIdFromHash(String ApplicationRoot, String moduleHash) {
        log.debug("*** Getter.getModuleIdFromHash ***");
        log.debug("Getting ID from Hash: " + moduleHash);
        String result = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetIdFromHash(?)");
            log.debug("Gathering moduleGetIdFromHash ResultSet");
            callstmt.setString(1, moduleHash);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetIdFromHash");
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleIdFromHash ***");
        return result;
    }

    /**
     * Returns true if a module has a hard coded key, false if server encrypts it
     * @param ApplicationRoot The current running context of the application
     * @param moduleId The id of the module 
     * @return Returns true if a module has a hard coded key, false if server encrypts it
     */
    public static boolean getModuleKeyType(String ApplicationRoot, String moduleId) {
        log.debug("*** Getter.getModuleKeyType ***");
        boolean theKeyType = true;
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            PreparedStatement prepstmt = conn
                    .prepareStatement("SELECT hardcodedKey FROM modules WHERE moduleId = ?");
            prepstmt.setString(1, moduleId);
            ResultSet moduleFind = prepstmt.executeQuery();
            moduleFind.next();
            theKeyType = moduleFind.getBoolean(1);
            if (theKeyType)
                log.debug("Module has hard coded Key");
            else
                log.debug("Module has user specific Key");
        } catch (Exception e) {
            log.error("Module did not exist: " + e.toString());
            theKeyType = true;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleKeyType ***");
        return theKeyType;
    }

    /**
     * This method retrieves the i18n local key for a module's name.
     * @param applicationRoot Application Running Context
     * @param moduleId ID of the module to lookup
     * @return Locale key for the Module's Name.
     */
    public static String getModuleNameLocaleKey(String applicationRoot, String moduleId) {
        log.debug("*** Getter.getModuleNameLocaleKey ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetNameLocale(?)");
            log.debug("Gathering moduleGetNameLocale ResultSet");
            callstmt.setString(1, moduleId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetNameLocale");
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.error("Could not execute moduleGetNameLocale: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleNameLocaleKey ***");
        return result;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @param moduleId Identifier of module
     * @return The db stored solution key value for the moduleId submitted
     */
    public static String getModuleResult(String ApplicationRoot, String moduleId) {
        log.debug("*** Getter.getModuleResult ***");
        String moduleFound = null;
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call moduleGetResult(?)");
            log.debug("Gathering moduleGetResult ResultSet");
            callstmt.setString(1, moduleId);
            ResultSet moduleFind = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetResult");
            moduleFind.next();
            log.debug("Module " + moduleFind.getString(1) + " Found");
            moduleFound = moduleFind.getString(2);
        } catch (Exception e) {
            log.error("Module did not exist: " + e.toString());
            moduleFound = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleResult ***");
        return moduleFound;
    }

    /**
     * Returns the result key for a module using the module's hash for the lookup procedure.
     * @param ApplicationRoot The current running context of the application
     * @param moduleHash The hash to use for module look up
     * @return The db stored solution key value for the moduleHash submited
     */
    public static String getModuleResultFromHash(String ApplicationRoot, String moduleHash) {
        log.debug("*** Getter.getModuleResultFromHash ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            log.debug("hash '" + moduleHash + "'");
            CallableStatement callstmt = conn.prepareCall("call moduleGetResultFromHash(?)");
            log.debug("Gathering moduleGetResultFromHash ResultSet");
            callstmt.setString(1, moduleHash);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleGetResultFromHash");
            resultSet.next();
            result = resultSet.getString(1);

        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleResultFromHash ***");
        return result;
    }

    /**
     * Used in creating functionality that requires a user to select a module. 
     * This method only prepares the option tags for this type of input. It must still be wrapped in select tags.
     * @param ApplicationRoot The current running context of the application
     * @return All modules in HTML option tags
     */
    public static String getModulesInOptionTags(String ApplicationRoot) {
        log.debug("*** Getter.getModulesInOptionTags ***");
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            PreparedStatement callstmt = conn.prepareStatement(
                    "SELECT moduleId, moduleName FROM modules ORDER BY moduleCategory, moduleName;");
            log.debug("Gathering moduleAllInfo ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleAllInfo");
            while (modules.next()) {
                //Each module name is embed in option tags, with a value of their module identifier
                output += "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                        + encoder.encodeForHTML(modules.getString(2)) + "</option>\n";
            }
        } catch (Exception e) {
            log.error("Challenge Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getModulesInOptionTags() ***");
        return output;
    }

    /**
     * Used in creating functionality that requires a user to select a module. 
     * This method only prepares the option tags for this type of input. It must still be wrapped in select tags.
     * @param ApplicationRoot The current running context of the application
     * @return All modules in HTML option tags ordered by incrementalRank
     */
    public static String getModulesInOptionTagsCTF(String ApplicationRoot) {
        log.debug("*** Getter.getModulesInOptionTags ***");
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            PreparedStatement callstmt = conn
                    .prepareStatement("SELECT moduleId, moduleName FROM modules ORDER BY incrementalRank;");
            log.debug("Gathering moduleAllInfo ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleAllInfo");
            while (modules.next()) {
                //Each module name is embed in option tags, with a value of their module identifier
                output += "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                        + encoder.encodeForHTML(modules.getString(2)) + "</option>\n";
            }
        } catch (Exception e) {
            log.error("Challenge Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        log.debug("*** END getModulesInOptionTags() ***");
        return output;
    }

    /**
     * Used to return a module cheat sheet
     * @param ApplicationRoot The current running context of the application
     * @param moduleId The identifier of the module to return the cheat sheet for
     * @param lang The Locale the user has enabled
     * @return String[] containing {ModuleName, CheatSheetSolution}
     */
    public static String[] getModuleSolution(String ApplicationRoot, String moduleId, Locale lang) {
        log.debug("*** Getter.getModuleSolution ***");
        String[] result = new String[2];
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        //Getting Translations
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.cheatsheets.solutions", lang);
        try {
            CallableStatement callstmt = conn.prepareCall("call cheatSheetGetSolution(?)");
            log.debug("Gathering cheatSheetGetSolution ResultSet");
            callstmt.setString(1, moduleId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from cheatSheetGetSolution");
            resultSet.next();
            result[0] = resultSet.getString(1);
            result[1] = bundle.getString(resultSet.getString(2));

        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getModuleSolution ***");
        return result;
    }

    /**
     * This method returns modules in option tags in different &lt;select&gt; elements depending on their current open/closed status. 
     * The output assumes it is contained in a table context
     * @param ApplicationRoot The Running Context of the Application
     * @return Tr/td elements containing a moduleStatusMenu that has lists of the current open and closed modules
     */
    public static String getModuleStatusMenu(String ApplicationRoot) {
        log.debug("*** Getter.getModuleStatusMenu ***");
        String openModules = new String();
        String closedModules = new String();
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            //Get the modules
            CallableStatement callstmt = conn.prepareCall("call moduleAllStatus()");
            log.debug("Gathering moduleAllStatus ResultSet");
            ResultSet modules = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleAllStatus");
            while (modules.next()) {
                String theModule = "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                        + encoder.encodeForHTML(modules.getString(2)) + "</option>\n";
                if (modules.getString(3).equalsIgnoreCase("open")) {
                    //Module is Open currently, so add it to the open side of the list
                    openModules += theModule;
                } else {
                    //If it is not open: It must be closed (NULL or not)
                    closedModules += theModule;
                }
            }
            //This is the actual output: It assumes a <table> environment
            output = "<tr><th>To Open</th><th>To Close</th></tr><tr>\n"
                    + "<td><select style='width: 300px; height: 200px;' multiple id='toOpen'>" + closedModules
                    + "</select></td>\n" + "<td><select style='width: 300px; height: 200px;' multiple id='toClose'>"
                    + openModules + "</select></td>\n" + "</tr>\n";
            log.debug("Module Status Menu returned");
        } catch (Exception e) {
            log.error("Module Status Menu: " + e.toString());
        }
        Database.closeConnection(conn);
        return output;
    }

    /**
     * This method returns the module categories in option tags that are to be open or closed in a &lt;select&gt; element for administration manipulation
     * @param ApplicationRoot
     * @return Module Category List for Html (&lt;SELECT&gt; element)
     */
    public static String getOpenCloseCategoryMenu(String ApplicationRoot) {
        log.debug("*** Getter.getOpenCloseCategoryMenu ***");
        String theModules = new String();
        String output = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            //Get the modules
            CallableStatement callstmt = conn
                    .prepareCall("SELECT DISTINCT moduleCategory FROM modules ORDER BY moduleCategory");
            ResultSet modules = callstmt.executeQuery();
            while (modules.next()) {
                String theModule = "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                        + encoder.encodeForHTML(modules.getString(1)) + "</option>\n";
                theModules += theModule;
            }
            //This is the actual output: It assumes a <table> environment
            output = "<select style='width: 300px; height: 200px;' multiple id='toDo'>" + theModules
                    + "</select>\n";
            log.debug("Module Category Menu returned");
        } catch (Exception e) {
            log.error("Module Status Menu: " + e.toString());
        }
        Database.closeConnection(conn);
        return output;
    }

    /**
     * This method is used to gather users according by class. Thanks to MySQL syntax, where class = null will return nothing, is null must be used.
     *  <br/>is 'validClass' will Error, = 'validclass' must be used.<br/>
     * So there are two procedures this method calls. One that handles null classes, one that does not
     * @param ClassId Identifier of class
     * @param ApplicationRoot The current running context of the application
     * @return ResultSet that contains users for the selected class in the formate {userId, userName, userAddress}
     */
    public static ResultSet getPlayersByClass(String ApplicationRoot, String classId) {
        ResultSet result = null;
        log.debug("*** Getter.getPlayersByClass (Single Class) ***");
        log.debug("classId: '" + classId + "'");
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = null;
            if (classId != null) {
                log.debug("Gathering playersByClass ResultSet");
                callstmt = conn.prepareCall("call playersByClass(?)");
                callstmt.setString(1, classId);
                log.debug("Returning Result Set from playersByClass");
            } else {
                log.debug("Gathering playersWithoutClass ResultSet");
                callstmt = conn.prepareCall("call playersWithoutClass()");
                log.debug("Returning Result Set from playersByClass");
            }
            ResultSet resultSet = callstmt.executeQuery();
            result = resultSet;
            resultSet.next();
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        log.debug("*** END getPlayersByClass");
        return result;
    }

    /**
     * Used to present the progress of a class in a series of loading bars
     * @param applicationRoot The current running context of the application
     * @param classId The identifier of the class to use in lookup
     * @return A HTML representation of a class's progress in the application
     */
    public static String getProgress(String applicationRoot, String classId) {
        log.debug("*** Getter.getProgress ***");

        String result = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            log.debug("Preparing userProgress call");
            CallableStatement callstmnt = conn.prepareCall("call userProgress(?)");
            callstmnt.setString(1, classId);
            log.debug("Executing userProgress");
            ResultSet resultSet = callstmnt.executeQuery();
            int resultAmount = 0;
            while (resultSet.next()) //For each user in a class
            {
                resultAmount++;
                if (resultSet.getString(1) != null) {
                    result += "<tr><td>" + encoder.encodeForHTML(resultSet.getString(1)) + //Output their progress
                            "</td><td><div style='background-color: #A878EF; heigth: 25px; width: "
                            + widthOfUnitBar * resultSet.getInt(2) + "px;'>" + "<font color='white'><strong>"
                            + resultSet.getInt(2);
                    if (resultSet.getInt(2) > 6)
                        result += " Modules";
                    result += "</strong></font></div></td></tr>";
                }
            }
            if (resultAmount > 0)
                result = "<table><tr><th>Player</th><th>Progress</th></tr>" + result + "</table>";
            else
                result = new String();
        } catch (SQLException e) {
            log.error("getProgress Failure: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getProgress ***");
        return result;
    }

    /**
     * Use to return the current progress of a class in JSON format with information like user name, score and completed modules
     * @param applicationRoot The current running context of the application
     * @param classId The identifier of the class to use in lookup
     * @return A JSON representation of a class's progress in the application
     */
    @SuppressWarnings("unchecked")
    public static String getProgressJSON(String applicationRoot, String classId) {
        log.debug("*** Getter.getProgressJSON ***");

        String result = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            log.debug("Preparing userProgress call");
            //Returns User's: Name, # of Completed modules and Score
            CallableStatement callstmnt = conn.prepareCall("call userProgress(?)");
            callstmnt.setString(1, classId);
            log.debug("Executing userProgress");
            ResultSet resultSet = callstmnt.executeQuery();
            JSONArray json = new JSONArray();
            JSONObject jsonInner = new JSONObject();
            int resultAmount = 0;
            while (resultSet.next()) //For each user in a class
            {
                resultAmount++;
                jsonInner = new JSONObject();
                if (resultSet.getString(1) != null) {
                    jsonInner.put("userName", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Name
                    jsonInner.put("progressBar", new Integer(resultSet.getInt(2) * widthOfUnitBar)); //Progress Bar Width
                    jsonInner.put("score", new Integer(resultSet.getInt(3))); //Score
                    log.debug("Adding: " + jsonInner.toString());
                    json.add(jsonInner);
                }
            }
            if (resultAmount > 0)
                result = json.toString();
            else
                result = new String();
        } catch (SQLException e) {
            log.error("getProgressJSON Failure: " + e.toString());
            result = null;
        } catch (Exception e) {
            log.error("getProgressJSON Unexpected Failure: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getProgressJSON ***");
        return result;
    }

    private static int getTounnamentSectionFromRankNumber(int rankNumber) {
        if (rankNumber < fieldTrainingCap)
            return 1;
        else if (rankNumber < privateCap)
            return 2;
        else if (rankNumber < corporalCap)
            return 3;
        else if (rankNumber < sergeantCap)
            return 4;
        else if (rankNumber < lieutenantCap)
            return 5;
        else if (rankNumber < majorCap)
            return 6;
        else if (rankNumber < admiralCap)
            return 7;
        else
            return 7; //Max level is 7.
    }

    /**
     * This method prepares the Tournament module menu. This is when Security Shepherd is in "Tournament Mode".
     * Users are presented with a list of that are specified as open. 
     * @param ApplicationRoot The running context of the application.
     * @param userId The user identifier of the user.
     * @param csrfToken The cross site request forgery token
     * @return A HTML menu of a users current module progress and a script for interaction with this menu
     */
    public static String getTournamentModules(String ApplicationRoot, String userId, Locale lang) {
        log.debug("*** Getter.getTournamentModules ***");
        String levelMasterList = new String();
        Encoder encoder = ESAPI.encoder();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        //Getting Translations
        ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", lang);
        ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
        try {

            String listEntry = new String();
            //Get the modules
            CallableStatement callstmt = conn.prepareCall("call moduleTournamentOpenInfo(?)");
            callstmt.setString(1, userId);
            log.debug("Gathering moduleTournamentOpenInfo ResultSet for user " + userId);
            ResultSet levels = callstmt.executeQuery();
            log.debug("Opening Result Set from moduleTournamentOpenInfo");
            int currentSection = 0; // Used to identify the first row, as it is slightly different to all other rows for output
            while (levels.next()) {
                //Create Row Entry First
                //log.debug("Adding " + lessons.getString(1));
                listEntry = "<li>";
                //Markers for completion
                if (levels.getString(4) != null) {
                    listEntry += "<img src='css/images/completed.png'/>";
                } else {
                    listEntry += "<img src='css/images/uncompleted.png'/>";
                }
                //Prepare entry output
                listEntry += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(levels.getString(3))
                        + "' href='javascript:;'>"
                        + encoder.encodeForHTML(levelNames.getString(levels.getString(1))) + "</a>\n";
                listEntry += "</li>";
                //What section does this belong in? Current or Next?
                if (getTounnamentSectionFromRankNumber(levels.getInt(5)) > currentSection) {
                    //This level is not in the same level band as the previous level. So a new Level Band Header is required on the master list before we add the entry.
                    //Do we need to close a previous list?
                    if (currentSection != 0) //If a Section Select hasn't been made before, we don't need to close any previous sections
                    {
                        //We've had a section before, so need to close the previous one before we make this new one
                        levelMasterList += "</ul>\n";
                    }
                    //Update the current section to the one we have just added to the list
                    currentSection = getTounnamentSectionFromRankNumber(levels.getInt(5));
                    //Which to Add?
                    switch (currentSection) {
                    case 1: //fieldTraining
                        //log.debug("Starting Field Training List");
                        levelMasterList += "<a id=\"fieldTrainingList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.1") + "</div></a>"
                                + "<ul id=\"theFieldTrainingList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 2: //private
                        //log.debug("Starting Private List");
                        levelMasterList += "<a id=\"privateList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.2") + "</div></a>"
                                + "<ul id=\"thePrivateList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 3: //corporal
                        //log.debug("Starting Corporal List");
                        levelMasterList += "<a id=\"corporalList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.3") + "</div></a>"
                                + "<ul id=\"theCorporalList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 4: //sergeant
                        //log.debug("Starting Sergeant List");
                        levelMasterList += "<a id=\"sergeantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.4") + "</div></a>"
                                + "<ul id=\"theSergeantList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 5: //Lieutenant
                        //log.debug("Starting Lieutenant List");
                        levelMasterList += "<a id=\"lieutenantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.5") + "</div></a>"
                                + "<ul id=\"theLieutenantList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 6: //major
                        //log.debug("Starting Major List");
                        levelMasterList += "<a id=\"majorList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.6") + "</div></a>"
                                + "<ul id=\"theMajorList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    case 7: //admiral
                        //log.debug("Starting Admiral List");
                        levelMasterList += "<a id=\"admiralList\" href=\"javascript:;\"><div class=\"menuButton\">"
                                + bundle.getString("getter.tournamentRank.7") + "</div></a>"
                                + "<ul id=\"theAdmiralList\" style=\"display: none;\" class='levelList'>\n";
                        break;
                    }
                }
                //Now we can add the entry to the level master List and start again
                levelMasterList += listEntry;
                //log.debug("Put level in category: " + currentSection);
            }
            //If no output has been found, return an error message
            if (levelMasterList.isEmpty()) {
                levelMasterList = "<ul><li><a href='javascript:;'>"
                        + bundle.getString("getter.button.noModulesFound") + "</a></li></ul>";
            } else {
                //List is complete, but we need to close the last list we made, which deinfetly exists as the levelmasterList is not empty
                levelMasterList += "</ul>";
                log.debug("Tournament List returned");
            }
        } catch (Exception e) {
            log.error("Tournament List Retrieval: " + e.toString());
        }
        Database.closeConnection(conn);
        return levelMasterList;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @param userName The username of the user
     * @return The class id of the submitted user name
     */
    public static String getUserClassFromName(String ApplicationRoot, String userName) {
        log.debug("*** Getter.getUserClass ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call userClassId(?)");
            log.debug("Gathering userClassId ResultSet");
            callstmt.setString(1, userName);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from userClassId");
            resultSet.next();
            result = resultSet.getString(1);
            log.debug("Found " + result);
        } catch (SQLException e) {
            log.error("Could not execute userClassId: " + e.toString());
            result = new String();
        }
        Database.closeConnection(conn);
        log.debug("*** END getUserClass ***");
        return result;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @param userName The username of the user
     * @return The user id of the submitted user name
     */
    public static String getUserIdFromName(String ApplicationRoot, String userName) {
        log.debug("*** Getter.getUserIdFromName ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call userGetIdByName(?)");
            log.debug("Gathering userGetIdByName ResultSet");
            callstmt.setString(1, userName);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from userGetIdByName");
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getUserIdFromName ***");
        return result;
    }

    /**
     * @param ApplicationRoot The current running context of the application
     * @param userId The identifier of a user
     * @return The user name of the submitted user identifier
     */
    public static String getUserName(String ApplicationRoot, String userId) {
        log.debug("*** Getter.getUserName ***");
        String result = new String();
        Connection conn = Database.getCoreConnection(ApplicationRoot);
        try {
            CallableStatement callstmt = conn.prepareCall("call userGetNameById(?)");
            log.debug("Gathering userGetNameById ResultSet");
            callstmt.setString(1, userId);
            ResultSet resultSet = callstmt.executeQuery();
            log.debug("Opening Result Set from userGetNameById");
            resultSet.next();
            result = resultSet.getString(1);
        } catch (SQLException e) {
            log.error("Could not execute query: " + e.toString());
            result = null;
        }
        Database.closeConnection(conn);
        log.debug("*** END getUserName ***");
        return result;
    }

    /**
     * This method is used to determine if a CSRF level has been completed. 
     * A call is made to the DB that returns the CSRF counter for a level. 
     * If this counter is greater than 0, the level has been completed
     * @param applicationRoot Running context of the application
     * @param moduleHash Hash ID of the CSRF module you wish to check if a user has completed
     * @param userId the ID of the user to check
     * @return True or False value depicting if the user has completed the module
     */
    public static boolean isCsrfLevelComplete(String applicationRoot, String moduleId, String userId) {
        log.debug("*** Setter.isCsrfLevelComplete ***");

        boolean result = false;

        Connection conn = Database.getCoreConnection(applicationRoot);
        try {
            log.debug("Preparing csrfLevelComplete call");
            CallableStatement callstmnt = conn.prepareCall("call csrfLevelComplete(?, ?)");
            callstmnt.setString(1, moduleId);
            callstmnt.setString(2, userId);
            log.debug("moduleId: " + moduleId);
            log.debug("userId: " + userId);
            log.debug("Executing csrfLevelComplete");
            ResultSet resultSet = callstmnt.executeQuery();
            resultSet.next();
            result = resultSet.getInt(1) > 0; // If Result is > 0, then the CSRF level is complete
            if (result)
                log.debug("CSRF Level is complete");
        } catch (SQLException e) {
            log.error("csrfLevelComplete Failure: " + e.toString());
            result = false;
        }
        Database.closeConnection(conn);
        log.debug("*** END isCsrfLevelComplete ***");
        return result;
    }
}