parse oracle query for parameters from c#

Question

I would like to do the same thing as oracle sqldeveloper do with parametered queries (but from c#, not java). Let's say there's an arbitrary, user supplied query, eg

select * from dual where 1 = :parameter

My task is to parse safely similar strings, identify the parameters, ask them from user and execute the query. Which is the right / safe approach? I guess, there's some oracle client api to do this. Or is the right way using some pl/sql stuff (eg. from DBMS_SQL)? I couldn't find such a thing yet...

Update / clarification: see the example code below:

// user enters the query string with parameters somehow:
string sql = AskUserForSelectString();
// now the value of sql is:
// "select column0 from tablename where column1 = :param1 and column2 = :param2 ;"

// this is my original question: HOW TO DO THIS?
List<string> param_names = OracleParseQueryAndGiveMyParameters(sql);
// param_names is now a list of ":param1",":param2"

// ask user again for parameter values:
var param_values = new List<string>();
foreach (var param_name in param_names) 
{
    string param_value = AskUserForParameterValue(param_name);
    param_values.Add(param_value);
}

// give the parameter values for the query in safe way:
using (var cmd = new SqlCommand(sql, myDbConnection))
{
    for (int i=0; i< param_names.Count; i++)
        cmd.Parameters.AddWithValue(param_names[i], param_values[i]);
    var result = cmd.ExecuteReader();
    // process result...
}

The key point is that I don't know the parameters in advance. This is exactly what SqlDeveloper can do. (That isn't an issue if EF expects @ before the parameter name instead of colon, that can be worked out easily.)

There should not be any real differences from java or c#. You need a driver to communicate with the database. The driver does all the work. And you query need to be compatible with the driver. So the driver is the back end and the language like java or c# is the front end. The vendors API make it easy to interface with the driver. So either adding the driver to the connection string or calling the API (which calls the driver) are equivalent. — jdweng
– jdweng, Commented Feb 1, 2020 at 14:54
Ok, thank you for ensuring me that it is a driver's task (I wasn't sure if it can be done with some oracle-specific server side package calling direct sql). Maybe the only missing point is that I couldn't find the appropriate api function. Could you show me a starting point? I'm using Oracle.EntityFrameworkCore — Feri
– Feri, Commented Feb 1, 2020 at 17:10
You are using Entity which creates a set of c# classes that represent the database. Then there is a mapping file that takes the database tables and maps then to the classes. The queries are then linq statements in c#. — jdweng
– jdweng, Commented Feb 1, 2020 at 17:42
I was wrong, not Entity. I would recommend using the Adapter( learn.microsoft.com/en-us/dotnet/api/…) instead of ExecuteReader(). The adapter would fill a datatable. Then you can use dt.AsEnumerable() to query the datatable with linq statements. — jdweng
– jdweng, Commented Feb 2, 2020 at 20:29

Shiraz Bhaiji · Accepted Answer · 2020-02-01 18:57:50Z

2

You can do it like this:

var sql = "INSERT INTO myTable (myField1, myField2) " +
          "VALUES (@someValue, @someOtherValue);";

using (var cmd = new SqlCommand(sql, myDbConnection))
{
    cmd.Parameters.AddWithValue("@someValue", someVariable);
    cmd.Parameters.AddWithValue("@someOtherValue", someTextBox.Text);
    cmd.ExecuteNonQuery();
}

What you absolutly must NOT do is:

var sql = "INSERT INTO myTable (myField1, myField2) " +
          "VALUES ('" + someVariable + "', '" + someTextBox.Text + "');";

var cmd = new SqlCommand(sql, myDbConnection);
cmd.ExecuteNonQuery();

The problem with the second example is that it opens your code to an SQL Injection attack.

answered Feb 1, 2020 at 18:57

Shiraz Bhaiji

65.7k37 gold badges149 silver badges260 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Feri Over a year ago

Please see "Update / clarification" in original post.

Shiraz Bhaiji Over a year ago

You can just execute the SQL string provided by the user, as in the last 2 lines of my second example. But then you need to make sure that there is nothing in the string that could damage your system.

Christopher Jones · Accepted Answer · 2020-02-01 22:19:21Z

1

One (hacky but accurate?!) way with the original ":parameter" bind variable syntax is to call out to C and use Oracle OCI functions to do the parsing for you. Prepare the statement with OCIStmtPrepare2() and then call OCIStmtGetBindInfo() to get the variable names.

answered Feb 1, 2020 at 22:19

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

1 Comment

Feri Over a year ago

"call out to C and use Oracle OCI functions to do the parsing for you" - Ugh, I hope there's a builtin stuff for doing this in one of the managed oracle APIs...

Collectives™ on Stack Overflow

parse oracle query for parameters from c#

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related