How do I pass an variable/array member to a SQL query in PowerShell?

Question

This has probably been answered somewhere before, but I can't figure out how to ask google the right question. To be able to define fewer variables, I would like to be able to pass a member of a variable or array to a SQL query. As an example, I would like to be able to define something like $date = get-date and use $date.month to just pass just the month in the query.

The issues I run into is that the period used to define the member seems to break things in a SQL query. Is there a way to properly punctuate this type of variable in this situation?

For fuller context:

$ConnectionString = "Server=" + $TargetServer + ";Database=" + $TargetDatabase + ";Trusted_Connection=$true;";
$TargetConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$TargetConnection.Open();

$date = get-date

$sql = "SELECT *
  FROM [database].[dbo].[table]
  where ([MONTH] = $date.month and [YEAR] = $date.year)"

$TargetCommand = New-Object System.Data.SqlClient.SqlCommand($sql, $TargetConnection);
$TargetCommand.ExecuteScalar()
$TargetConnection.Close()

The month and year columns have the values stored as int.

Answer 1

As I mentioned, what you have above injection, not parametrisation. You don't tell us why what you have isn't working, so this is some what of a guess, but most likely you want something like this:

$connectionString = 'Server=' + $TargetServer + ';Database=' + $TargetDatabase + ';Trusted_Connection=$true;'
$sqlConn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
#Start a try, so that we can close the connection on error
try{
    $sqlConn.Open()
    $sqlCmd = $sqlConn.CreateCommand()
    $sqlCmd.Connection = $sqlConn #Assign the connection to the command
    $query = "SELECT *
    FROM [dbo].[table] --Database isn't needed, you have defined it in your connection string
    WHERE [MONTH] = @Month and [YEAR] = @Year;" #Define the query
    $sqlCmd.CommandText = $query
    $date = get-date #Get the current date
    $sqlCmd.Parameters.Add("@Month", 8).Value = $date.month #Add Month Parameter
    $sqlCmd.Parameters.Add("@Year", [System.Data.SqlDbType]::Int).Value = $date.year #Add year Parameter
    #I don't know what you want to do with the data, so I put it into a data adapter
    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
    $dataSet = New-Object System.Data.DataSet
    $sqlAdapter.Fill($dataSet)
}
finally{
    if ($sqlAdapter -ne $null) { $sqlAdapter.Dispose(); }
    if ($sqlConn -ne $null) { $sqlConn.Close(); }
}

$dataSet.Tables

You can get the list of the enum values for the parameter types in the documentation; note I use 8 in the above for int. AlwaysLearninghas since reminded me of the correct syntax, so I now demonstrate both. @Month passes uses an enum value, an @Year uses the name of the datatype from sqlDbType.

From the comments it seems like you want to inject; in some ways this defeats of the object of using the .Net objects. If you simply want to inject the data, the you could just use Invoke-SqlCmd:

Invoke-Sqlcmd -Query "SELECT * FROM dbo.[table] WHERE Month = $($(get-date).month) AND [Year] = $($(get-date).year);" -ServerInstance $TargetServer -Database $TargetDatabase