All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

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

    Go to download

    Awake SQL is an open source framework that allows remote and secure JDBC access through HTTP.

    There is a newer version: 2.0.2
    Show newest version
    /*
 * 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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Vector;
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.AwakeFileUtil;
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.servlet.AwakeSqlConfiguratorCall;

/**
 * @author KawanSoft S.A.S
 * 
 *         Allows to execute the Statement or Prepared Statement on the Server
 *         as raw execute()
 */
public class ServerStatementRawExecute {
    private static boolean DEBUG = AwakeDebug
	    .isSet(ServerStatementRawExecute.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 StatementHolder statementHolder;

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

    /**
     * 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 sqlOrderAndParmsStore
     *            the Sql order and parms
     */

    public ServerStatementRawExecute(HttpServletRequest request,
	    AwakeCommonsConfigurator awakeCommonsConfigurator,
	    AwakeFileConfigurator awakeFileConfigurator,
	    AwakeSqlConfigurator awakeSqlConfigurator, Connection connection,
	    String username, StatementHolder statementHolder)
	    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.statementHolder = statementHolder;
	this.request = request;
    }

    /**
     * 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 {
	// File tempFileForResultSet = createTempFileForResultSet();
	BufferedWriter br = null;

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

	    // Execute it
	    if (statementHolder.isPreparedStatement()) {
		executePrepStatement(br);
	    } else {
		executeStatement(br);
	    }

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

    }

    /**
     * Execute the passed SQL PreparedStatement as execute() sand 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 executePrepStatement(BufferedWriter br) throws SQLException,
	    IOException {
	String sqlOrder = statementHolder.getSqlOrder();

	PreparedStatement preparedStatement = null;

	boolean usesAutoGeneratedKeys = false;

	if (statementHolder.getAutoGeneratedKeys() != -1) {
	    preparedStatement = connection.prepareStatement(sqlOrder,
		    statementHolder.getAutoGeneratedKeys());
	    usesAutoGeneratedKeys = true;
	} else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) {
	    preparedStatement = connection.prepareStatement(sqlOrder,
		    statementHolder.getColumnIndexesAutogenerateKeys());
	    usesAutoGeneratedKeys = true;
	} else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) {
	    preparedStatement = connection.prepareStatement(sqlOrder,
		    statementHolder.getColumnNamesAutogenerateKeys());
	    usesAutoGeneratedKeys = true;
	} else {
	    preparedStatement = connection.prepareStatement(sqlOrder);
	}

	Map parameterTypes = null;
	Map parameterStringValues = null;

	// Class to set all the statement parameters
	ServerPreparedStatementParameters serverPreparedStatementParameters = null;

	try {

	    ServerSqlUtil.setStatementProperties(preparedStatement,
		    statementHolder);

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

	    if (!AwakeSqlConfiguratorCall.allowExecute(awakeSqlConfigurator,
		    username, connection)) {
		String ipAddress = request.getRemoteAddr();

		AwakeSqlConfiguratorCall.runIfStatementRefused(
			awakeSqlConfigurator, username, connection, ipAddress,
			sqlOrder, new Vector());

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

		throw new SecurityException(message);
	    }

	    debug("before ServerPreparedStatementParameters");

	    serverPreparedStatementParameters = new ServerPreparedStatementParameters(
		    request, username, awakeFileConfigurator,
		    preparedStatement, statementHolder);
	    serverPreparedStatementParameters.setParameters();

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

	    boolean isAllowed = awakeSqlConfigurator
		    .allowStatementAfterAnalysis(username, connection,
			    sqlOrder, serverPreparedStatementParameters
				    .getParameterValues());
	    ;

	    if (!isAllowed) {

		String ipAddress = request.getRemoteAddr();

		AwakeSqlConfiguratorCall.runIfStatementRefused(
			awakeSqlConfigurator, username, connection, ipAddress,
			sqlOrder,
			serverPreparedStatementParameters.getParameterValues());

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

		throw new SecurityException(message);
	    }

	    debug("before preparedStatement.execute()");

	    ServerSqlUtil.setMaxRowsToReturn(preparedStatement,
		    awakeSqlConfigurator);

	    boolean isResultSet = preparedStatement.execute();

	    if (isResultSet) {
		ResultSet rs = preparedStatement.getResultSet();

		try {

		    br.write(TransferStatus.SEND_OK + CR_LF);

		    ResultSetWriter resultSetWriter = new ResultSetWriter(request,
			    br, awakeCommonsConfigurator,
			    awakeFileConfigurator, awakeSqlConfigurator, username,
			    sqlOrder, statementHolder);

		    resultSetWriter.write(rs);
		} finally {
		    if (rs != null)
			rs.close();
		}
	    } else {
		int rc = preparedStatement.getUpdateCount();
		br.write(TransferStatus.SEND_OK + CR_LF);
		br.write("getUpdateCount=" + rc + CR_LF);

		// Write the preparedStatement.getGeneratedKeys() on the stream
		// if necessary
		if (usesAutoGeneratedKeys) {
		    ResultSet rs = preparedStatement.getGeneratedKeys();

		    try {
			ResultSetWriter resultSetWriter = new ResultSetWriter(
				request, br,
				awakeCommonsConfigurator, awakeFileConfigurator,
				awakeSqlConfigurator, username, sqlOrder, statementHolder);
			resultSetWriter.write(rs);
		    } finally {
			if (rs != null)
			    rs.close();
		    }
		}

	    }

	} catch (SQLException e) {
	    AwakeServerLogger.log(Level.WARNING,
		    Tag.AWAKE_PRODUCT_FAIL + CR_LF + "Prepared 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 (serverPreparedStatementParameters != null) {
		serverPreparedStatementParameters.close();
	    }

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

	}
    }

    /**
     * Execute the passed SQL Statement as execute(sql) 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 executeStatement(BufferedWriter br) throws SQLException,
	    IOException {

	String sqlOrder = statementHolder.getSqlOrder();

	// sqlOrder = HtmlConverter.fromHtml(sqlOrder);

	if (statementHolder.isDoExtractResultSetMetaData()) {
	    sqlOrder = ServerSqlUtil.addLimit1(sqlOrder, connection);
	}

	Statement statement = null;

	try {

	    if (!AwakeSqlConfiguratorCall.allowExecute(awakeSqlConfigurator,
		    username, connection)) {
		String ipAddress = request.getRemoteAddr();

		AwakeSqlConfiguratorCall.runIfStatementRefused(
			awakeSqlConfigurator, username, connection, ipAddress,
			sqlOrder, new Vector());

		String message = Tag.AWAKE_SECURITY + " ["
			+ "{Statement not authorized for execute}"
			+ "{sql order : " + sqlOrder + "}" + "]";

		throw new SecurityException(message);
	    }

	    statement = connection.createStatement();
	    ServerSqlUtil.setStatementProperties(statement, statementHolder);

	    debug("before ServerPreparedStatementParameters");

	    boolean isAllowed = awakeSqlConfigurator
		    .allowStatementAfterAnalysis(username, connection,
			    sqlOrder, new Vector());
	    ;

	    if (!isAllowed) {

		String ipAddress = request.getRemoteAddr();

		AwakeSqlConfiguratorCall.runIfStatementRefused(
			awakeSqlConfigurator, username, connection, ipAddress,
			sqlOrder, new Vector());

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

		throw new SecurityException(message);
	    }

	    debug("before statement.execute(sqlOrder)");

	    ServerSqlUtil.setMaxRowsToReturn(statement, awakeSqlConfigurator);

	    boolean isResultSet = false;

	    boolean usesAutoGeneratedKeys = false;

	    if (statementHolder.getAutoGeneratedKeys() != -1) {
		isResultSet = statement.execute(sqlOrder,
			statementHolder.getAutoGeneratedKeys());
		usesAutoGeneratedKeys = true;
	    } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) {
		isResultSet = statement.execute(sqlOrder,
			statementHolder.getColumnIndexesAutogenerateKeys());
		usesAutoGeneratedKeys = true;
	    } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) {
		isResultSet = statement.execute(sqlOrder,
			statementHolder.getColumnNamesAutogenerateKeys());
		usesAutoGeneratedKeys = true;
	    } else {
		isResultSet = statement.execute(sqlOrder);
	    }

	    if (isResultSet) {
		ResultSet rs = statement.getResultSet();

		try {

		    br.write(TransferStatus.SEND_OK + CR_LF);
		    ResultSetWriter resultSetWriter = new ResultSetWriter(request,
			    br, awakeCommonsConfigurator,
			    awakeFileConfigurator, awakeSqlConfigurator, username,
			    sqlOrder, statementHolder);

		    resultSetWriter.write(rs);
		} finally {
		    if (rs != null)
			rs.close();
		}
	    } else {
		int rc = statement.getUpdateCount();
		br.write(TransferStatus.SEND_OK + CR_LF);
		br.write("getUpdateCount=" + rc + CR_LF);

		if (usesAutoGeneratedKeys) {
		    ResultSet rs = statement.getGeneratedKeys();

		    try {
			ResultSetWriter resultSetWriter = new ResultSetWriter(
				request, br,
				awakeCommonsConfigurator, awakeFileConfigurator,
				awakeSqlConfigurator, username, sqlOrder, statementHolder);
			resultSetWriter.write(rs);
		    } finally {
			if (rs != null)
			    rs.close();
		    }
		}
	    }

	} catch (SQLException e) {
	    AwakeServerLogger.log(Level.WARNING, Tag.AWAKE_PRODUCT_FAIL + CR_LF
		    + "Statement: " + sqlOrder + CR_LF + "- sql order: "
		    + sqlOrder + CR_LF + "- exception: " + e.toString());
	    throw e;
	} finally {
	    IOUtils.closeQuietly(br);

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

    /**
     * Create our own Awake temp file
     * 
     * @return the tempfile to create with ".awake" prefix in the java.io.tmpdir
     *         directory
     */
    public static synchronized File createTempFileForResultSet() {
	String unique = AwakeFileUtil.getUniqueId();
	String tempDir = AwakeFileUtil.getAwakeTempDir();
	String tempFile = tempDir + File.separator + "result-set" + unique
		+ ".tmp";

	return new File(tempFile);
    }

    /**
     * @param s
     */

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

}
    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy