dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #08606
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2097: Improved sql view functionality - Unallowance to query in the sepecial table such as "users" and ...
------------------------------------------------------------
revno: 2097
committer: Hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2010-11-18 13:46:03 +0700
message:
Improved sql view functionality - Unallowance to query in the sepecial table such as "users" and "userinfo"
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/ResourceTableNameMap.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java
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/resources/org/hisp/dhis/dataadmin/i18n_module.properties
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified 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 2010-08-24 07:43:37 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/ResourceTableNameMap.java 2010-11-18 06:46:03 +0000
@@ -36,10 +36,13 @@
public class ResourceTableNameMap
{
private static Map<String, String> resourceNameMap;
+
+ private static Map<String, String> ignoredNameMap;
static
{
resourceNameMap = new HashMap<String, String>();
+ ignoredNameMap = new HashMap<String, String>();
resourceNameMap.put( "_cocn", "_categoryoptioncomboname" );
resourceNameMap.put( "_cs", "_categorystructure" );
@@ -49,10 +52,23 @@
resourceNameMap.put( "_ougss", "_orgunitgroupsetstructure" );
resourceNameMap.put( "_oustgss", "_organisationunitgroupsetstructure" );
+ ignoredNameMap.put( "_users", "users" );
+ ignoredNameMap.put( "_uinfo", "userinfo" );
}
- public static String getNameByAlias( String alias )
+ public static String getResourceNameByAlias( String alias )
{
return resourceNameMap.get( alias );
}
+
+ public static String getIgnoredNameByAlias( String alias )
+ {
+ return ignoredNameMap.get( alias );
+ }
+
+ public static Map<String, String> getIgnoredNameMap()
+ {
+ return ignoredNameMap;
+ }
+
}
=== 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 2010-10-29 12:19:15 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2010-11-18 06:46:03 +0000
@@ -132,7 +132,7 @@
try
{
ResultSet rs = holder.getStatement().executeQuery(
- PREFIX_SELECT_QUERY + ResourceTableNameMap.getNameByAlias( resourceTableName ) + " LIMIT 1" );
+ PREFIX_SELECT_QUERY + ResourceTableNameMap.getResourceNameByAlias( resourceTableName ) + " LIMIT 1" );
ResultSetMetaData rsmd = rs.getMetaData();
int countCols = rsmd.getColumnCount();
=== 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 2010-08-19 09:25:55 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java 2010-11-18 06:46:03 +0000
@@ -26,6 +26,11 @@
* (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 static org.hisp.dhis.sqlview.ResourceTableNameMap.getIgnoredNameMap;
+
+import java.util.Map;
+
import org.hisp.dhis.i18n.I18n;
import org.hisp.dhis.sqlview.SqlViewService;
@@ -40,10 +45,18 @@
{
private static final String ADD = "add";
- private static final String REGEX_SELECT_QUERY = "^(?i)\\s*(select\\s{1,}).+$";
+ private static final String SEMICOLON = ";";
+
+ private static final String SEPERATE = "|";
+
+ private static final String REGEX_SELECT_QUERY = "^(?i)\\s*select\\s{1,}.+$";
private static final String REGEX_SELECT_INTO_QUERY = " into ";
+ private static final String PREFIX_REGEX_IGNORE_TABLES_QUERY = "^(?i).+((?<=[^\\d\\w])(";
+
+ private static final String SUFFIX_REGEX_IGNORE_TABLES_QUERY = ")(?=[^\\d\\w])).*$";
+
// -------------------------------------------------------------------------
// Dependencies
// -------------------------------------------------------------------------
@@ -131,16 +144,27 @@
return INPUT;
}
+ final String ignoredRegex = this.setUpIgnoredRegex();
+
sqlquery = sqlViewService.makeUpForQueryStatement( sqlquery );
- for ( String s : sqlquery.split( ";" ) )
+ for ( String s : sqlquery.split( SEMICOLON ) )
{
- if ( !s.matches( REGEX_SELECT_QUERY ) || s.toLowerCase().contains( REGEX_SELECT_INTO_QUERY ) )
+ String tmp = new String( s.toLowerCase() );
+
+ if ( !s.matches( REGEX_SELECT_QUERY ) || tmp.contains( REGEX_SELECT_INTO_QUERY ) )
{
message = i18n.getString( "sqlquery_is_invalid" ) + "<br/>" + i18n.getString( "sqlquery_is_welformed" );
return INPUT;
}
+
+ if ( tmp.matches( ignoredRegex ) )
+ {
+ message = i18n.getString( "sqlquery_is_not_allowed" );
+
+ return INPUT;
+ }
}
message = sqlViewService.testSqlGrammar( sqlquery );
@@ -152,4 +176,30 @@
return SUCCESS;
}
+
+ // -------------------------------------------------------------------------
+ // Supportive methods
+ // -------------------------------------------------------------------------
+
+ private String setUpIgnoredRegex()
+ {
+ int i = 0;
+ int len = getIgnoredNameMap().size();
+
+ StringBuffer ignoredRegex = new StringBuffer( PREFIX_REGEX_IGNORE_TABLES_QUERY );
+
+ for ( Map.Entry<String, String> entry : getIgnoredNameMap().entrySet() )
+ {
+ ignoredRegex.append( entry.getValue() );
+
+ if ( ++i < len )
+ {
+ ignoredRegex.append( SEPERATE );
+ }
+ }
+
+ ignoredRegex.append( SUFFIX_REGEX_IGNORE_TABLES_QUERY );
+
+ return ignoredRegex.toString();
+ }
}
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties 2010-11-08 09:21:24 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties 2010-11-18 06:46:03 +0000
@@ -317,4 +317,5 @@
orgunit_group_list = Organisation Unit Group list
patient_data_archive = Beneficiary Data Archive
intro__patient_data_archive = Archive beneficiary data which is not currently relevant to your system in order to improve performance. Data can also be unarchived.
-pruning_interrupted = You must choose the organisation unit has parent to prune. Please try again!
\ No newline at end of file
+pruning_interrupted = You must choose the organisation unit has parent to prune. Please try again!
+sqlquery_is_not_allowed = Not allowance to query in the special tables !
\ No newline at end of file
