I'm loading a regex from a file into a database using a stored procedure, like this:
$stuff = Get-Content -Path '.\data.txt' -Raw
Invoke-Sqlcmd -Query "Exec dbo.loadStuff @stuff='$stuff'" -ServerInstance $server -Database $database -Username $username -Password $password
This works most of the time, but sometimes the file contains $randomtext which PowerShell then fails to expand because the $randomtext variable doesn't exist.
Is there a way to tell PowerShell to pass the contents of the $stuff variable as is without expanding it?
Adding -DisableVariables doesn't appear to work.
EDIT: As zett42 pointed out, PowerShell isn't performing a second expansion. It is passing the full text to sqlcmd.
sqlcmd is finding $(randomtext) and treating it as a variable. -DisableVariables should have fixed this, but it hasn't.
After removing all $ signs from the test source file it is still failing, and I suspect that it is due to $stuff containing single and double quotes.
I hadn't realised that Invoke-Sqlcmd is simply a pass through to sqlcmd. I will stop using Invoke-Sqlcmd and follow the resource provided by zett42 instead.
Invoke-Sqlcmd is great for simple calls, but if you're going to do anything more complex (like pass arbitrary text as a parameter) it's best to use the .NET stack like System.Data.SqlClient.SqlCommand.
I have rewritten my code to use this more appropriate approach and I am able to pass bound parameters (no possibility of SQL injection) without either PowerShell or sqlcmd mangling or trying to parse the parameter values (both of which were the cause of the original problems).