Example usage for java.sql CallableStatement setString

List of usage examples for java.sql CallableStatement setString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. setString

Introduction

In this page you can find the example usage for java.sql CallableStatement setString.

Prototype

void setString(String parameterName, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:dbProcs.Getter.java

/**
 * 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// w w w .j a  v a  2 s .c  om
 * @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;
}

From source file:dbProcs.Getter.java

/**
 * 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/*from   w w w.  ja  v a2s . c  om*/
 */
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;
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#verifySecurityData(java.lang.String, java.lang.String, java.util.List)
 *//* w w w.  ja v a2 s  .c  o m*/
public synchronized boolean verifySecurityData(final String userId, final String userGuid,
        final List<String> attributes) throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#verifySecurityData(final String userId, final String userGuid, final List<String> attributes) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userId);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL verifySecurityQuestions(?, ?, ?, ?)}");
        stmt.setString(1, userGuid); // guid
        stmt.setString(2, userId);
        stmt.setString(3, attributes.get(0)); // username
        stmt.setString(4, attributes.get(1)); // username

        if (DEBUG) {
            DEBUGGER.debug("Statement: {}", stmt.toString());
        }

        return stmt.execute();
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainOtpSecret(java.lang.String, java.lang.String)
 *//*from w w  w.ja va2  s .  co m*/
public synchronized String obtainOtpSecret(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainOtpSecret(final String userName, final String userGuid) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userName);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    String otpSecret = null;
    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL getOtpSecret(?, ?)}");
        stmt.setString(1, userGuid); // guid
        stmt.setString(2, userName);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();

                otpSecret = resultSet.getString(1);
            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return otpSecret;
}

From source file:dbProcs.Getter.java

/**
 * 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
 *///w  w w  .ja v  a2s .  c  o  m
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;
}

From source file:dbProcs.Getter.java

/**
 * 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
 *///w  w  w .j av a2 s .c o  m
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;
}

From source file:dbProcs.Getter.java

/**
 * 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/*from  ww  w.  j a  v  a  2  s. c  om*/
 * @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;
}

From source file:dbProcs.Getter.java

/**
 * 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
 *//*from   w ww . j  a va2  s. c  o  m*/
@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;
}

From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java

@Transactional(readOnly = true)
public List<AbstractResponseParametersType> geocoding(final GeocodeRequestType param) {
    HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() {
        public List<AbstractResponseParametersType> doInHibernate(Session session)
                throws HibernateException, SQLException {

            List<AddressType> addressList = param.getAddress();
            List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

            for (AddressType addressType : addressList) {

                // TODO change deprecation?
                @SuppressWarnings("deprecation")
                CallableStatement consulta = session.connection()
                        .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}");

                String street = GeoUtil.extractStreet(addressType);
                String munsub = GeoUtil.extractMunSub(addressType);
                String mun = GeoUtil.extractMun(addressType);
                String subcountry = GeoUtil.extractSubCountry(addressType);
                String country = GeoUtil.extractCountry(addressType);

                consulta.setString(1, street);
                consulta.setString(2, munsub);
                consulta.setString(3, mun);
                consulta.setString(4, subcountry);
                consulta.setString(5, country);

                LOG.debug(consulta);//from  ww w  . j  av  a 2 s . c  o  m

                ResultSet o = consulta.executeQuery();
                GeocodeResponseType grt = new GeocodeResponseType();
                while (o.next()) {
                    GeocodeResponseListType geocode = new GeocodeResponseListType();
                    try {
                        PGgeometry g = (PGgeometry) o.getObject("geometry");
                        Jdbc4Array address = (Jdbc4Array) o.getArray("address");

                        GeocodedAddressType addresstype = new GeocodedAddressType();
                        addresstype.setPoint(PostGisUtils.getReferencedPoint(g));
                        addresstype.setAddress(PostGisUtils.getAddress(address));

                        geocode.getGeocodedAddress().add(addresstype);

                        geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l));

                        grt.getGeocodeResponseList().add(geocode);
                    } catch (Throwable t) {
                        LOG.error("Error extracting data from database.", t);
                    }
                    res_.add(grt);
                }
            }
            return res_;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}

From source file:dbProcs.Getter.java

/**
 * 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
 *//*ww  w . j  a  va  2 s  .c  o  m*/
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;
}