Sql insert command in C#

Question

I'm currently creating a small C# program that inserts data from files into a postgres table.

The code that does the insertion looks like this:

 NpgsqlCommand cmd = new NpgsqlCommand(@"INSERT INTO :table(text) VALUES (:word);", con);
 cmd.Parameters.AddWithValue("table", table);
 cmd.Parameters.AddWithValue("word", line);
 cmd.ExecuteNonQuery();

But every time it tries to execute the "ExecuteNonquery" line I get the following error:

An unhandled exception of type 'Npgsql.NpgsqlException' occurred in Npgsql.dll
Additional information: ERROR: 42601: syntax error at or near "("

I can connect to the database I have checked. The variables table and line also have the correct values at runtime. I just can't figure out what the problem is..

Any suggestions ?

I don't know C# specifically, but generally in most languages you cannot parameterize the table name, only the values in the where clause. — Mike Ryan
– Mike Ryan, Commented May 26, 2012 at 15:48

Leon Cullens · Accepted Answer · 2012-05-26 15:48:19Z

4

As far as I know the table can't be a parameter.

What you can do however is use string concatting/formatting for that:

string table = "table";
NpgsqlCommand cmd = new NpgsqlCommand(string.Format(@"INSERT INTO {0}(text) VALUES (:word);", table), con);

Guess that would work (didn't test it).

answered May 26, 2012 at 15:48

Leon Cullens

12.5k11 gold badges56 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

elephantintheroom Over a year ago

I think you would need to cancel out the table strings special characters before you do string.Format .. (Would be pretty bad to get ; DELETE FROM tablenamehere; --)

Leon Cullens Over a year ago

Of course, but why would you accept user input for the table name? Doesn't make sense. But it can be useful for maintainability, define the table name once, use it everywhere.

Mike Ryan Over a year ago

However, this is a huge SQL injection security hole. You should either find a way to eliminate the need to dynamically specify the table name, or at a minimum do string escaping of any string you use for table. A java method would be org.apache.commons.lang.StringEscapeUtils.escapeSql(). Look for something similar in C# and don't try to do it by hand yourself -- it's a lot of work.

elephantintheroom Over a year ago

@Leon Cullens Well I have a lot of files that are generated in a specific way that go into different tables and I need to insert them from a shell script(requirement by the client)..

Leon Cullens Over a year ago

@MikeRyan, it's only vulnerable if you accept user input. Not if you just define the name of the table in your code and use that.

|

Collectives™ on Stack Overflow

Sql insert command in C#

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related