Click here to Skip to main content
14,866,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using the below query with In clause
strign courceids = comma seperated ids ex "123,456,567,678";
select * from student where studentID= stdId and courseid in ("+ courceids +")
Its working but i want to prevent the sql injection for this query
so could you please assist on this how to use for IN clause in oracle

What I have tried:

I have tried
OracleCommand oraCommand = new OracleCommand("select * from student where studentID= stdId and courseid in = :courceids ", con);
oraCommand.Parameters.Add(new OracleParameter("courceids ", courceids ));
Posted
Updated 14-Feb-21 4:36am

   
Comments
DGKumar 17-Sep-20 4:34am
   
Hi Gerry Schmtiz,
we are using direct select commands with in clause (Oracle db ) but we dont have access for db modifications, just we are fetching data by using the select command
For that we need to pass in clause to db with cmd.parameters.
Gerry Schmitz 17-Sep-20 11:15am
   
If you had checked the code, you would see it creates parms for each value for the in clause. In other words, brute force instead of some magic expression.

#2 says the same thing.
You need to pass multiple parameters, one for each item in your list.
C#
using (var oraCommand = new OracleCommand(null, con))
{
    oraCommand.Parameters.AddWithValue("@studentID", studentID);
    
    var parameterNames = new List<string>(courseIDs.Count);
    for (int index = 0; index < courseIDs.Count; index++)
    {
        string name = "@courseId" + index;
        oraCommand.Parameters.AddWithValue(name, courseIDs[index]);
        parameterNames.Add(name);
    }
    
    oraCommand.CommandText = string.Format("SELECT ... FROM student WHERE studentID = @studentID And courseId In ({0})", string.Join(", ", parameterNames));
    
    ...
}

It might be easier to use Dapper:
C#
IEnumerable<Student> students = connection.Query<Student>("SELECT ... FROM student WHERE studentID = @studentID And courseId In (@courseIDs)", new { studentID = 42, courseIDs = new[] { 1, 2, 3 } });
GitHub - StackExchange/Dapper: Dapper - a simple object mapper for .Net[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900