Java tutorial
/* * This file is part of AceQL. * AceQL: Remote JDBC access over HTTP. * Copyright (C) 2015, KawanSoft SAS * (http://www.kawansoft.com). All rights reserved. * * AceQL is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * AceQL 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 Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA * 02110-1301 USA * * Any modifications to this file must keep this entire header * intact. */ package org.kawanfw.test.api.server.config; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import java.util.Collections; import java.util.Date; import java.util.HashSet; import java.util.List; import java.util.Properties; import java.util.Set; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.io.IOUtils; import org.kawanfw.commons.api.server.DefaultCommonsConfigurator; import org.kawanfw.commons.util.FrameworkDebug; import org.kawanfw.sql.api.server.DefaultSqlConfigurator; import org.kawanfw.sql.api.server.SqlConfigurator; import org.kawanfw.sql.api.server.StatementAnalyser; /** * @author Nicolas de Pomereu * * SqlConfigurator implementation. Its extends the default configuration * and provides a security mechanism for login. */ public class TestSqlConfigurator extends DefaultSqlConfigurator implements SqlConfigurator { /** Debug info */ private static boolean DEBUG = FrameworkDebug.isSet(TestSqlConfigurator.class); /** * Default constructor */ public TestSqlConfigurator() { } /** * @return one element with "ALL" value */ private Set<String> authorizedTables() { Set<String> theAllowedTables = new HashSet<String>(); theAllowedTables.add("CUSTOMER"); theAllowedTables.add("CUSTOMER_AUTO"); theAllowedTables.add("DOCUMENTATION"); theAllowedTables.add("ORDERLOG"); theAllowedTables.add("ORDERLOG_2"); theAllowedTables.add("USER_LOGIN"); theAllowedTables.add("REGIONS"); return Collections.unmodifiableSet(theAllowedTables); } @Override public boolean allowExecute(String username, Connection connection) throws IOException, SQLException { return allowMethod("allowExecute"); } @Override public boolean allowStatementClass(String username, Connection connection) throws IOException, SQLException { return allowMethod("allowStatementClass"); } @Override public boolean allowExecuteUpdate(String username, Connection connection) throws IOException, SQLException { return allowMethod("allowExecuteUpdate"); } /** * @return <code><b>false</b></code>. (Client programs will be not allowed * to use {@Link Connection#getMetaData()}). */ @Override public boolean allowGetMetaData(String username, Connection connection) throws IOException, SQLException { return allowMethod("allowGetMetaData"); } /** * @return <code><b>true</b></code>. (Client programs will be allowed to use * <code>ResulSet.getMetaData()</code>). */ @Override public boolean allowResultSetGetMetaData(String username, Connection connection) throws IOException, SQLException { return allowMethod("allowResultSetGetMetaData"); } /** * Allow the passed SqlConfigurator method depending on it's presence as a * true property in a file * * @param theMethodToAllow * the SqlConfigurator method to allow * @return true if method is allowed * @throws IOException */ private boolean allowMethod(String theMethodToAllow) throws IOException { File propertyFile = new File(System.getProperty("user.home") + File.separator + "kawanfw-test" + File.separator + "aceql-sql-configurator.txt"); if (!propertyFile.exists()) { return true; } FileInputStream in = null; try { in = new FileInputStream(propertyFile); Properties prop = new Properties(); prop.load(in); String property = prop.getProperty(theMethodToAllow); if (property == null) { return true; } property = property.trim(); return new Boolean(property); } finally { IOUtils.closeQuietly(in); } } /** * @return <code><b>true</b></code> if all following requirements are met: * <ul> * <li>Statement does not contain SQL comments.</li> * <li>Statement does not contain ";" character.</li> * <li>Statement is a DML statement: DELETE / INSERT / SELECT / * UPDATE.</li> * <li>Table must be: CUSTOMER / ORDERLOG / USER_LOGIN</li> * </ul> */ @Override public boolean allowStatementAfterAnalysis(String username, Connection connection, String sql, List<Object> parameterValues) throws IOException, SQLException { boolean parentAnalysis = super.allowStatementAfterAnalysis(username, connection, sql, parameterValues); debug("Begin allowStatementAfterAnalysis"); debug("sql : " + sql); debug("parameterValues: " + parameterValues); if (!parentAnalysis) { debug("disallow parentAnalysis"); return false; } // We will start statement analysis on the sql string. StatementAnalyser statementAnalyser = new StatementAnalyser(sql, parameterValues); // Update/Delete must be PreparedStatement with at least one parameter if (statementAnalyser.isUpdate() || statementAnalyser.isDelete()) { if (!statementAnalyser.isPreparedStatement()) { debug("disallow Update/Delete must be PreparedStatement with at least one parameter"); return false; } } // Allow all Selects statement if (statementAnalyser.isSelect()) { return true; } String table = statementAnalyser.getTableNameFromDmlStatement(); if (table == null) { debug("disallow table null"); return false; } table = table.toUpperCase(); if (!authorizedTables().contains(table)) { debug("disallow !authorizedTables().contains(table) :" + table + ":"); return false; } // ok, accept statement return true; } /** * The event will be logged as <code>Level.WARNING</code> in the * <code>user.home/.kawansoft/log/kawanfw.log</code> file */ @Override public void runIfStatementRefused(String username, Connection connection, String ipAddress, String sql, List<Object> parameterValues) throws IOException, SQLException { Logger logger = new DefaultCommonsConfigurator().getLogger(); logger.log(Level.WARNING, "In TestSqlConfigurator: Client " + username + "(IP: " + ipAddress + ") has been denied executing sql statement: " + sql + " with parameters: " + parameterValues); } // @Override // public boolean encryptResultSet() throws IOException ,SQLException // { // return true; // } // // /** // * @return 50 // */ // @Override // public int getMaxRowsToReturn() throws IOException, SQLException { // return maxRowToReturn; // } @SuppressWarnings("unused") private int maxRowToReturn = 0; /** * @param maxRowToReturn * the maxRowToReturn to set */ public void setMaxRowToReturn(int maxRowToReturn) { this.maxRowToReturn = maxRowToReturn; } /** * @param s * the content to log/debug */ private void debug(String s) { if (DEBUG) System.out.println(this.getClass().getName() + " " + new Date() + " " + s); } }