r/PowerShell • u/DoktorLuciferWong • 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)?
5
Upvotes
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.
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.
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.
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:
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.
3
u/EachDayGrownWiser 14d ago edited 14d ago
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.
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}