i'm lost: what is wrong with this ado.net code?

Question

well, the question is clear i hope, the code is this:

string sql = "delete from @tabelnaam";
            SqlCommand sc = new SqlCommand();

                sc.Connection = getConnection();
                sc.CommandType = CommandType.Text;
                sc.CommandText = sql;
                SqlParameter param = new SqlParameter();

                param.Direction = ParameterDirection.Input;
                param.ParameterName = "@tabelnaam";
                param.Value  = tableName;

                sc.Parameters.Add(param);
                OpenConnection(sc);
                sc.ExecuteScalar();

tableName is supplied to this function.

I get the exception:

Must declare the table variable @tabelnaam

score 3 · Answer 1 · answered Jun 24 '10 at 10:43

3

IIRC, you cant use a substitute the table name for a parameter.

Rather build the SQL string containing the correct table name.

answered Jun 24 '10 at 10:43

leppie

115,091
17
196
297

doesn't that lead to sql injection? – Michel Jun 24 '10 at 11:12
@Michel: How do you come to that conclusion? – leppie Jun 24 '10 at 11:20
2

of course it is a sql injection risk if the source of the string is via an untrusted source (such as user input). What if I entered the following "table name": "MyTable; DROP DATABASE MyDb". – Rob Levine Jun 24 '10 at 11:31

score 1 · Accepted Answer · answered Jun 24 '10 at 10:44

1

Make to changes

rather than using paramter use this

string sql = string.format( "delete from {0}",tableName);

make use of executenonquery intead of ExecuteScalar

sc.ExecuteNonQuery();

answered Jun 24 '10 at 10:44

Pranay Rana

175,020
35
237
263

doesn't that lead to sql injection? – Michel Jun 24 '10 at 11:15
you can avoide this by validating the tablename properly – Pranay Rana Jun 24 '10 at 11:37

Rob Levine · Answer 3 · 2010-06-24T11:43:24.910

1

As mentioned by others, you can't parameterise the table name.

However, as you rightly mention in comments on other answers, using simple string manipulation potentialy introduces a SQL injection risk:

If your table name input is fro an untrusted source, such as user input, then using this:

string sql = string.format( "DELETE FROM {0}",tableName);

leaves you open to the table name "myTable; DROP DATABASE MyDb" being inserted, to give you:

DELETE FROM myDb; DROP DATABASE MyDB

The way round this is to delimit the table name doing something such as this:

string sql = string.format("DELETE FROM dbo.[{0}]", tableName);

in combination with checking that the input does not contain either '[' or ']'; you should probably check it also doesn't contain any other characters that can't be used as a table name, such as period and quotes.

edited Jun 24 '10 at 11:43

answered Jun 24 '10 at 11:37

Rob Levine

40,328
13
85
111

You mean `dbo.` not `do.`? Tables can live in other schemas, though. – Rup Jun 24 '10 at 11:41
@Rup - I did - good spot, thank you. It's true, they can live in other schemas. I am just trying to impress the idea upon the OP to keep things *as restricted as possible*. If the tables are in other schemas, then this would need relaxing, but *only if* that is the case. – Rob Levine Jun 24 '10 at 11:44

score 0 · Answer 4 · answered Jun 24 '10 at 10:44

0

I dont think you can parameterize the table name. From what I have read you can do it via Dynamic sql and calling sp_ExecuteSQL.

answered Jun 24 '10 at 10:44

Steve

50,173
4
32
41

huh? so i can do this 'select * from client where name = @name' but i can't do this: 'delete from @table'? – Michel Jun 24 '10 at 11:16

score 0 · Answer 5 · edited Jun 24 '10 at 10:46

0

Your SQL is incorrect, you are deleting from a table variable yet you haven't defined that variable.

Update: as someone has pointed out, you are trying to dynamically build a query string but have inadvertantly used SQL parameters (these do not act as place holders for string literals).

edited Jun 24 '10 at 10:46

codingbadger

42,678
13
95
110

answered Jun 24 '10 at 10:44

Adam Houldsworth

63,413
11
150
187

i'm not sure i know what you mean. – Michel Jun 24 '10 at 11:14
The @tableName syntax you used followed by the comm.Parameters collection gave you something called "Parameterized SQL". The command was trying to put a "value" into the @tableName instead of the string literal that you intended. You simply wanted to build a chunk of SQL without any parameters using a string. – Adam Houldsworth Jun 24 '10 at 11:23

score 0 · Answer 6 · edited May 23 '17 at 12:01

0

More here: Parameterise table name in .NET/SQL?

edited May 23 '17 at 12:01

Community

1
1

answered Jun 24 '10 at 10:49

andyb

770
5
11

Christian Hayter · Answer 7 · 2010-06-24T11:55:07.103

You cannot parameterise the table name, you have to inject it into the command text.

What you can and should do is protect yourself against SQL injection by delimiting the name thus:

public static string Delimit(string name) {
    return "[" + name.Replace("]", "]]") + "]";
}

// Construct the command...
sc.CommandType = CommandType.Text;
sc.CommandText = "delete from " + Delimit(tableName);
sc.ExecuteNonQuery();

See here and here for more background info.

i'm lost: what is wrong with this ado.net code?

7 Answers7