=== removed file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/ResourceTableNameMap.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/ResourceTableNameMap.java 2015-01-17 07:41:26 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/ResourceTableNameMap.java 1970-01-01 00:00:00 +0000 @@ -1,64 +0,0 @@ -package org.hisp.dhis.sqlview; - -/* - * Copyright (c) 2004-2015, University of Oslo - * All rights reserved. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * Redistributions of source code must retain the above copyright notice, this - * list of conditions and the following disclaimer. - * - * Redistributions in binary form must reproduce the above copyright notice, - * this list of conditions and the following disclaimer in the documentation - * and/or other materials provided with the distribution. - * Neither the name of the HISP project nor the names of its contributors may - * be used to endorse or promote products derived from this software without - * specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND - * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR - * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON - * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -import java.util.HashMap; -import java.util.Map; - -/** - * @author Dang Duy Hieu - * @version $Id ResourceTableNameMap.java Aug 10, 2010$ - */ -public class ResourceTableNameMap -{ - private static Map ignoredNameMap; - - static - { - ignoredNameMap = new HashMap<>(); - - ignoredNameMap.put( "_users", "users" ); - ignoredNameMap.put( "_uinfo", "userinfo" ); - ignoredNameMap.put( "_trackedentityinstance", "trackedentityinstance" ); - ignoredNameMap.put( "_trackedentityattr", "trackedentityattribute" ); - ignoredNameMap.put( "_relationship", "relationship.*" ); - ignoredNameMap.put( "_caseaggrcondition", "caseaggregationcondition" ); - } - - public static String getIgnoredNameByAlias( String alias ) - { - return ignoredNameMap.get( alias ); - } - - public static Map getIgnoredNameMap() - { - return ignoredNameMap; - } - -} === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2015-02-11 22:50:44 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2015-02-12 09:12:38 +0000 @@ -28,10 +28,11 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ -import com.fasterxml.jackson.annotation.JsonProperty; -import com.fasterxml.jackson.annotation.JsonView; -import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty; -import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement; +import java.util.HashMap; +import java.util.Map; +import java.util.Set; +import java.util.regex.Pattern; + import org.apache.commons.lang.StringUtils; import org.hisp.dhis.common.BaseIdentifiableObject; import org.hisp.dhis.common.DxfNamespaces; @@ -41,10 +42,11 @@ import org.hisp.dhis.common.view.ExportView; import org.hisp.dhis.schema.annotation.PropertyRange; -import java.util.HashMap; -import java.util.Map; -import java.util.Set; -import java.util.regex.Pattern; +import com.fasterxml.jackson.annotation.JsonProperty; +import com.fasterxml.jackson.annotation.JsonView; +import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty; +import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement; +import com.google.common.collect.Sets; /** * @author Dang Duy Hieu @@ -57,6 +59,9 @@ private static final String CRITERIA_SEP = ":"; + public static final Set PROTECTED_TABLES = Sets.newHashSet( "users", "userinfo", + "trackedentityinstance", "trackedentityattribute", "trackedentityattributevalue", "relationship" ); + // ------------------------------------------------------------------------- // Variables // ------------------------------------------------------------------------- === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java 2015-02-11 22:32:01 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java 2015-02-12 09:12:38 +0000 @@ -34,7 +34,6 @@ /** * @author Dang Duy Hieu - * @version $Id SqlViewExpandStore.java July 06, 2010$ */ public interface SqlViewExpandStore { @@ -50,5 +49,11 @@ void executeQuery( Grid grid, String sql ); + /** + * Tests the given SQL for validity. + * + * @param sql the SQL string. + * @return a non-null description if invalid, and null if valid. + */ String testSqlGrammar( String sql ); } === removed file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewJoinLib.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewJoinLib.java 2015-01-17 07:41:26 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewJoinLib.java 1970-01-01 00:00:00 +0000 @@ -1,158 +0,0 @@ -package org.hisp.dhis.sqlview; - -/* - * Copyright (c) 2004-2015, University of Oslo - * All rights reserved. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * Redistributions of source code must retain the above copyright notice, this - * list of conditions and the following disclaimer. - * - * Redistributions in binary form must reproduce the above copyright notice, - * this list of conditions and the following disclaimer in the documentation - * and/or other materials provided with the distribution. - * Neither the name of the HISP project nor the names of its contributors may - * be used to endorse or promote products derived from this software without - * specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND - * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR - * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON - * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -/** - * @author Dang Duy Hieu - * @version $Id SqlCodeMapUtil.java Aug 16, 2010$ - */ -public class SqlViewJoinLib -{ - public static final String COCN_JOIN_CS = "JOIN _categorystructure AS _cs ON _cocn.categoryoptioncomboid = _cs.categoryoptioncomboid \n"; - - public static final String COCN_JOIN_DV = "JOIN datavalue AS dv ON _cocn.categoryoptioncomboid = dv.categoryoptioncomboid \n"; - - public static final String CS_JOIN_COCN = "JOIN _categoryoptioncomboname AS _cocn ON _cs.categoryoptioncomboid = _cocn.categoryoptioncomboid \n"; - - public static final String DEGSS_JOIN_DV = "JOIN datavalue AS dv ON _degss.dataelementid = dv.dataelementid \n"; - - public static final String DV_JOIN_COCN = "JOIN _categoryoptioncomboname AS _cocn ON dv.categoryoptioncomboid = _cocn.categoryoptioncomboid \n"; - - public static final String DV_JOIN_DEGSS = "JOIN _dataelementgroupsetstructure AS _degss ON dv.dataelementid = _degss.dataelementid \n"; - - public static final String DV_JOIN_OUS = "JOIN _orgunitstructure AS _ous ON dv.sourceid = _ous.organisationunitid \n"; - - public static final String DV_JOIN_OUSTGSS = "JOIN _organisationunitgroupsetstructure AS _oustgss ON dv.sourceid = _oustgss.organisationunitid \n"; - - public static final String OUS_JOIN_DV = "JOIN datavalue AS dv ON _ous.organisationunitid = dv.sourceid \n"; - - public static final String OUGSS_JOIN_DV = "JOIN datavalue AS dv ON _ougss.organisationunitid = dv.sourceid \n"; - - public static final String OUSTGSS_JOIN_DV = "JOIN datavalue AS dv ON _oustgss.organisationunitid = dv.sourceid \n"; - - public static final String OUS_JOIN_OUGSS = "JOIN _orgunitgroupsetstructure AS _ougss ON _ous.organisationunitid = _ougss.organisationunitid \n"; - - public static final String OUS_JOIN_OUSTGSS = "JOIN _organisationunitgroupsetstructure AS _oustgss ON _ous.organisationunitid = _oustgss.organisationunitid \n"; - - public static final String OUGSS_JOIN_OUS = "JOIN _orgunitstructure AS _ous ON _ougss.organisationunitid = _ous.organisationunitid \n"; - - public static final String OUGSS_JOIN_OUSTGSS = "JOIN _organisationunitgroupsetstructure AS _oustgss ON _ougss.organisationunitid = _oustgss.organisationunitid \n"; - - public static final String OUSTGSS_JOIN_OUS = "JOIN _orgunitstructure AS _ous ON _oustgss.organisationunitid = _ous.organisationunitid \n"; - - public static final String OUSTGSS_JOIN_OUGSS = "JOIN _orgunitgroupsetstructure AS _ougss ON _oustgss.organisationunitid = _ougss.organisationunitid \n"; - - /** - * COCN_JOIN_DEGSS presents to the relationship between two resourcetables - * _CategoryOptionComboname and _DataElementGroupSetStructure through the - * INNER JOIN between DataValue and _DataElementGroupSetStructure - */ - public static final String COCN_JOIN_DEGSS = COCN_JOIN_DV + DV_JOIN_DEGSS; - - /** - * DEGSS_JOIN_COCN presents to the relationship between two resourcetables - * _dataelementgroupsetstructure and _CategoryOptionComboname through the - * INNER JOIN between DataValue and _CategoryOptionComboname - */ - public static final String DEGSS_JOIN_COCN = DEGSS_JOIN_DV + DV_JOIN_COCN; - - /** - * COCN_JOIN_OUS presents to the relationship between two resourcetables - * _CategoryOptionComboname and _OrgUnitStructure through the INNER JOIN - * between DataValue and _OrgUnitStructure - */ - public static final String COCN_JOIN_OUS = COCN_JOIN_DV + DV_JOIN_OUS; - - /** - * COCN_JOIN_OUSTGSS presents to the relationship between two resourcetables - * _CategoryOptionComboname and _OrganisationUnitGroupSetStructure through - * the INNER JOIN between DataValue and _OrganisationUnitGroupSetStructure - */ - public static final String COCN_JOIN_OUSTGSS = COCN_JOIN_DV + DV_JOIN_OUSTGSS; - - /** - * OUSTGSS_JOIN_COCN presents to the relationship between two resourcetables - * _OrganisationUnitGroupSetStructure and _CategoryOptionComboname through - * the INNER JOIN between DataValue and _CategoryOptionComboname - */ - public static final String OUSTGSS_JOIN_COCN = OUSTGSS_JOIN_DV + DV_JOIN_COCN; - - /** - * OUGSS_JOIN_COCN presents to the relationship between two resourcetables - * _OrgUnitGroupSetStructure and _CategoryOptionComboname through the INNER - * JOIN between DataValue and _CategoryOptionComboname - */ - public static final String OUGSS_JOIN_COCN = OUGSS_JOIN_DV + DV_JOIN_COCN; - - /** - * OUS_JOIN_COCN presents to the relationship between two resourcetables - * _OrgUnitStructure and _CategoryOptionComboname through the INNER JOIN - * between DataValue and _CategoryOptionComboname - */ - public static final String OUS_JOIN_COCN = OUS_JOIN_DV + DV_JOIN_COCN; - - /** - * OUS_JOIN_DEGSS presents to the relationship between two resourcetables - * _OrgUnitStructure and _DataElementGroupSetStructure through the INNER - * JOIN between DataValue and _DataElementGroupSetStructure - */ - public static final String OUS_JOIN_DEGSS = OUS_JOIN_DV + DV_JOIN_DEGSS; - - /** - * OUGSS_JOIN_DEGSS presents to the relationship between two - * resourcetables_OrgUnitGroupSetStructure and _DataElementGroupSetStructure - * through the INNER JOIN between DataValue and - * _DataElementGroupSetStructure - */ - public static final String OUGSS_JOIN_DEGSS = OUGSS_JOIN_DV + DV_JOIN_DEGSS; - - /** - * OUGSS_JOIN_DEGSS presents to the relationship between two - * _OrganisationUnitGroupSetStructure and _DataElementGroupSetStructure - * through the INNER JOIN between DataValue and - * _DataElementGroupSetStructure - */ - public static final String OUSTGSS_JOIN_DEGSS = OUSTGSS_JOIN_DV + DV_JOIN_DEGSS; - - /** - * DEGSS_JOIN_OUS presents to the relationship between two resourcetables - * _DataElementGroupSetStructure and _OrgUnitStructure through the INNER - * JOIN between DataValue and _OrgUnitStructure - */ - public static final String DEGSS_JOIN_OUS = DEGSS_JOIN_DV + DV_JOIN_OUS; - - /** - * DEGSS_JOIN_OUSTGSS presents to the relationship between two - * resourcetables _DataElementGroupSetStructure and - * _OrganisationUnitGroupSetStructure through the INNER JOIN between - * DataValue and _OrganisationUnitGroupSetStructure - */ - public static final String DEGSS_JOIN_OUSTGSS = DEGSS_JOIN_DV + DV_JOIN_OUSTGSS; - -} === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2015-02-11 22:32:01 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2015-02-12 09:12:38 +0000 @@ -162,7 +162,7 @@ return ex.getCause().getMessage(); } - return ""; + return null; } @Override === modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java 2015-02-11 22:50:44 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java 2015-02-12 09:12:38 +0000 @@ -28,13 +28,12 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ -import com.opensymphony.xwork2.Action; import org.hisp.dhis.i18n.I18n; import org.hisp.dhis.sqlview.SqlViewService; -import java.util.Map; +import com.opensymphony.xwork2.Action; -import static org.hisp.dhis.sqlview.ResourceTableNameMap.getIgnoredNameMap; +import static org.hisp.dhis.sqlview.SqlView.PROTECTED_TABLES; /** * @author Dang Duy Hieu @@ -43,19 +42,12 @@ implements Action { private static final String ADD = "add"; - private static final String SEMICOLON = ";"; - - private static final String SEPERATE = "|"; - + private static final String SEP = "|"; private static final String SPACE = " "; - private static final String INTO = " into "; - private static final String REGEX_SELECT_QUERY = "^(?i)\\s*select\\s{1,}.+$"; - private static final String PREFIX_REGEX_IGNORE_TABLES_QUERY = "^(?i).+((?<=[^\\d\\w])("; - private static final String SUFFIX_REGEX_IGNORE_TABLES_QUERY = ")(?=[^\\d\\w])).*$"; // ------------------------------------------------------------------------- @@ -123,8 +115,8 @@ @Override public String execute() { - message = ""; - + message = null; + if ( name == null || name.trim().isEmpty() ) { message = i18n.getString( "name_is_null" ); @@ -146,7 +138,7 @@ return INPUT; } - final String ignoredRegex = this.setUpIgnoredRegex(); + final String validationRegex = getValidationRegex(); for ( String s : sqlquery.split( SEMICOLON ) ) { @@ -159,7 +151,7 @@ return INPUT; } - if ( tmp.concat( SPACE ).matches( ignoredRegex ) ) + if ( tmp.concat( SPACE ).matches( validationRegex ) ) { message = i18n.getString( "sqlquery_is_not_allowed" ); @@ -169,7 +161,7 @@ message = sqlViewService.testSqlGrammar( sqlquery ); - if ( !message.equals( "" ) ) + if ( message != null ) { return INPUT; } @@ -181,20 +173,20 @@ // Supportive methods // ------------------------------------------------------------------------- - private String setUpIgnoredRegex() + private String getValidationRegex() { int i = 0; - int len = getIgnoredNameMap().size(); + int len = PROTECTED_TABLES.size(); StringBuffer ignoredRegex = new StringBuffer( PREFIX_REGEX_IGNORE_TABLES_QUERY ); - for ( Map.Entry entry : getIgnoredNameMap().entrySet() ) + for ( String table : PROTECTED_TABLES ) { - ignoredRegex.append( entry.getValue() ); + ignoredRegex.append( table ); if ( ++i < len ) { - ignoredRegex.append( SEPERATE ); + ignoredRegex.append( SEP ); } }