Parameterized SQL in FROM clause

Question

Using OleDbCommand. I can add SQL parameters and thus keep the query safe from SQL-injection, but is there a way to do this within the FROM cluase.See below

THIS WORKS

query = "Select * From Company Where @param = 1";
OleDbCommand Command = new OleDbCommand(query, sqlConnStr);

DataTable Table = new DataTable();
DataSet dataSet = new DataSet();
Table = null;

//Add Parameters
Command.Parameters.AddWithValue("param", "ID");
Command.ExecuteNonQuery();
adapter.SelectCommand = Command;
adapter.Fill(dataSet);
Table = dataSet.Tables[0];

it returns a nice table with the wonderful row where id = 1

BUT

I am looking for something like this, note the FROM CLAUSE

query = "Select * From @tableName Where @param = 1";
OleDbCommand Command = new OleDbCommand(query, sqlConnStr);

DataTable Table = new DataTable();
DataSet dataSet = new DataSet();
Table = null;

//Add Parameters
Command.Parameters.AddWithValue("param", "ID");
Command.Parameters.AddWithValue("tableName", "Company");
Command.ExecuteNonQuery();
adapter.SelectCommand = Command;
adapter.Fill(dataSet);
Table = dataSet.Tables[0];

The DBMS keeps returning with "Error in From clause"

PS everything is spelled correctly - I triple checked

To All - Thanks but Alas i shall stick to just Parameterized SQL. I don't like Dynamic SQL One BIT

According to Microsoft OleDbCommand does not even support named Paramters http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx yet the top code excerpt works fine — Aiden Strydom, May 17 '12 at 15:46

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

You'd have to execute a dynamically concatenated SQL string, which unfortunately would compromise whatever benefits parametrized SQL had afforded you to begin with. See this post.

DECLARE @SQL varchar(250)
SELECT @SQL = 'Select * From ' + @TableName + ' Where ' + @param + ' = 1'
Exec(@SQL)

Not advisable, if you can predetermine the table name through some other means instead.

edited May 23 '17 at 11:44

Community

1
1

answered May 17 '12 at 15:55

LesterDove

3,014
1
23
24

You should wrap @TableName and @param With [QUOTENAME](http://msdn.microsoft.com/en-us/library/ms176114.aspx). i.e. `from ' + QUOUTENAME(@TableName) + ' where`. This not only reduces the chance of syntax errors due to table/column names being reserved words, or containing special characters. – GarethD May 17 '12 at 16:03

score 1 · Answer 2 · answered May 17 '12 at 15:59

1

SQL doesn't support the FROM clause to be parameterized. You can't do it the way you are trying. Probably you can do like this:

query = "Select * From" + tableName + "Where @param = 1";

answered May 17 '12 at 15:59

Rahul

76,197
13
71
125

Yep correct, it is volunerable but that just's an example and show go for it at own risk. – Rahul May 17 '12 at 16:26

score 1 · Answer 3 · answered May 17 '12 at 16:01

You cannot use paramaterised object names. You would have to do somthing like

string.format("SELECT * FROM [{0}] WHERE [{1}] = 1", tableName, columnName);

DO SO AT YOUR OWN PERIL

It could be worth checking for square brackets in the input, this is the only way I can currently think off to "Inject" the above example. I am no hacker though and not versed in sql injection. Something like this might work:

        if (tableName.Contains("]"))
        {
            throw new ArgumentException();
        }

Parameterized SQL in FROM clause

3 Answers3