SqlViewUtils { public static String substituteSqlVariables( String sql, Map<String, String> variables ) { String sqlQuery = sql; if ( variables != null ) { for ( String param : variables.keySet() ) { if ( param != null && SqlView.isValidQueryParam( param ) ) { sqlQuery = substituteSqlVariable( sqlQuery, param, variables.get( param ) ); } } } return sqlQuery; }  static Set<String> getVariables( String sql ); static String substituteSqlVariables( String sql, Map<String, String> variables ); static String substituteSqlVariable( String sql, String name, String value ); static String removeQuerySeparator( String sql ); static final String VARIABLE_EXPRESSION; static final Pattern VARIABLE_PATTERN; }
@Test public void testSubsituteSql() { Map<String, String> variables = new HashMap<>(); variables.put( "level", "4" ); variables.put( "id", "abc" ); String sql = "select * from datavalue where level=${level} and id='${id}'"; String expected = "select * from datavalue where level=4 and id='abc'"; String actual = SqlViewUtils.substituteSqlVariables( sql, variables ); assertEquals( expected, actual ); }
@Test public void testSubsituteSqlMalicious() { Map<String, String> variables = new HashMap<>(); variables.put( "level", "; delete from datavalue;" ); String sql = "select * from datavalue where level=${level}"; String expected = "select * from datavalue where level=${level}"; String actual = SqlViewUtils.substituteSqlVariables( sql, variables ); assertEquals( expected, actual ); }