找到你要的答案

Q:Retrieve a record from a stored procedure, invoked from java.sql.CallableStatement

Q:检索存储过程调用的记录,从java.sql.callablestatement

I am creating a webapp in which a user can set alert for certains network documents. this date is stored in a mysql DB along with a bunch of other data which is encrypted on insert using AES_ENCRYPT.

the prcedure for insert looks like this:

CREATE DEFINER=`service01`@`%` PROCEDURE `insert_secure_alert`(
IN master_secret VARCHAR(100), 
IN path VARCHAR(300),
IN creation DATE,
IN expiration DATE,
IN alert_name VARCHAR(300),
IN alert_comment VARCHAR(300)
)
BEGIN
INSERT INTO `fujitsu_consensus_core`.`alert`
(`id`,
`path`,
`creation`,
`expiration`,
`name`,
`comment`)
VALUES
(0,
AES_ENCRYPT(path, master_secret),
AES_ENCRYPT(creation, master_secret),
AES_ENCRYPT(expiration, master_secret),
AES_ENCRYPT(alert_name, master_secret),
AES_ENCRYPT(alert_comment, master_secret));
END

This is al working just fine, I am however facing some issues when retrieving this data using a stored procedure. the class responsible for this retrieval looks like this:

package com.fujitsu.database;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONObject;
import org.json.simple.parser.ParseException;

import com.fujitsu.consensus.helper.StreamToJson;

public class RetrieveSecureAlert extends HttpServlet{

/**
 * 
 */
private static final long serialVersionUID = -6379032803226212817L;
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException{

    try {
        JSONObject obj = StreamToJson.convertStreamToJson(req.getInputStream());
        String path = (String) obj.get("path");

        CallableStatement cs = AccessObject.getConnectionObject().prepareCall("{CALL `fujitsu_consensus_core`.`retrieve_secure_alert`(?,?}");
        cs.setString(1, path);
        cs.setString(2, "FUJITSU");
        cs.execute();

        ResultSet result = cs.getResultSet();

        System.out.println("RESULT!");
        while(result.next()){
            System.out.println(result.getString("id"));
            System.out.println(result.getString("decryptd_path"));
            System.out.println(result.getString("decrypted_creation"));
            System.out.println(result.getString("decrypted_expiration"));
            System.out.println(result.getString("decrypted_name"));
            System.out.println(result.getString("decrypted_comment"));
        }


    } catch (ParseException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

}

The corresponding mysql stores procdure looks like this:

CREATE DEFINER=`service01`@`%` PROCEDURE `retrieve_secure_alert`(IN path_key VARCHAR(300), IN master_secret VARCHAR(100))
BEGIN

DECLARE v_encrypted_path VARBINARY(300);
DECLARE v_encrypted_creation VARBINARY;
DECLARE v_encrypted_expiration VARBINARY;
DECLARE v_encrypted_name VARBINARY(300);
DECLARE v_encrypted_comment VARBINARY(300);

SELECT path INTO v_encrypted_path FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT creation INTO v_encrypted_creation FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT expiration INTO v_encrypted_expiration FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT `name` INTO v_encrypted_name FROM fujitsu_consensus_core WHERE path = path = AES_ENCRYPT(path_key, master_secret);
SELECT `comment` INTO v_encrypted_comment FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);

SELECT  id,
    AES_DECRYPT(v_encrypted_path, master_secret) AS decryptd_path,
    AES_DECRYPT(v_encrypted_creation, master_secret) AS decrypted_creation,
    AES_DECRYPT(v_encrypted_expiration, master_secret) AS decrypted_expiration,
    AES_DECRYPT(v_encrypted_name, master_secret) AS decrypted_name,
    AES_DECRYPT(v_encrypted_comment, master_secret) AS decrypted_comment
FROM fujitsu_consensus_core.alert
WHERE path = AES_ENCRYPT(path_key, master_secret);
END

The stack trace:

java.sql.SQLException: Unable to retrieve metadata for procedure.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.CallableStatement.extractProcedureName(CallableStatement.java:659)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:498)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:403)
at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4161)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4235)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4209)
at com.fujitsu.database.RetrieveSecureAlert.doPost(RetrieveSecureAlert.java:29)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

So the big question is, how do I retrieve records from a stored procedure in mysql using the java.sql.callablestatement which decrypts my records?

