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

    • src-main.org.awakefw.sql.api.server.StatementAnalyser Maven / Gradle / Ivy

    Go to download

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

    The 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.api.server;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.awakefw.commons.server.util.AwakeServerLogger;
import org.awakefw.file.api.util.AwakeDebug;
import org.awakefw.file.util.Tag;

/**
 * 
 * Class that allows the analysis of the string content of a SQL statement,
 * mainly for security reasons. 
    
 * 
    
 * Analysis methods include:
 * 
      
 * 
    • Says if a statement contains SQL comments.
      • 
 * 
    • Extract the statement type:
 * DELETE/INSERT/SELECT/UPDATE, CREATE/ALTER/DROP...
      • 
 * 
    • Says if the statement is a DML statement (exclusively:
 * DELETE/INSERT/SELECT/UPDATE).
      • 
 * 
    • Says if the statement is a PreparedStatement with at least one '?'
 * parameter.
      • 
 * 
    • Counts the number of parameters.
      • 
 * 
    • Methods to get the first, the last or any parameter.
      • 
 * 
    • Says if the statement is a DDL statement (exclusively:
 * CREATE/ALTER/DROP/TRUNCATE/COMMENT/RENAME).
      • 
 * 
    • Says if the statement is a DCL statement (exclusively:
 * GRANT/REVOKE).
      • 
 * 
    • Extract the table name in use for a DML statement;
      • 
 * 
    
 * 
 * @author Nicolas de Pomereu
 * @since 1.0
 */

public class StatementAnalyser {
    /** Set to true to display/log debug info */
    private static boolean DEBUG = AwakeDebug.isSet(StatementAnalyser.class);

    // DML
    private final static String DELETE = "DELETE";
    private final static String INSERT = "INSERT";
    private final static String SELECT = "SELECT";
    private final static String UPDATE = "UPDATE";

    // DDL
    private final static String CREATE = "CREATE";
    private final static String ALTER = "ALTER";
    private final static String DROP = "DROP";
    private final static String TRUNCATE = "TRUNCATE";
    private final static String COMMENT = "COMMENT";
    private final static String RENAME = "RENAME";

    // DCL
    private final static String GRANT = "GRANT";
    private final static String REVOKE = "REVOKE";

    // TCL
    // public final static String COMMIT = "COMMIT ";
    // public final static String ROLLBACK = "ROLLBACK";
    // public final static String SET_TRANSACTION = "SAVEPOINT";
    // public final static String savepoint = "savepoint";

    private static final String BLANK = " ";

    /** The statement type */
    private final String statementType;

    /** The Sql statement in string format */
    private final String sql;

    /** The parameter values */
    private List parameterValues = null;

    /**
     * Constructor.
     * 
     * @param sql
     *            the string content of the SQL statement.
     * @param parameterValues
     *            the parameter values of a prepared statement in the natural
     *            order, empty list for a (non prepared) statement
     */
    public StatementAnalyser(String sql, List parameterValues) {

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

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

	sql = sql.trim();

	this.sql = sql;
	this.statementType = StringUtils.substringBefore(this.sql, BLANK);

	this.parameterValues = parameterValues;
    }

    /**
     * Says if a statement contains Semicolons (';') that are not trailing. Use
     * this to prevent attacks when a statement is multi-statements.
     * 
     * @return true if the SQL statement contains SQL comments
     */
    public boolean isWithSemicolons() {
	String localSql = sql;

	// Remove the trailing ";": they are harmless (not source of attack);
	while (localSql.endsWith(";")) {
	    localSql = StringUtils.removeEnd(localSql, ";");
	}

	return localSql.contains(";");
    }

    /**
     * Says if a statement contains SQL comments.
     * 
     * @return true if the SQL statement contains SQL comments
     */
    public boolean isWithComments() {
	return ((sql.contains("/*") && sql.contains("*/") || (sql.contains("({") && sql.contains("})")) || sql.contains(" --")));
    }

    /**
     * Extract the statement type from a sql order.
     * 
     * @return the statement type: DELETE, INSERT, SELECT, UPDATE,
     *         etc...
     */
    public String getStatementType() {
	return statementType;
    }

