src-main.org.awakefw.sql.servlet.sql.ServerCallableStatement Maven / Gradle / Ivy

Go to download
/*
 * This file is part of Awake SQL. 
 * Awake SQL: Remote JDBC access over HTTP.                                    
 * Copyright (C) 2013,  KawanSoft SAS
 * (http://www.kawansoft.com). All rights reserved.                    
 *                                                                         
 * Awake SQL 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.         
 *              
 * Awake SQL 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          
 * Lesser General Public License for more details.       
 *                                  
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see .
 *
 * If you develop commercial activities using Awake SQL, you must: 
 * a) disclose and distribute all source code of your own product,
 * b) license your own product under the GNU General Public License.
 * 
 * You can be released from the requirements of the license by
 * purchasing a commercial license. Buying such a license will allow you 
 * to ship Awake SQL with your closed source products without disclosing 
 * the source code.
 *
 * For more information, please contact KawanSoft SAS at this
 * address: [email protected]
 * 
 * Any modifications to this file must keep this entire header
 * intact.
 */
package org.awakefw.sql.servlet.sql;

import java.io.BufferedWriter;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.IOUtils;
import org.awakefw.commons.api.server.AwakeCommonsConfigurator;
import org.awakefw.commons.server.util.AwakeServerLogger;
import org.awakefw.file.api.server.AwakeFileConfigurator;
import org.awakefw.file.api.util.AwakeDebug;
import org.awakefw.file.util.Tag;
import org.awakefw.file.util.TransferStatus;
import org.awakefw.sql.api.server.AwakeSqlConfigurator;
import org.awakefw.sql.json.StatementHolder;
import org.awakefw.sql.json.no_obfuscation.CallableStatementHolder;
import org.awakefw.sql.json.no_obfuscation.CallableStatementHolderTransportJson;
import org.awakefw.sql.servlet.AwakeSqlConfiguratorCall;
import org.awakefw.sql.util.CallableParms;

/**
 * @author Alexandre Becquereau
 */
public class ServerCallableStatement {

    private static boolean DEBUG = AwakeDebug
	    .isSet(ServerCallableStatement.class);

    public static String CR_LF = System.getProperty("line.separator");

    private AwakeCommonsConfigurator awakeCommonsConfigurator = null;

    /** The File Configurator (for Blobs/Clobs) */
    private AwakeFileConfigurator awakeFileConfigurator = null;

    private AwakeSqlConfigurator awakeSqlConfigurator;
    private Connection connection;
    private String username;
    private CallableStatementHolder callableStatementHolder;

    /** The http request */
    private HttpServletRequest request;

    /** If true, we will ask to to a raw CallableStatement.execute() */
    private boolean isExecuteRaw = false;

    /**
     * Default Constructor
     * 
     * @param request
     *            the http request
     * @param awakeCommonsConfigurator
     *            the commons file configurator (for encryption)
     * @param awakeFileConfigurator
     *            the FileConfigurator (for blobs/clobs)
     * @param awakeSqlConfigurator
     *            the SqlConfigurator (for security check)
     * @param connection
     *            the Jdbc connection
     * @param username
     *            the client username
     * @param isExecuteRaw
     *            if true, we ask for a raw execute
     * @param sqlOrderAndParmsStore
     *            the Sql order and parms
     */

