-1

I am building a sql query string like this:

var sqlDailyDataForOption = @"select underlying_symbol, quote_date
                            FROM [@val6]

Later I add the command line parameters thus:

command.Parameters.AddWithValue("@val6", o.underlying_symbol + "_1545");

I get an exception when I try to execute the query string:

using (SqlDataReader reader = command.ExecuteReader())
        {

-       $exception  {"Invalid object name '@val6'."}    System.Data.SqlClient.SqlException

However, if I hardwire the value o.underlying_symbol + "_1545" it works fine.

Is it that command-parameters can't be dynamically created in a FROM ?

Ivan
  • 7,448
  • 14
  • 69
  • 134
  • 2
    Table names have to be static. – LarsTech Aug 31 '21 at 15:11
  • Ugh, that sucks that they have to be static. Is there a work around? – Ivan Aug 31 '21 at 15:12
  • 2
    Why do you *need* to do such a substitution? It seems you have a broken schema to begin with. – Alejandro Aug 31 '21 at 15:13
  • `var sqlDailyDataForOption = $@"SELECT underlying_symbol, quote_date FROM [{tableNameVariable}]"` – Peter Csala Aug 31 '21 at 15:14
  • 1
    It's by design, @Ivan. Despite what people *think* SQL is a compiled language, and having a dynamic object name means that compilation process fails. – Thom A Aug 31 '21 at 15:14
  • 1
    And don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) – SMor Aug 31 '21 at 15:15
  • I guess I will create the sting myself. – Ivan Aug 31 '21 at 15:16
  • @SMOR what should I use instead, and why not? – Ivan Aug 31 '21 at 15:16
  • 2
    @Ivan have you read the linked article? – derpirscher Aug 31 '21 at 15:17
  • @SMor Ah, thanks didn't notice that. – Ivan Aug 31 '21 at 15:21
  • 1
    @Ivan SQL is a compiled , strongly-typed, semi-functional language and tables are equivalent to types. A SQL query is compiled by the server into an actual execution plan based on the table's indexes and data statistics. Different operations will be used if an index exist, different if a search field has only two possible values. An INNER JOIN may get executed using nested or hash joins, based on indexes and how many rows the server expects, based on the data statistics. The same query, on the same server, may result in different execution plans as data changes – Panagiotis Kanavos Aug 31 '21 at 15:25
  • @Ivan parameters on the other hand are the same as parameters in any statically-typed language. They're used to pass *values* not types. – Panagiotis Kanavos Aug 31 '21 at 15:28

2 Answers2

1

You would have to use dynamic SQL and safely inject the value of the dynamic object into the statement. I'm not a C# developer, but I suspect it'll look something like this:

var sqlDailyDataForOption = @"DECLARE @SQL nvarchar(MAX) = N'SELECT underlying_symbol, quote_date FROM dbo.' + QUOTENAME(@var6) + N';'; EXEC sys.sp_executesql @SQL;"
command.Parameters.Add("@Var6", SqlDbType.NVarChar, 128).Value = o.underlying_symbol + "_1545"
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

try this

var val16=o.underlying_symbol + "_1545";
var sqlDailyDataForOption = $"select underlying_symbol, quote_date  FROM [{val16}]";

but please remember about possibility sql script injection attack in this case and check val16 for malicious words like delete.

Serge
  • 40,935
  • 4
  • 18
  • 45
  • 3
    please, at least put the tablename into square brackets ... – derpirscher Aug 31 '21 at 15:16
  • That doesn't actually solve the problem though, @derpirscher . There's nothing stopping me inputting the following for the value of `val6`: `SomeRealTable]; CREATE LOGIN L WITH PASSWORD = '1', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD MEMBER L;--` – Thom A Aug 31 '21 at 15:41
  • 1
    @Larnu of course it doesn't. I was not even talking about attacks (don't know whether the `o.underlying_symobl` is something entered by the user or generated by the system) but just preventing "random" errors, where queries start to fail because someone wasn't careful enough when generating table names ... – derpirscher Aug 31 '21 at 16:59