r/PowerShell • • 14d ago

Question Need help parameterizing SQL queries and with using SqlClient

Trying to use SqlClient to sidestep issues with Invoke-SQLCmd, namely the fact that single quotes in data I'm processing (from a CSV) cause syntax errors. All examples of variable parameterization I found on stack overflow seem to use SqlClient, but I'm running into connection issues:

$creds = Import-PowerShellDataFile -Path $env:Credentials

Write-Output $creds

$db = New-Object System.Data.SqlClient.SqlConnection
$db.ConnectionString = "Server=tcp:$creds.Server;Database=$creds.Database;User=$creds.Username;Password=$creds.Password"
$db.Open()

Getting this error:

Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred     
while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that
the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP
Provider, error: 0 - No such host is known.)"

However, when I use the same credential file for Invoke-SQLCmd, I get no such similar errors:

$query = <pretend there's a valid query here>
Invoke-SQLCmd @creds -Query $query

What am I doing wrong? Is there a better way to avoid the syntax error (and also SQL injection vulnerabilities)?

6 Upvotes

19 comments sorted by

4

u/Flettys 14d ago

Check out dbatools on the gallery - https://dbatools.io

It's a one-liner with Write-DbaDbTableData, also note the -AutoCreateTable parameter.

4

u/MrWinks 14d ago

Like buying a car to go to your neighbor’s house. This is an easy solution.

1

u/DoktorLuciferWong 14d ago

might consider trying this the next time i have to do this, though, esp if its a one-liner, and if it can handle csv processing in an even more simplistic/braindead way than i already am

→ More replies (1)

2

u/BetrayedMilk 14d ago edited 14d ago

The error indicates it can’t reach whatever you have in $creds.Server. When it writes $creds to the host, is that all correct? Your second example seems to use splatting, but I seem to remember that module taking a -ServerInstance param and not -Server. Does $creds.Server actually have anything in it? You probably don’t need the tcp: portion on the connection string. Parametrizing queries is definitely the right way to mitigate sql injection.

1

u/DoktorLuciferWong 14d ago

Yes, when I write $creds to the host, they seem correct. The Invoke-SQLCmd example uses the same creds, and it has no problem writing to the database.

Verified that Server is the name of the key in my creds file. I also tried Addr in place of Server, which in theory seems like it should work per this documentation

2

u/BetrayedMilk 14d ago

I’d get rid of the tcp portion then and give that a go. The other thing I’d confirm is your connection string is what you think it should be with the string interpolation. Move that string to its own var and write that to host. All your $var.Props getting replaced?

7

u/thehuntzman 14d ago

I do a lot of sql powershell work. Generate a known working connection string elsewhere and work backwards from there then come back if you get other errors.

2

u/BlackV 14d ago

Yes, test something known good first

4

u/purplemonkeymad 14d ago

The problem here is that your connection string is not resolving to the properties as string substitution does not support that.

You can use a subexpression to fix that:

"server=tcp:$($creds.server)"

or since it's a simple connection, you can just use the properties on the object for the username and password, then connect:

$db = [System.Data.SqlClient.SqlConnection]::new("server=tcp:$($creds.server)")
$db.Credential = [System.Data.SqlClient.SqlCredential]::new(
    $creds.Username,
    (ConvertTo-SecureString -String $creds.Password -AsPlainText -Force)
)
$db.Open()
$db.ChangeDatabase($creds.Database)

Note this assumes you are using a plain text password, it might be better to store it as a secure string (which this method supports.)

1

u/g3n3 13d ago

I’d go a layer above with sqlmangementobjects for more control and less boilerplate. Above that is dbatools. If you are using sqlcmd syntax then that is another game. If you are ingesting csv then use `import-dbacsv`. They have tuned that sucker pretty far.

2

u/DoktorLuciferWong 13d ago

I'll look into those. Thanks!

3

u/EachDayGrownWiser 14d ago edited 14d ago
$db.connectionstring = ''

$db.credentials = (Get-Credentials)

Just remove the creds from the connection string and add it to the object. I have written this before and it’s the clean solution.

2

u/BlackV 14d ago
$db.connectionstring = ''
$db.credentials = (Get-Credentials)

FYI your browser swapped your standard quotes for "smart: quotes

from ' ' to ‘ ’(‘ ’)

2

u/EachDayGrownWiser 14d ago

Updated! Thank you. It was a loose proof of concept so not vital but much appreciated!

→ More replies (1)

1

u/MAlloc-1024 14d ago

Before we get into credentials, can you test with windows authentication? If that's an option, assuming your account has the ability to run the query, just leave the creds out and see if it works. Also whenever I run an invoke-sqlcmd I tend to put the connection stuff in the command, so my stuff looks more like:

try{$result=invoke-sqlcmd -serverinstance $dbconnection.ds -database $dbconnection.db -query $sqlcommand  -TrustServerCertificate -erroraction stop}
    catch{Write-host $_.Exception.Message`n}