    public ServerCallableStatement(HttpServletRequest request,
	    AwakeCommonsConfigurator awakeCommonsConfigurator,
	    AwakeFileConfigurator awakeFileConfigurator,
	    AwakeSqlConfigurator awakeSqlConfigurator, Connection connection,
	    String username, CallableStatementHolder statementHolder,
	    boolean isExecuteRaw) throws SQLException {

	if (awakeCommonsConfigurator == null) {
	    throw new IllegalArgumentException(Tag.AWAKE_PRODUCT_FAIL
		    + "awakeCommonsConfigurator can not be null!");
	}

	if (awakeSqlConfigurator == null) {
	    throw new IllegalArgumentException(Tag.AWAKE_PRODUCT_FAIL
		    + "awakeSqlConfigurator can not be null!");
	}

	if (connection == null) {
	    throw new IllegalArgumentException(Tag.AWAKE_PRODUCT_FAIL
		    + "SQL Connection can not be null!");
	}

	if (username == null) {
	    throw new IllegalArgumentException(Tag.AWAKE_PRODUCT_FAIL
		    + "username can not be null!");
	}

	if (statementHolder == null) {
	    throw new IllegalArgumentException(Tag.AWAKE_PRODUCT_FAIL
		    + "statementHolder can not be null!");
	}

	this.awakeCommonsConfigurator = awakeCommonsConfigurator;
	this.awakeFileConfigurator = awakeFileConfigurator;
	this.awakeSqlConfigurator = awakeSqlConfigurator;
	this.connection = connection;
	this.username = username;
	this.callableStatementHolder = statementHolder;
	this.request = request;

	this.isExecuteRaw = isExecuteRaw;

    }

    /**
     * Execute the query/update for the statement and return the result (or
     * result set) as as string
     * 
     * @throws SQLException
     * 
     * @return the temp file containing the result set output, null if no result
     *         set is produced
     */

    public File execute(File tempFileForResultSet) throws SQLException,
	    IOException {

	BufferedWriter br = null;

	try {
	    br = new BufferedWriter(new FileWriter(tempFileForResultSet));

	    callStatement(br);
	    IOUtils.closeQuietly(br);
	    return tempFileForResultSet;
	} finally {
	    IOUtils.closeQuietly(br);
	}
    }

    /**
     * Execute the query/update for the statement and return the result (or
     * result set) as as string
     * 
     * @throws SQLException
     * 
     * @return the string containing the result set output, null if no result
     *         set is produced
     */

    public String execute() throws SQLException, IOException {

	StringWriter br = new StringWriter();

	callStatement(br);

	return br.toString();

    }

