Split string into separate strings in PowerShell

user_11365188 0
Viewed 120

My script in powershell runs: test.ps1 -dbo test1 => this works, it enters provided database

SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1');

but if i want to enter multiple databases e.g test.ps1 -dbo test1, test2, test3 => this is read as one string

SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1, test2, test3');

How can I make in Powershell to split string into seperate, like that:

SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1', 'test2', 'test3');

code:

Param(
    [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
    [Alias("dbo")]
    [String[]]
    $databaseName
)

$dbquery = @"
        SELECT name FROM sys.databases
        where name not in ('master', 'tempdb', 'model', 'msdb', '$databaseName');
"@
Invoke-Sqlcmd -ServerInstance "$databaseIP" -Database "master" -Username $databaseUser -Password $databasePass -Query $dbquery

I tried with split and join but it does not work as i expected

powershell sql-server
1 Answers

NOTE: Any time you are taking parameters and directly putting them into a SQL string, you can open yourself up to malicious coders and SQL injection issues. It is recommended to sanitize your inputs (make sure the input is valid) before you use them in a SQL string. Exploits of a Mom

You are getting exactly what you're asking for by putting an array of strings into a string without pre-processing. A simple way of doing this is to fill in the stuff between the array members first, so add the ', ' with a -join like this (Adding plural array name for an array as suggested by @CaiusJard):

Param(
    [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
    [Alias("dbo")]
    [String[]]
    $databaseNames
)

$databaseNameString = $databaseNames -join "', '"
$dbquery = @"
        SELECT name FROM master.dbo.sysdatabases
        where name not in ('master', 'tempdb', 'model', 'msdb', '$databaseNameString');
"@
Invoke-Sqlcmd -ServerInstance "$databaseIP" -Database "master" -Username $databaseUser -Password $databasePass -Query $dbquery

I hope this helps you out.

user_85074730
Related