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:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *//*ww w .  j av  a2 s  .c om*/
public synchronized List<String> obtainSecurityData(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainSecurityData(final String userName, final String userGuid) throws AuthenticatorException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> userSecurity = 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 getUserByAttribute(?, ?)}");
        stmt.setString(1, userName); // guid
        stmt.setInt(2, 0); // count

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

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

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

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

                while (resultSet.next()) {
                    if (StringUtils.equals(resultSet.getString(2), userName)) {
                        String cn = resultSet.getString(1);
                        String username = resultSet.getString(2);

                        if (DEBUG) {
                            DEBUGGER.debug("String: {}", cn);
                            DEBUGGER.debug("String: {}", username);
                        }

                        resultSet.close();
                        stmt.close();

                        // found the user we want
                        stmt = sqlConn.prepareCall("{ CALL getSecurityQuestions(?, ?) }");
                        stmt.setString(1, username); // common name
                        stmt.setString(2, cn);

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

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

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

                            if (resultSet.next()) {
                                userSecurity = new ArrayList<String>(
                                        Arrays.asList(resultSet.getString(1), resultSet.getString(2)));

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

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

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#addService(java.util.List)
 *///ww  w  . j a v  a  2  s  .c o  m
public synchronized boolean addService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#addService(final List<String> data) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL addNewService(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}

From source file:com.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#updateService(java.util.List)
 *///from w w w.  j  av a2 s.  c o m
public synchronized boolean updateService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#updateService(final List<String> data) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL updateServiceData(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}

From source file:dbProcs.Getter.java

/**
 * 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.
 *///from   w ww. j a v a 2  s .co  m

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

From source file:dbProcs.Getter.java

/**
 * 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
 *//*from   w w w. j  av a 2 s  .  com*/
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;
}

From source file:dbProcs.Getter.java

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

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listServicesForGroup(java.lang.String)
 *///from   w  w w .  j  a v a2s .c  om
public synchronized List<String> listServicesForGroup(final String groupName) throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME
            + "#listServicesForGroup(final String groupName) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> serviceList = null;

    try {
        sqlConn = dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL listServicesForGroup(?)}");
        stmt.setString(1, groupName);

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

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

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

            if (resultSet.next()) {
                resultSet.first();
                serviceList = new ArrayList<String>();

                for (String service : StringUtils.split(resultSet.getString(1), ",")) // single row response
                {
                    if (DEBUG) {
                        DEBUGGER.debug("Service: {}", service);
                    }

                    serviceList.add(service);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", serviceList);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

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

        if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return serviceList;
}

From source file:com.rosy.bill.dao.hibernate.SimpleHibernateDao.java

/**
 * ?String//from w w w  .  j  a v a  2 s . c  o m
 * @param execStr
 * @param params
 * @param outIndex
 * @return
 */
@SuppressWarnings("deprecation")
public String getNumByExecuteProc(String execStr, String[] params, int outIndex) {
    java.sql.CallableStatement cstmt = null;
    String rtn = null;
    try {
        cstmt = this.getSession().connection().prepareCall(execStr);
        cstmt.registerOutParameter(outIndex, oracle.jdbc.driver.OracleTypes.LONGVARCHAR);
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                if (i != outIndex) {
                    cstmt.setString(i, params[i].toString());
                }
            }
        }
        cstmt.executeQuery();
        java.sql.ResultSet rs = (java.sql.ResultSet) cstmt.getObject(outIndex);
        if (rs != null) {
            rtn = rs.getString(0);
        }
    } catch (HibernateException e1) {
        e1.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return rtn;
}

From source file:dbProcs.Getter.java

/**
 * 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//from   www. ja v  a 2 s .c o  m
 * @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;
}

From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#removeServer(java.lang.String)
 *///from w w w  .  j a v a2s.  c o m
public synchronized boolean removeServer(final String serverGuid) throws SQLException {
    final String methodName = IServerDataDAO.CNAME
            + "#removeServer(final String serverGuid) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("serverGuid: {}", serverGuid);
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL removeServerFromAssets(?)}");
        stmt.setString(1, serverGuid);

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

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return isComplete;
}