How to use IN clause with SQLParameters?

Question

My function works perfectly if I provide one instance of ComputerName value. My question is how to modify SQL query so it could accept an array? I don't believe creating a loop to query a database several times is a proper way. For example, I think this SQL query with IN clause is a proper way:

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

In other words, I'd like to be able to call this function providing multiple ComputerName values. Like this:

PS C:\>Get-Query_Database_Query1('comp01','comp02')

The code I need help with. Please use SQLParameters to build SQL query:

function Get-Query_Database_Query1
{
    [OutputType([System.Data.DataTable])]
    param
    (
        [Parameter(Mandatory = $false,
                   Position = 1)]
        [string]$PCname
    )
    
    #Database Query
    $QueryString = "select * from [Table1] where [ComputerName]=@PCname"
        
    #Database Connection String
    $ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Open\Database4.mdb;Password=;User ID=Admin'
    
    $command = New-Object System.Data.OleDb.OleDbCommand ($QueryString, $ConnectionString)
        $Command.Parameters.Add("@PCname", [System.Data.OleDb.OleDbType]::VarChar, 50).Value = $PCname;
    $adapter = New-Object System.Data.OleDb.OleDbDataAdapter ($command)
    
    #Load the Dataset
    $dataset = New-Object System.Data.DataSet
    [void]$adapter.Fill($dataset)
    
    #Return the Dataset
    return @( ,$dataset.Tables[0])
}

First of, your parameter `$PCName` should be changed to `[string[]]`. — Santiago Squarzon, Mar 26 '21 at 19:08
Please conform to standard parameter naming. `PCName` should be `ComputerName` — Colyn1337, Mar 26 '21 at 20:36

score -1 · Accepted Answer · answered Mar 26 '21 at 19:18

You need to do a little bit of string manipulation, also when working with SQL queries is a lot easier to use Here Strings.

If you are going to pass multiple computers to your functions, the parameter $PCname has to be able to accept an array of strings, hence changing [string] to [string[]].

Check out this code and see if it works for you:

function Get-Query_Database_Query1
{
    [OutputType([System.Data.DataTable])]
    param
    (
        [Parameter(Mandatory = $false,
                   Position = 1)]
        [string[]]$PCname
    )
    
    #Database Query
    $QueryString = @"
    SELECT * 
    FROM [Table1]
    WHERE [ComputerName] IN ('{0}')
"@ -f ($PCname -join "','")

$QueryString
}

Get-Query_Database_Query1 -PCname 'computer1','computer2','computer3','computer4'

Here is how the query should look like:

PS /home/> Get-Query_Database_Query1 -PCname 'computer1','computer2','computer3','computer4'
    SELECT * 
    FROM [Table1]
    WHERE [ComputerName] IN ('computer1','computer2','computer3','computer4')

I can't accept this answer. Your code is valuable to SQL injection. You need to perform an SQLCommand using SQLParameters to avoid it. — user2978216, Mar 26 '21 at 19:43
LOL I'm just doing what you asked in YOUR question. I already know this is not a good practice :) — Santiago Squarzon, Mar 26 '21 at 19:47

How to use IN clause with SQLParameters?

1 Answers1