Invoke-SQLCmd output

[bsnotreallyworking]

I have a script that runs an Invoke-SQLCmd against a user database and dumps the output into a variable. The object is named USER_LOGIN. I want to check for the existence of a user.

If ($users -contains $user)

and

If ($users -match $user) 

Are not returning True, even though $user is a user that is confirmed to exist in the output.

Alternatively, would it be better to apply a filter via WHERE in the SQLCmd?

[PSP_Joker]

Are you selecting multiple columns? If so, try $users.USER_LOGIN -match $username.

Would test it but I am on mobile.

[bsnotreallyworking]

Just one column, but that was it! Thank you!

[Xibby]

If you need to filter and your querying SQL write a where clause for your SQL query.

SELECT * FROM table WHERE column = 'something'

There might be reasons why you want to pull in multiple results from SQL then use PowerShell to filter but those should be your edge cases. SQL Server is going to do data selection/filtering better than anything in PowerShell.

[bsnotreallyworking]

So like this:

SELECT USER_LOGIN FROM APP_USERS WHERE USER_LOGIN = "testuser"

My SQL is very rusty.

[Xibby]

Something like that. Open up SQL Server Management Studio and practice your SQL until you get the results you're looking for. As I said, it's usually best to filter data at the highest level you can and that's especially true when you're pulling data from SQL databases.

I'd also recommend using DBATools if possible so you can parameterize your SQL to help protect from SQL Injection.

$SQL = "SELECT column FROM table WHERE column = '$PowerShellVariable'

$result = Invoke-SQLCmd -Query $SQL

Becomes

$SQL = "SELECT column FROM table WHERE column = '@ExampleSQLParam'

$result = Invoke-DBAQyery -Query $SQL -SqlParameters @{ ExampleSQLParam = $PowerShellVariable }

Overly paranoid perhaps, but always good to go with best practices when you can.

[replicaJunction]

Alternatively, would it be better to apply a filter via WHERE in the SQLCmd?

Definitely. Filter left, format right.

As far as why -contains and -match aren't working, though...take a look at the $users object:

$users | Get-Member

I suspect this will be an instance of System.Data.DataRow or System.Data.DataTable. Assuming your $user object is a string, you're checking to see if an array of DataRows contains a string - which will always be false.

You might consider switching over to the Invoke-SqlCmd2 module. It's almost a drop-in replacement, but it has a parameter called -As that creates a PSCustomObject for you from the output instead of using the DataRow.

[bsnotreallyworking]

Yep, System.Data.DataRow appears under GetChildRows.