    /**
     * Execute the passed SQL Statement and return: 

     * - The result set as a List of Maps for SELECT statements. 

     * - The return code for other statements
     * 
     * @param sqlOrder
     *            the qsql order
     * @param sqlParms
     *            the sql parameters
     * @param br
     *            the writer where to write to result set output
     * 
     * 
     * @throws SQLException
     */
    private void callStatement(Writer br) throws SQLException, IOException {

	String sqlOrder = callableStatementHolder.getSqlOrder();

	debug("callableStatementHolder: "
		+ callableStatementHolder.getSqlOrder());
	debug("sqlOrder               : " + sqlOrder);

	CallableStatement callableStatement = null;
	callableStatement = connection.prepareCall(sqlOrder);

	Map parameterTypes = null;
	Map parameterStringValues = null;

	// Class to set all the statement parameters
	ServerCallableStatementParameters serverCallableStatementParameters = null;

	try {

	    ServerSqlUtilCallable.setCallableStatementProperties(
		    callableStatement, callableStatementHolder);

	    parameterTypes = callableStatementHolder.getParameterTypes();
	    parameterStringValues = callableStatementHolder
		    .getParameterStringValues();

	    debug("before serverCallableStatementParameters");

	    serverCallableStatementParameters = new ServerCallableStatementParameters(
		    username, awakeFileConfigurator, callableStatement,
		    callableStatementHolder);
	    serverCallableStatementParameters.setParameters();

	    // Throws a SQL exception if the order is not authorized:
	    debug("before new SqlSecurityChecker()");
	    boolean isAllowedAfterAnalysis = true;

	    /*
	     * boolean isAllowedAfterAnalysis = awakeSqlConfigurator
	     * .allowStatementAfterAnalysis(username, connection, sqlOrder,
	     * serverPreparedStatementParameters .getParameterValues());
	     */
	    if (!isAllowedAfterAnalysis) {

		String ipAddress = request.getRemoteAddr();
		AwakeSqlConfiguratorCall.runIfStatementRefused(
			awakeSqlConfigurator, username, connection, ipAddress,
			sqlOrder,
			serverCallableStatementParameters.getParameterValues());

		String message = Tag.AWAKE_SECURITY + " ["
			+ "{Callable Statement not authorized}"
			+ "{sql order : " + sqlOrder + "}" + "{sql parms : "
			+ parameterTypes + "}" + "{sql values: "
			+ parameterStringValues + "}]";

		throw new SecurityException(message);
	    }

	    isAllowedAfterAnalysis = AwakeSqlConfiguratorCall
		    .allowResultSetGetMetaData(awakeSqlConfigurator, username,
			    connection);

	    debug("before callableStatement.executeQuery() / execute");

	    ServerSqlUtil.setMaxRowsToReturn(callableStatement,
		    awakeSqlConfigurator);

	    ResultSet rs = null;
	    boolean hasResultSet = false;

	    if (isExecuteRaw) {
		hasResultSet = callableStatement.execute();
		if (hasResultSet) {
		    rs = callableStatement.getResultSet();
		}
	    } else {
		rs = callableStatement.executeQuery();
	    }

	    // 1) Update and send back the CallableStatementHolder:
	    updateCallableStatementHolderValues(callableStatement);
	    br.write(TransferStatus.SEND_OK + CR_LF);

	    String jsonString = CallableStatementHolderTransportJson
		    .toJson(callableStatementHolder);
	    boolean doEncryptCallableStatement = AwakeSqlConfiguratorCall
		    .encryptResultSet(awakeSqlConfigurator);

	    if (doEncryptCallableStatement) {
		jsonString = JsonLineEncrypter.encrypt(jsonString,
			awakeCommonsConfigurator);
	    }
	    br.write(jsonString + CR_LF);

	    // 2) Send back the result set:

	    if (hasResultSet || !isExecuteRaw) {
		try {
		    
		    // Horrible hack because ResultSetWriter is in common CE/EE code and
		    // we can't modify it now (to be clean in next CE version):
		    StatementHolder statementHolder = new StatementHolder();
		    statementHolder.setHtmlEncodingOn(callableStatementHolder
			    .isHtmlEncodingOn());
		    
		    ResultSetWriter resultSetWriter = new ResultSetWriter(request,
			    br, awakeCommonsConfigurator,
			    awakeFileConfigurator, awakeSqlConfigurator, username,
			    sqlOrder, statementHolder);

		    resultSetWriter.write(rs);
		} finally {
		    if (rs != null) {
			rs.close();
		    }
		}
	    } else {
		// Write a line saying there is no Result Set!
		br.write(CallableParms.NO_RESULT_SET + CR_LF);
	    }

	} catch (SQLException e) {
	    AwakeServerLogger.log(Level.WARNING, Tag.AWAKE_EXCEPTION_RAISED
		    + CR_LF + "CallableStatement statement: " + sqlOrder
		    + CR_LF + "- sql order : " + sqlOrder + CR_LF
		    + "- sql parms : " + parameterTypes + CR_LF
		    + "- sql values: " + parameterStringValues + CR_LF
		    + "- exception : " + e.toString());
	    throw e;
	} finally {
	    // Close the ServerPreparedStatementParameters
	    if (serverCallableStatementParameters != null) {
		serverCallableStatementParameters.close();
	    }

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

	    // Clean all
	    parameterTypes = null;
	    parameterStringValues = null;
	    serverCallableStatementParameters = null;

	}
    }

    private void updateCallableStatementHolderValues(
	    CallableStatement callableStatement) throws SQLException {
	try {
	    List outParms = callableStatementHolder.getOutP();
	    for (Integer outParmIndex : outParms) {
		Object value = callableStatement.getObject(outParmIndex);
		callableStatementHolder.setOutParameterValue(outParmIndex,
			value);
	    }
	} catch (Exception e) {
	    e.printStackTrace(System.out);
	    throw new SQLException(e);
	}
    }

    /**
     * @param s
     */

    protected void debug(String s) {
	if (DEBUG) {
	    System.out.println(s);
	    AwakeServerLogger.log(s);
	}
    }
}