我创建了一个Web应用中,用户可以设定警告说网络文件。这个日期存储在一堆其他的数据是加密的aes_encrypt插入使用MySQL数据库。

插入的过程看起来像这样:

CREATE DEFINER=`service01`@`%` PROCEDURE `insert_secure_alert`(
IN master_secret VARCHAR(100), 
IN path VARCHAR(300),
IN creation DATE,
IN expiration DATE,
IN alert_name VARCHAR(300),
IN alert_comment VARCHAR(300)
)
BEGIN
INSERT INTO `fujitsu_consensus_core`.`alert`
(`id`,
`path`,
`creation`,
`expiration`,
`name`,
`comment`)
VALUES
(0,
AES_ENCRYPT(path, master_secret),
AES_ENCRYPT(creation, master_secret),
AES_ENCRYPT(expiration, master_secret),
AES_ENCRYPT(alert_name, master_secret),
AES_ENCRYPT(alert_comment, master_secret));
END

这是非常好的工作,但是当使用存储过程检索这些数据时,我面临一些问题。负责此检索的类看起来像这样:

package com.fujitsu.database;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONObject;
import org.json.simple.parser.ParseException;

import com.fujitsu.consensus.helper.StreamToJson;

public class RetrieveSecureAlert extends HttpServlet{

/**
 * 
 */
private static final long serialVersionUID = -6379032803226212817L;
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException{

    try {
        JSONObject obj = StreamToJson.convertStreamToJson(req.getInputStream());
        String path = (String) obj.get("path");

        CallableStatement cs = AccessObject.getConnectionObject().prepareCall("{CALL `fujitsu_consensus_core`.`retrieve_secure_alert`(?,?}");
        cs.setString(1, path);
        cs.setString(2, "FUJITSU");
        cs.execute();

        ResultSet result = cs.getResultSet();

        System.out.println("RESULT!");
        while(result.next()){
            System.out.println(result.getString("id"));
            System.out.println(result.getString("decryptd_path"));
            System.out.println(result.getString("decrypted_creation"));
            System.out.println(result.getString("decrypted_expiration"));
            System.out.println(result.getString("decrypted_name"));
            System.out.println(result.getString("decrypted_comment"));
        }


    } catch (ParseException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

}

相应的MySQL存储过程看起来像这样:

CREATE DEFINER=`service01`@`%` PROCEDURE `retrieve_secure_alert`(IN path_key VARCHAR(300), IN master_secret VARCHAR(100))
BEGIN

DECLARE v_encrypted_path VARBINARY(300);
DECLARE v_encrypted_creation VARBINARY;
DECLARE v_encrypted_expiration VARBINARY;
DECLARE v_encrypted_name VARBINARY(300);
DECLARE v_encrypted_comment VARBINARY(300);

SELECT path INTO v_encrypted_path FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT creation INTO v_encrypted_creation FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT expiration INTO v_encrypted_expiration FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);
SELECT `name` INTO v_encrypted_name FROM fujitsu_consensus_core WHERE path = path = AES_ENCRYPT(path_key, master_secret);
SELECT `comment` INTO v_encrypted_comment FROM fujitsu_consensus_core WHERE path = AES_ENCRYPT(path_key, master_secret);

SELECT  id,
    AES_DECRYPT(v_encrypted_path, master_secret) AS decryptd_path,
    AES_DECRYPT(v_encrypted_creation, master_secret) AS decrypted_creation,
    AES_DECRYPT(v_encrypted_expiration, master_secret) AS decrypted_expiration,
    AES_DECRYPT(v_encrypted_name, master_secret) AS decrypted_name,
    AES_DECRYPT(v_encrypted_comment, master_secret) AS decrypted_comment
FROM fujitsu_consensus_core.alert
WHERE path = AES_ENCRYPT(path_key, master_secret);
END

堆栈跟踪:

java.sql.SQLException: Unable to retrieve metadata for procedure.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.CallableStatement.extractProcedureName(CallableStatement.java:659)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:498)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:403)
at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4161)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4235)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4209)
at com.fujitsu.database.RetrieveSecureAlert.doPost(RetrieveSecureAlert.java:29)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

所以最大的问题是,我如何使用我的java.sql.callablestatement解密记录MySQL检索存储过程的记录?

answer1: 回答1:

I found the error however, the sql string in the callablestatement can not contain ` but should contain '

我发现了错误,但是,在CallableStatement SQL字符串不能包含`但应包含“

java  mysql  stored-procedures  callable-statement