An issue of SqlCommand with parameters for IN

Question

Apparently, the following code does not print anything out as it is expected.. I am sure it is to do with the fact that I tried to put a list of items in to @namelist. Clearly, it is not just a text replacement.

How can I solve this problem? Thanks

using (var connection = new SqlConnection(_connectionString))
{
    connection.Open();

    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@namelist)";
        cmd.Parameters.AddWithValue("@namelist",  "'tableOne', 'tableTwo'");

        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            var a = reader[0];
            Console.WriteLine(a);
        }
    }
}

Instead of "TradeName" in your addWithValue have you tried @namelist — Bearcat9425, May 30 '13 at 17:21
My personal solution [is this one](http://stackoverflow.com/a/337864/119477) The answer Includes an extension method to convert `IEnumerable` to `IEnumerable` — Conrad Frix, May 30 '13 at 17:32
heard from someone that there could be a security issue if this is allowed. it is sql server that does not allows this rather than on the dotnet layer. Anyone has comments on this? — hong pei, May 30 '13 at 18:42

Mike Christensen · Accepted Answer · 2013-05-30T17:33:27.337

Unfortunately, SQL parameters aren't resolved that way, in other words, the backend doesn't just build a safe-string replacing each parameter with its value. Instead, you'll have to dynamically build a parameter list:

cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@p1, @p2, @p3)"; // This can be built dynamically

And then add each parameter:

cmd.Parameters.AddWithValue("@p1", "tableOne");
cmd.Parameters.AddWithValue("@p2", "tableTwo");
cmd.Parameters.AddWithValue("@p3", "tableThree");

You could of course add these parameters in a loop if the number was unknown until runtime:

for(var i = 0; i < myParams.length; i++)
{
   cmd.Parameters.AddWithValue("@p" + i.ToString(), myParams[i]);
}

If your list of tables were stored in an enum, or you could escape them or validate them with a regular expression, it would also be fairly safe to just build the raw SQL yourself and not use parameters at all.

This is, of course, one of the big reasons I use PostgreSQL; native support for arrays.

Oracle has Associative Arrays. SQL Server has Table Value params. — Conrad Frix, May 30 '13 at 17:36
I checked into table value params once and it was too confusing for me. ADO.NET wouldn't just marshal over the data as an array, and in the sproc you had to JOIN in this temp table or some nonsense. Postgres just lets you pass in a `string[]` as a param and do `WHERE x IN (:myArray)` - The way you'd expect it to work. — Mike Christensen, May 30 '13 at 17:43

score 4 · Answer 2 · edited Jan 22 '16 at 08:10

As has been noted, "in" lists etc are notoriously awkward in ado.net; because of this, some tools offer convenience methods to help. For example, Dapper offers a variant on the "in" syntax which it automatically expands to the correct parameterized form (still retaining injection safety etc) - in both type-bound and "dynamic" usage. For example:

string[] namelist = ...
foreach(var row in conn.Query(@"
    select column_name, table_name
    from information_schema.columns
    where table_name in @namelist",
        new { namelist } ))
{
    string col = row.column_name,
         table = row.table_name;
    // .. 
}

This also avoids the need to mess around with db-command/parameter/reader. Note the "in" without brackets which it uses to recognise this pattern.

Frick man, what CAN'T dapper do! – pim Sep 06 '17 at 21:25 — pim, Sep 06 '17 at 21:25

score -2 · Answer 3 · answered May 30 '13 at 17:22

You are not using your list right now. If you don't need it for something else you could simply do this :

 using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in ('tradeName',  'tableOne', 'tableTwo')"

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
              var a = reader[0];
              Console.WriteLine(a);
            }
        }

This will check if it's in those 3 values.

What's with the -1 and no comment ? – phadaphunk May 30 '13 at 17:27 — phadaphunk, May 30 '13 at 17:27

An issue of SqlCommand with parameters for IN

3 Answers3

Linked

Related