    /**
     * Says a statement is a statement of certain type.
     * 
     * @param statementTypeToMatch
     *            the statement type to match: DELETE / ...
     * @return true if the statement type is matched.
     */
    private boolean isStatementType(String statementTypeToMatch) {
	if (statementTypeToMatch == null) {
	    throw new IllegalArgumentException(
		    "statementTypeToMatch can not be null!");
	}

	if (statementType == null) {
	    return false;
	}

	if (statementType.equalsIgnoreCase(statementTypeToMatch)) {
	    return true;
	} else {
	    return false;
	}
    }

    /**
     * Says if the statement is a DELETE.
     * 
     * @return true if the statement is a DELETE
     */
    public boolean isDelete() {
	return isStatementType(DELETE);
    }

    /**
     * Says if the statement is an INSERT.
     * 
     * @return true if the statement is an INSERT
     */
    public boolean isInsert() {
	return isStatementType(INSERT);
    }

    /**
     * Says if the statement is a SELECT.
     * 
     * @return true if the statement is a SELECT
     */
    public boolean isSelect() {
	return isStatementType(SELECT);
    }

    /**
     * Says if the statement is an UPDATE.
     * 
     * @return true if the statement is an UPDATE
     */
    public boolean isUpdate() {
	return isStatementType(UPDATE);
    }

    /**
     * Returns true if the statement is a prepared statement with at least one
     * '?' parameter.
     * 
     * @return true if the statement is a prepared statement with at least one
     *         '?' parameter, else false
     */
    public boolean isPreparedStatement() {
	return parameterValues.isEmpty() ? false : true;
    }

    /**
     * Returns the number of parameters in the statement
     * 
     * @return the number of parameters in the statement
     */
    public int getParameterCount() {
	return parameterValues.size();
    }

    /**
     * Returns the value in string of the last parameter of the parameters list.
     * 
     * @return the value in string of the last parameter of the parameters list
     * @throws IndexOutOfBoundsException
     *             if there is no parameter
     */
    public Object getLastParameter() {

	int size = parameterValues.size();
	if (size == 0) {
	    throw new IndexOutOfBoundsException("There is no parameter.");
	}
	return parameterValues.get(size - 1);
    }

    /**
     * Returns the value in string of the first parameter of the parameters
     * list.
     * 
     * @return the value in string of the first parameter of the parameters
     *         list.
     * @throws IndexOutOfBoundsException
     *             if there is no parameter
     */
    public Object getFirstParameter() {
	int size = parameterValues.size();
	if (size == 0) {
	    throw new IndexOutOfBoundsException("There is no parameter.");
	}
	return parameterValues.get(0);
    }

    /**
     * Returns the value as object of the parameter index in the list.
     * 
     * @param index
     *            index of parameter as in a list: starts at 0.
     * @return the value as object of the parameter index.
     * @throws IndexOutOfBoundsException
     *             if the index is out of range (
     *             index < 0 || index >= size())
     */
    public Object getParameter(int index) {
	int size = parameterValues.size();
	if (size == 0) {
	    throw new IndexOutOfBoundsException("There is no parameter.");
	}
	try {
	    return parameterValues.get(index);
	} catch (IndexOutOfBoundsException e) {
	    throw new IndexOutOfBoundsException(
		    "Parameter index is out of bounds: " + index
			    + ". Number of parameters: " + size);
	}
    }

    /**
     * Says if the statement is a DML (Data Manipulation Language) statement (
     * DELETE/INSERT/SELECT/UPDATE).
     * 
     * @return true if the statement is DML statement
     */
    public boolean isDml() {
	return (isDelete() || isInsert() || isSelect() || isUpdate());
    }

    /**
     * Says if the statement is a DCL (Data Control Language) statement (
     * GRANT/REVOKE).
     * 
     * @return true if the statement is DCL statement
     */
    public boolean isDcl() {
	if (statementType == null) {
	    return false;
	}

	if (statementType.equalsIgnoreCase(GRANT)
		|| statementType.equalsIgnoreCase(REVOKE)) {
	    return true;
	} else {
	    return false;
	}
    }

