2008-09-17

Parameterised queries in ECO

Whenever I generate OCL queries in code I find myself having to escape user input in order to avoid making the query invalid, or allowing malicious input.

I've decided instead to use the ECO equivalent of parameterised queries (variables in ECO) and here is the result.

public static string CreateParameterisedQuery(
  this IEcoServiceProvider serviceProvider,
  string query,
  out IModifiableVariableList vars,
  params object[] args)
{
  vars = serviceProvider.GetEcoService<IVariableFactoryService>().CreateVariableList();
  for (int varIndex = 0; varIndex < args.Length; varIndex++)
  {
    string variableName = "autoVar_" + varIndex.ToString();
    query = query.Replace("{" + varIndex.ToString() + "}", variableName);
    vars.AddConstant(variableName, args[varIndex]);
  }
  return query;
}



To use this code you would do something like this

//1: Create the OCL with string.format style parameters
string query = "Person.allInstances" +
  "->select(name.sqlLikeCaseInsensitive({0}))" +
  "->select(gender = {1}";

//2: Parse the query and build the variable list
IModifyableVariableList vars;
query = self.AsIObject().ServiceProvider.CreateParameterisedQuery(query,
  out vars,
  "Peter Morris",
  Gender.Male);


Now you can use IOclPsService or IOclService to execute the new query passing the variables.

1 comment:

Joe Hendricks said...

Very very helpful Pete! Especially for ECO Asp.Net apps like mine..
JoeH