3

I am trying to create a parameterized query in C# against a SQL server database.

Code: 

        query = new StringBuilder( "SELECT @fields FROM @tables");

        using(SqlConnection connection = new SqlConnection(connection))
        {
            SqlCommand command = new SqlCommand(query.ToString(), connection);
            command.Parameters.AddWithValue("@fields", fields.ToString());
            command.Parameters.AddWithValue("@tables", tables.ToString());

            try
            {
                connection.Open();
                Int32 rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("RowsAffected: {0}", rowsAffected);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

The strange part is this fails with the message "Must declare the table variable \"@tables\". However as you can see, it's clearly been defined.

So my question is:

  1. Can you pass a parameter to define the table list in the FROM statement?
  2. If you can, why isn't this working?
morganpdx
  • 1,856
  • 2
  • 29
  • 46
  • 1
    As a side-note to these answers about dynamic SQL. Always, always, always be very careful when building dynamic SQL, to avoid SQL injection. When you build SQL on the fly, you open yourself up, so make sure you're checking it, either in your application, or in the SQL script itself. – Joe Enos Feb 03 '11 at 21:45
  • @Joe Exactly why I'm trying to parameterize as much as I can. The Where clause will be completely parameter-driven as well - add to that the fact that the user interface has no text fields, only checkboxes and multiselect lists, I should be pretty well covered. – morganpdx Feb 03 '11 at 23:16
  • 1
    You can't necessarily trust select-box input. It's very simple for a malicious user to run javascript on the form that changes the values there, or to simply post the form manually with the values they want, rather than what your form is supposed to restrict you to. (This is assuming this is a web app, but the same concept applies, just a little differently, to Windows apps). So make sure you've got another layer of protection in your server code as well. – Joe Enos Feb 03 '11 at 23:22
  • @Joe Right...which was my reason for wanting to parameterize the SELECT and FOR statements as well. Fooey. – morganpdx Feb 03 '11 at 23:25

4 Answers4

7

SQL doesn't support the FROM clause to be parameterized. So you have to use either dynamic SQL, or create/concatenate the query string prior to submitting it to the database.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Watch out for SQL Injection if you do go this route. – Abe Miessler Feb 03 '11 at 21:43
  • @Abe Miessler: That's exactly why the FROM clause doesn't support substitution. – OMG Ponies Feb 03 '11 at 21:44
  • Really? Why would it work for parameters but not table names? Related: http://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection – Abe Miessler Feb 03 '11 at 21:56
  • 1
    An easy way to always be safe when concatenating SQL is to use this construct ` string.format("'{0}'", arg.replace("'", "''"))` and use it on every argument in the same statement as the concatenation. Never trust a variable unless its a numeric. I have so far never found any inject that gets past this as long as you are vigilant in implementing it everywhere (even on data you just read from your own database, you never know what another code put there) – David Mårtensson Feb 03 '11 at 23:24
2

No unfortunately you cant use a parameter in the FROM clause.

JK.
  • 21,477
  • 35
  • 135
  • 214
1

I think this is not the way SQL command and its parameters should look like. It should look like

SELECT fieldName1, fieldName2
FROM   TableName
WHERE  fieldName = @paramName

You cannot use parameters as definition of fields to be selected or the target table. If you need to define fields to be selected, simply compose the command string in StringBuilder before you call it - as you need. Parameters are used for filtering purposes. In your case you don't need any paramters, just build your command and execute.

Ondrej Vencovsky
  • 3,188
  • 9
  • 28
  • 34
0

If you're confident that your table and column names are ok, then you can do some safety checks in the database before building your dynamic SQL.

This is just for illustration - for real life, obviously you'd need to make it a lot cleaner:

declare @TABLE_NAME nvarchar(128)
set @TABLE_NAME = 'Robert'');DROP TABLE Students;--' -- This line will raise an error
set @TABLE_NAME = 'BOOK' -- This line will go through properly

declare @sql varchar(max)
set @sql = 'SELECT * FROM '

if exists (select 1 from sys.objects where type = 'U' and name = @TABLE_NAME)
    begin
        set @sql = @sql + @TABLE_NAME
        exec (@sql)
    end
else
    begin
        raiserror ('ERROR ERROR ERROR', 0, 0)
        return
    end
Joe Enos
  • 39,478
  • 11
  • 80
  • 136