    /**
     * Says if the statement is a DDL (Data Definition Language) statement (
     * CREATE/ALTER/DROP/TRUNCATE/COMMENT/RENAME)
     * 
     * @return true if the statement is DDL statement
     */
    public boolean isDdl() {
	if (statementType == null) {
	    return false;
	}

	if (statementType.equalsIgnoreCase(CREATE)
		|| statementType.equalsIgnoreCase(ALTER)
		|| statementType.equalsIgnoreCase(DROP)
		|| statementType.equalsIgnoreCase(TRUNCATE)
		|| statementType.equalsIgnoreCase(COMMENT)
		|| statementType.equalsIgnoreCase(RENAME)

	) {
	    return true;
	} else {
	    return false;
	}
    }

    /**
     * Returns the table name in use type from a DML SQL order.
     * 
     * @return the table name in use (the first one in a SELECT
     *         statement) for a DML statement. Returns null if statement is not
     *         DML.
     */
    public String getTableNameFromDmlStatement()
	    throws IllegalArgumentException {
	// Extract the first order
	String statementTypeUpper = statementType.toUpperCase();

	String sqlUpper = sql.toUpperCase();

	// Extract the table depending on the ordOer
	sqlUpper = StringUtils.substringAfter(sqlUpper, statementTypeUpper);
	sqlUpper = sqlUpper.trim();

	String table = null;

	if (statementTypeUpper.equals(INSERT)) {
	    sqlUpper = StringUtils.substringAfter(sqlUpper, "INTO ");
	    sqlUpper = sqlUpper.trim();
	    table = StringUtils.substringBefore(sqlUpper, " ");
	} else if (statementTypeUpper.equals(SELECT)
		|| statementTypeUpper.equals(DELETE)) {
	    sqlUpper = StringUtils.substringAfter(sqlUpper, "FROM ");
	    sqlUpper = sqlUpper.trim();
	    // Remove commas in the statement and replace with blanks in case we
	    // have
	    // a join: "TABLE," ==> "TABLE "
	    sqlUpper = sqlUpper.replaceAll(",", " ");
	    table = StringUtils.substringBefore(sqlUpper, BLANK);
	} else if (statementTypeUpper.equals(UPDATE)) {
	    debug("sqlLocal :" + sqlUpper + ":");
	    table = StringUtils.substringBefore(sqlUpper, BLANK);
	} else {
	    return null; // No table
	}

	if (table != null) {
	    table = table.trim();
	}

	return table;
    }

    /**
     * Returns the string content of the SQL statement.
     * 
     * @return the string content of the SQL statement
     */
    public String getSql() {
	return this.sql;
    }

    // * 
  • Says if the statement contain basic aggregate functions:
    // * MAX(), MIN(), COUNT() or AVG().
    • 

    // /**
    // * Says if the statement has at least a basic aggregate function:  
    // * MAX(), MIN(), COUNT(), AVG().
    // *
    // * @return true if the statement has at least a basic aggregate function
    // */
    // public boolean isWithBasicAggregate() {
    // String sqlOrderUpper = sql.toUpperCase();
    //
    // // Aggregate format is either " MAX(" or " MAX "
    // String arrayAgg[] = { " MAX(", " MIN(", " COUNT(", " AVG(" };
    //
    // for (int i = 0; i < arrayAgg.length; i++) {
    // String element = arrayAgg[i];
    //
    // if (sqlOrderUpper.contains(element)) {
    // return true;
    // }
    //
    // // Presentation may be different: "MAX (" instead of "MAX("
    // element = element.replace("(", " ");
    //
    // if (sqlOrderUpper.contains(element)) {
    // return true;
    // }
    //
    // }
    //
    // return false;
    // }

    /**
     * Debug tool
     * 
     * @param s
     */

    // @SuppressWarnings("unused")
    private void debug(String s) {
	if (DEBUG) {
	    AwakeServerLogger.log(s);
	}
    }

}
    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy