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

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.

You need to use subexpressions for the interpolation to work correctly: $($date.month) and $($date.year) — boxdog
– boxdog, Commented Jun 7, 2022 at 8:43
Larnu's answer is a safer alternative to your approach, but, as boxdog points out, the only problem with your approach is a syntax problem in your string-interpolation syntax: In short: In order to embed expressions in an expandable string ("..."), you must enclose them in $(...). Notably, this includes property and indexed access (e.g., $($var.property), $($var[0])). Only variables as a whole do not require this (e.g., $var, $env:USERNAME). See the linked duplicate. — mklement0
– mklement0, Commented Jun 7, 2022 at 12:32

Charlieface · Accepted Answer · 2022-06-07 11:30:09Z

5

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

edited Jun 7, 2022 at 11:30

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

answered Jun 7, 2022 at 8:51

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

AlwaysLearning Over a year ago

Instead of magic numbers you can specify [System.Data.SqlDbType]::Int

Thom A Over a year ago

Thanks, @AlwaysLearning . I tried without the brackets and it wasn't working; been some time since I've .Net within Powershell.

mouseskowitz Over a year ago

@Larnu thank you for your detailed explanation. While it solves the issue technically it adds more complexity than I was looking for. I also don't understand it well enough to know if it solves all the situations I've run into. boxdog gave me what I was looking for in the comments of the initial post.

Thom A Over a year ago

The thing is, if you're going down the injection route, you might as well not use the .Net objects, @mouseskowitz . If you just want to inject, why not just use invoke-sqlcmd?

Thom A Over a year ago

I've added an example for injection with Invoke-SqlCmd, @mouseskowitz , but I would suggest that if you want to actually parametrise your queries, then use the .Net objects and Parameters.Add.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Linked

Related