Passing in Oracle Parameter to SQL string

Question

I'm having a problem where I don't know how I'm supposed to pass in an Oracle parameter where the C# type is a string and the Oracle type is a Varchar2.

Currently I'm passing in this string as CMS','ABC thinking that Oracle will add in the '' that surround this string making it a varchar2 that looks like 'CMS','ABC'.

This works for a single string like CMS but when the value is something longer, like something typically in a IN (list) command the parameter won't be passed in correctly.

This is the code I'm referring too.

string sql = 'SELECT name FROM Pers p WHERE p.FirstName IN (:names)';

The below works when the value of :names being passed in is CML without any quotes.

OracleParameter param = new OracleParameter(":names", OracleDbType.Varchar2, "CML", ParameterDirection.Input);

Below doesn't work when the value of :names being passed in is CML','ABC with quotes on the inside.

OracleParameter param = new OracleParameter(":names", OracleDbType.Varchar2, "CML','ABC", ParameterDirection.Input);

Why is that?

Does Oracle add in single quotes around the parameter when it's passed into the sql statement? Why doesn't it add quotes around the second case?

ODP.NET doesn't support parameters with `IN` clauses. See: https://stackoverflow.com/questions/1625649/oracle-parameters-with-in-statement — Pierre-Loup Pagniez, Jun 01 '18 at 14:42
This works find when `:names` is just `CML` so it's binding fine with an `IN` clause. — Jimenemex, Jun 01 '18 at 14:47
Well, yes, my above comment wasn't quite correct, I should have said: ODP.NET parameters do not work with multiple, comma separated values. — Pierre-Loup Pagniez, Jun 01 '18 at 14:49

Pierre-Loup Pagniez · Answer 1 · 2018-06-01T15:05:49.520

ODP.NET parameters do not work with multiple, comma separated values. Each parameter is treated as a single value, whatever kind of quotes it contains.

Oracle does not add quotes around parameter values when passed to a query. Quotes are just a way to write a VARCHAR value in a query, but when using parameters, Oracle doesn't "replace your parameter with its value then execute the query", as this would allow SQL injection.

If that was the case, imagine your parameter value was: "CML', 'ABC');DROP DATABASE Test;--". Oracle would then execute SELECT name FROM Pers p WHERE p.FirstName IN ('CML', 'ABC');DROP DATABASE Test;--'!

See this question for ideas on how to solve your problem: Oracle Parameters with IN statement?

score 2 · Accepted Answer · answered Jun 01 '18 at 16:00

From your comments/answers I was able to come up with this solution. I hope it helps others who come.

To get around ODT.NET parameters not working with multiple comma separated values you can divide each value into its own parameter. Like the following.

string allParams = "CML, ABC, DEF";
string formattedParams = allParams.Replace(" ", string.Empty); // Or a custom format
string [] splitParams = formattedParams.Split(',');

List<OracleParamter> parameters = new List<OracleParameter>();

string sql = @"SELECT * FROM FooTable WHERE FooValue IN (";
for(int i = 0; i < splitParams.Length; i++)
{
    sql += @":FooParam" + i + ",";
    parameters.Add(new OracleParameter(":FooParam" + i, OracleDbType.Varchar2, splitParams[i], ParameterDirection.Input));
{
sql = sql.Substring(0, (sql.Length - 1));
sql += ')';

The string sql will now have this as it's value: SELECT * FROM FooTable WHERE FooValue IN (:FooParam0,:fooParam1, etc...)

This will solve the problem.

Another approach would be to add in a bunch of OR clauses for each parameter. The above example is better since you don't write a bunch of OR clauses though.

Passing in Oracle Parameter to SQL string

2 Answers2