-2

I want to basically use multiple iD's in In clause of my sql query. Now i have two options one is to get the comma separated ID's from a textbox or i can put a list view or grid view to insert id's there and then get the id's to be used in sql statement. Can you please help me with the code, how to do this thing?

Bilal
  • 21
  • 1
  • 9
  • loop over `id`'s generating parameters with name and value, `string.Join` parameter names to create in clause for statement. But really, what are you asking exactly? – Kris Oct 02 '17 at 06:30
  • see, let's suppose i have a textbox which will get as an input ID's seperated by ''," . now what i want is if i write select * from table where id in (ID); i get the result of all the id's passed in the textbox with , seperated – Bilal Oct 02 '17 at 06:36
  • I guess this is what you are looking for :- "select * from table where id in ( "+textbox1.text+")"; – Deepak Oct 02 '17 at 06:42
  • @Deepak thanks alot man it is working – Bilal Oct 02 '17 at 06:47
  • Does that solve your problem? – Deepak Oct 02 '17 at 06:48
  • @Deepak what if `textbox1` contains the following text: `1);DROP TABLE table;--`? Please read about SQL Injections. – Zohar Peled Oct 02 '17 at 06:48
  • @ZoharPeled I guess he asked for select query only. Isn't that so? – Deepak Oct 02 '17 at 06:50
  • @Deepak yes man that solved my problem thanks alot, but one more question.what if my textbox contains id as 010-170928-0168. whenn i use the same query you old me it gives error – Bilal Oct 02 '17 at 06:53
  • Well, given the input I've posted in the last comment, the select statement might not return anything but the table called `table` **will** be dropped. That's what SQL Injection is - exploiting string concatenation to inject harmful sql statements to your database. – Zohar Peled Oct 02 '17 at 06:53
  • @ZoharPeled I got your point. – Deepak Oct 02 '17 at 06:55
  • @Bilal is that a single Id ? – Deepak Oct 02 '17 at 06:55
  • @Deepak yes, and i want to add this type multiple id's – Bilal Oct 02 '17 at 06:58
  • @Bilal what is the error you are getting? Also try to change the code to "select * from table where id in ( ' " + textbox1.Text + " ')"; – Deepak Oct 02 '17 at 07:03
  • @Bilal please also bear in mind the Sql Injection problem you can have as stated by Zohar. – Deepak Oct 02 '17 at 07:04
  • @Deepak it returns nothing – Bilal Oct 02 '17 at 07:09
  • @Deepak then what is the right way to use it to avoid sql injection – Bilal Oct 02 '17 at 07:09
  • @Deepak sorry i forgot to add for this id (010-170929-01672) i am using oracle DB, so what should i modify in the above code. My query looks like select * from pmp_bpm_troubleticket where ticketno in ('010-170922-01158','010-170922-01154') – Bilal Oct 02 '17 at 07:12
  • @Bilal to avoid Sql Injection you can apply condition prior to your query. For your id problem I have to take a look and get back to you. – Deepak Oct 02 '17 at 07:13
  • @Deepak what type of conditions can you please explain, and sure i will be waiting for your response. Thanks alot man – Bilal Oct 02 '17 at 07:14
  • @Bilal msdn has explained Sql Injection in a great way [link](https://msdn.microsoft.com/en-us/library/ff648339.aspx) – Deepak Oct 02 '17 at 07:23
  • Your case does not suit using the `IN` clause & avoiding SQL Injection, as you have a variable number of arguments. The `IN` clause is just a shorthand for multiple `OR` clauses. I would suggest you use a `for` loop to generater multiple parameterized `OR` statements. The standard method of avoiding SQL Injection is the use of parameterized queries. – Ashley Pillay Oct 02 '17 at 07:31

2 Answers2

3

The correct way to send user input to the database is using parameters. The IN operator often confuses inexperienced developers since a lot of them try to use it with a single parameter that contains comma delimited values and expect it to return results. However that is a mistake since the IN operator expect a list of values, not a single value containing a list.

So, to parameterize a query for the IN operator what you need to do is break down the comma separated string in your code and provide the query with a parameter for each value.

Here is a basic example:

var userInput = "1,2,3,4,5,6";
var values = userInput.Split(',');

using(var command = new OdbcCommand())
{
    var sql = "SELECT * FROM table where id IN(";

    for(int i=0; i < values.Length; i++) {
        sql = $"{sql} @{i},";
        command.Parameters.Add($"@{i}", OdbcType.Int).Value = values[i];
    }

    command.CommandText = sql.TrimEnd(',') +");";
    command.Connection = con;
    using(var reader = Command.ExecuteReader())
    {
        while(reader.Read())
        {
            // do your stuff with the data
        }
    }
}
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Wouldn't this result in a query like `SELECT * FROM table where id IN(@1, SELECT * FROM table where id IN(@1, @2, SELECT * FROM table where id IN(@1, @2, @3`... ? – Ashley Pillay Oct 02 '17 at 07:36
  • @AshleyPillay No, it will result with a query like `SELECT * FROM table where id IN( @0, @1, @2, @3, @4, @5);`. [See a live demo on rextester](http://rextester.com/MLPG40865) – Zohar Peled Oct 02 '17 at 07:42
-6

In order to get textbox value you have to code like this:-

"select * from table where id in ( "+textbox1.text+")";

But this will lead you to Sql Injection problem. So a better approach will be:-

var command = new SqlCommand("SELECT * FROM table WHERE id = @value")
{
  Connection = connection();
};

command.Parameters.AddWithValue("value", textbox1.text);
var dataReader = command.ExecuteReader();
Deepak
  • 376
  • 6
  • 23