parameterized insertion of data half selected from other column half constant gives invalid pseudocolum

Question

I have the following query

     "INSERT INTO t1 select $v1,c2 FROM t2 WHERE c3= $v2";

which is executed as

    SqlCommand cmd= new SqlCommand(query, conn);
    cmd.Parameters.AddWithValue("$v2", data);
    foreach (string value in list)
    {
        cmd.Parameters.AddWithValue("$v1", value);
        cmd.ExecuteNonQuery();
    }

however this results in an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Invalid pseudocolumn "$v1".

This is based on the following question: SQL Insert into ... values ( SELECT ... FROM ... ) I suspect that it's not understanding where the were clause goes or that $v1 is not the name of a column but an actual value, but does anybody know how to fix this (t1 only has 2 columns both ints c2 is an int and c3 is also an int).

The final goal of this code is to do basically the following (in pseudocode)

$insertv1=$v1
$insertv2= select c2 from t2 where c3=$v2 
query = insert INTO t1 VALUES ($insertv1,$insertv2)

Note that deparameterizing the values $v1 and $v2 solves the problem but just parameterizing one of the two causes a problem.

You _can't_ parameterize your column names. You can _only_ parameterize your values. — Soner Gönül, Aug 14 '15 at 07:19
What I'm trying to do is insert a value that is parameterized by making it part of the select statement kind lika how you can do SELECT columnname, "someconstant" FROM table. See for an example the answer from @travis in the linked question. — Thijser, Aug 14 '15 at 07:20

score 1 · Accepted Answer · edited May 23 '17 at 11:58

As the past says, you cannot pass the column name to an OleDB SQL query as a parameter. Parameters only for values, not table or column names.

As failed passed attempts mention in their comments, this is not secure - since this could be dangerous. The main problem is if the table name comes from user input - if it is so, it will leave an SQL injection vulnerability in your application. Thus, if it does indeed come from user input you'll need to protect yourself. The easiest way is to make sure that table name is a valid table, Attached table, or query name - To do that, you can use a query like so:

SELECT Name FROM Myssobjects Where Type in (1,5,6,)

This query will return you the valid values for tables in your database, which you can compare to the input you got. Notice you should still use this with caution, because most of the times you won't want to let your user have access to your entire database.

Anyhow, if you protected yourself well and made sure that you are no longer vulnerable, the way to do this is dynamically create the string for the query - like so:

string tableName = value;
string query =  "INSERT INTO t1 select "+tableName+",c2 FROM t2 WHERE c3= $v2";
SqlCommand cmd= new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("$v2", data);
// etc. - instead of the AddWithValue(), you add it to the string.

EDIT: Since this isn't exacly what you asked for, I'll explain what I mean in relation to your pseudo code:

$insertv1=$v1
$insertv2= select c2 from t2 where c3=$v2 
query = insert INTO t1 VALUES ($insertv1,$insertv2)

Would actually require seperation of the two queries, and thus translate into:

string v1="Your value here.",v2="Your second value here.",v3="";
//first : get v2.
SqlCommand cmd= new SqlCommand("select c2 from t2 where c3=$v2", conn); // using the v2 query you stated.
cmd.Parameters.AddWithValue("$v2", v2);
cmd.ExecuteNonQuery();
// put the value from the query into v3
// then, make the entire, bigger query
string finalQuery =  "INSERT INTO t1 VALUES($v1,$v2)";
SqlCommand cmd2= new SqlCommand(finalQuery, conn);
cmd2.Parameters.AddWithValue("$v1", v1);
cmd2.Parameters.AddWithValue("$v2", v3);
cmd2.ExecuteNonQuery();

Note that deparameterizing the values $v1 and $v2 solves the problem but just parameterizing one of the two causes a problem.

EDIT 2:

In chat, we looked further into the compliation error, since there was some strange behavior with parameterizing different values. I asked you what values are in $v1 and $v2, and we figured that the nvarchar in v2 is misinterpreted as a pseudo name. That also explains why the SQL works but the C# failed; the error was in fact in the way OleDB intrepreted the name. The solution is simple - add ' marks before and after v2, which will lead to it being read as a non-pseudo string, like so: "INSERT INTO t1 select $v1,c2 FROM t2 WHERE c3= '$v2'".

The problem in this case is that tableName (v1) is not in fact a column or table name but a variable I want to have in the output of my select statement. — Thijser, Aug 14 '15 at 07:34
@Thijser I think it could be done, but before we step into that - I'm not sure it'd actually be better, since these two queries are pretty different. run both these queries on your database, and measure the time diffrence. If there is one, I'll help you convert it into a single query. — A. Abramov, Aug 14 '15 at 07:45
@Thijser By the way - the main problem with your code is the fact you try to override the previous addition with another one, which is not how `OleDBQuery` works. If you'd loop around the entire process of making the query (since the first attachment) and override the decleration of `cmd` each time, it should work fine. If you'd like me to edit my answer and add an example, just ask for it :) — A. Abramov, Aug 14 '15 at 07:48
You mean that this code will not result in insertion of several new values? I based this construction on the answer here: https://stackoverflow.com/questions/12426300/how-to-insert-list-from-c-sharp-into-sql-server-2008?lq=1 would this answer then be wrong? What would you say is the valid construction for adding multiple values this way? — Thijser, Aug 14 '15 at 07:52
@Thijser I was about to write that the fact you use `$v2` as `@column` metters since it's another parameter based on a query, but that'd be in fact, wrong. That's pretty strange then - no idea why your code isn't working. I've tested my solution on Northwind, access 2010 database and everything seems to be fine. — A. Abramov, Aug 14 '15 at 07:57
So it works if executed directly on a database but not if it's executed trough c# code? Or does it just not work against an sql-server? It appears to work if I use a non parametized value for $v2 (1 for example) but then we would be working with non parametized values. — Thijser, Aug 14 '15 at 07:58
@Thijser The SQL portion of what I've written works on northwind, so does yours. The C# part of my (seperated) solution works on northwind, and you claim yours doesn't work on your database. So unless there's something wrong with your connection, there's probably an error in the c# part, but I can't put my finger on it... — A. Abramov, Aug 14 '15 at 08:01
Note that parameterizing value $v2 also causes the same problem which is weird. — Thijser, Aug 14 '15 at 08:07
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86963/discussion-between-a-abramov-and-thijser). — A. Abramov, Aug 14 '15 at 08:07

parameterized insertion of data half selected from other column half constant gives invalid pseudocolum

1 Answers1