CODEWITHSUNDEEP
c#ado.net
Michel
Michel

Reputation: 23635

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

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

Upvotes: 1

Views: 181

Answers (7)

Christian Hayter
Christian Hayter

Reputation: 31071

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.

Upvotes: 0

Rob Levine
Rob Levine

Reputation: 41318

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.

Upvotes: 1

andyb
andyb

Reputation: 790

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

Upvotes: 0

Adam Houldsworth
Adam Houldsworth

Reputation: 64517

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).

Upvotes: 0

Steve
Steve

Reputation: 50573

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.

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176936

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();

Upvotes: 1

leppie
leppie

Reputation: 117280

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

Rather build the SQL string containing the correct table name.

Upvotes: 3

Related Questions