0

I am using Esper 5.0 and need to perform a query on data from a relational database. This is in my case a MySQL database connected via JDBC. Now I would like to formulate an EPL query on the data from the database. Hence, my query is similar to this one:

String parameter = "any"; // untrusted (!) parameter from some user input
String mySqlQuery = "SELECT `sth` FROM `mytable` WHERE `att` = " + parameter;
String query = "select sth from sql:myDB ['" + mySqlQuery + "']"

Now how can I sanitize the untrusted parameter which is then send to my MySQL database? The Esper documentation says that the query is basically passed to the database software unchanged. So what can I do? I came up with four ideas:

  1. Using EPL substitution parameters: Does not work, because it is not supported by Esper in SQL expressions.
  2. Using EPL variables: It should be possible to define a variable via something like epService.getEPAdministrator().getConfiguration().addVariable("parameter", String.class, parameter);, then use the syntax "SELECT `sth` FROM `mytable` WHERE `att` = ${parameter}" for the MySQL query. Esper should replace the variable with the respective value. It is not nice to define a global variable for it, although one could again remove it with epService.getEPAdministrator().getConfiguration().removeVariable("parameter", true); afterwards. But much more important: That also does not sanitize the untrusted parameter and does not make it safe to pass it to the database, right?
  3. Sanitizing the parameters on the Java side: Queries on the Java side should be done via PreparedStatement. Since it is technically impossible to get a MySQL query string out of a PreparedStatement, this is not an option. I do not think that there is another safe way to sanitize a parameter passed to a database on the Java side.
  4. Defining all the constraints on the EPL side: One could probably just do a SELECT * FROM `mytable` without defining any constraints in order to select everything from the MySQL database and then define the constraints via EPL and use a EPPreparedStatement for it. Is this the proper way to go? I fear that this is not really performant, because a lot of entries are read from the MySQL database which are not needed.

Any ideas?

Community
  • 1
  • 1
Simon
  • 4,103
  • 7
  • 28
  • 53

1 Answers1

0

Would it work with the statement object model API? That API gives you complete control over all parts of EPL.

user650839
  • 2,594
  • 1
  • 13
  • 9
  • Well... I do not know :-) I am pretty much new to Esper. If you added an example showing how to use it with a MySQL statement and how to sanitize the parameters with it, I would be very thankful. – Simon Aug 13 '14 at 14:31
  • BTW why don't you create a Stackoverflow account? :) Thanks for all the help here with [#esper](https://stackoverflow.com/questions/tagged/esper) – Simon Aug 13 '14 at 14:32