Tip An interesting hack for calling a function..

[cjard]

I have a generic CSV loader here at work. It receives a CSV and according to some parameters (connectionstring, function name, columns-to-parameters mapping) the loader reads the CSV and calls the nominated function, passing parameters as found in the CSV

Suppose we have a CSV:
a,1,banana,#

And we have a function:
function(in_letter,in_fruit,in_symbol)

The map looks like:
in_letter,,in_fruit,in_symbol

meaning columns 1,3 and 4 from the csv are passed to those named parameters, to achieve:
function('a','banana','#')

This worked fine, but I came upon a problem. I needed to use values from two columns in the csv, as the value for one argument in the function:
function('a1','banana','#')


After making a test app and watching the OracleCommand behaviour I found a curious hack so that I could have my stuff work.

When in code we set this:
oc.CommandText = "function"

the oc.StatementText (a private property that holds the true SQL that is run) becomes:
"function"

When we then do this:
oc.CommandType = CommandType.StoredProcedure

The oc.StatementText is changed to become:
"begin function; end;"

We then add our parameters:
oc.Parameters.AddWithValue("returnValue", " ").Direction = ParameterDirection.ReturnValue;
oc.Parameters.AddWithValue("p1", "1");
oc.Parameters.AddWithValue("p2", "23");

Our oc.StatementText ends up as:
"begin :returnValue := function(p1=>1,p2=>2); end;"


This seems to be built by basic string concatenation because I was able to make my commandtext to be this:
functionp1||2,34); END;--
Note the -- which is like VB's ' mark meaning comment until end of the line

After adding my parameters the oc.StatementText was:
"begin :returnValue := functionp1||2,34); END;--(p1=>1, p2=>2); end;"
i.e. the rest of the string that .net builds is commented out

Which meant I could specify a map of:
p1,p2,p3,p4

for my file, and the function was called with p1 and p2 combined:
function('a1','banana','#')

-

The fact that we're discussing CSV file mappings is largely irrelevant here; it's jsut a demo of how I was able to overcome a limitation of an existing system. This is mainly a discussion about how oracle builds a command string based on what you put in.. and how it uses the names you give to parameters in the calling of the function. It also carries a caveat for those of you who may expose your functions to the users. If you allow them to enter their own parameter names you can still let them carry out SQL injection attackes

Have fun!