Escaping single quote in string within Powershell before passing value to SQL

Question

I have a script that retrieves information relating to the files within a given folder path, and then passes those values via the SQLPS module and the SQL INSERT INTO command to populate my database table.

That works fine except for a few occasions where the file or folder path include an apostophe.

The relevant block of code is

$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -Recurse | Where-Object {!($_.PSIsContainer)} | Select DirectoryName, Name, Length, LastWriteTime 
ForEach ($file in $filelist)
{
    # Retrieve all values into variables. Value passed to the insert statement needs to be within single quotes, so escaping them out here so they're included later.
    $insFolder = "`'$($file.DirectoryName)`'"
    $insFile = "`'$($file.Name)`'"
    $insLength = "`'$($file.Length)`'"
    $insLastWrite = "`'$($file.LastWriteTime)`'"
    write-host "$insFolder`n$insFile`n$insLength`n$insLastWrite`n---"
    # Generate the SQL insert statement using the values above
    $sqlCommand.CommandText = "INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime) VALUES ($insFolder,$insFile,$insLength,$insLastWrite)"
    # Execute the SQL command while also incrementing the row count for use later
    $PathArowCount = $PathArowCount + $sqlCommand.ExecuteNonQuery()
} 
write-output "$(Get-Date) - $PathArowCount rows (eg files found) inserted into table PathA"

Where as you can see I'm already setting the input values $insFolder, $insFile, $insLength and $insLastWrite that are passed to the SQL command to include single quotes since that's expected by SQL.

My problem is where a file/folder name includes an apostrophe/single quote, for instance :

c:\Myfiles\Keith's Document.docx

I get the following error from the $sqlCommand.ExecuteNonQuery() line :

Exception calling "ExecuteNonQuery" with "0" argument(s): Incorrect syntax near 's'.
Unclosed quotation mark after the character string')'."

The Write-Host line shows me that at that point $insFile does correctly contain the entire string as expected with single quotes at either end, eg :

'Keith's Document.docx'

But from the error SQL is presumably seeing the value passed to it as

'Keith'

and then erroring by the unexpected argument :

s Document.docx'

I obviously need to find and Escape out that character where relevant, and I've tried various combinations for instance

$insFile = "`'$($file.Name).Replace("`'","\`'")`'"

where I'm escaping the single quotes within the comparison values with backticks, and trying to add a \ to the backtick to escape that character when it reaches SQL, but every combination I've tried throws a Powershell error so I'm unsure what the magic combination is.

Answer 1

tl;dr

• It is always preferable to use a parameterized query rather than string interpolation, both to avoid quoting headaches and, more importantly, to eliminate any possibility of a SQL injection attack.

  • See this answer for an example based on SqlCommand.ExecuteNonQuery().

• If you do want to stick with string interpolation, you must escape value-internal ' as '':

  $insFile = "'$($file.Name -replace "'", "''")'"
  
  # Alternative:
  $insFile = "'{0}'" -f ($file.Name -replace "'", "''")
  

---

Quoting considerations:

The PowerShell perspective with respect to string literals:

• Inside "..." - expandable (interpolating) strings - ' chars. do not require escaping and can be used as-is.

• Inside '...' - verbatim strings - embedded ' chars., must be escaped as '' - however, such strings are not involved here; see below.

The T-SQL perspective (where string literals are called character string constants):

• '...' strings embedded in a query strings happen to also require escaping string-internal ' as ''.

• ("..." literals aren't supported by default, and neither form is interpolating)

Answer 2

Big thanks to @SantiagoSquarzon for pointing me in what I didn't even realise was the right direction at the time. Got my script using parameterized queries (once I'd learnt how) thinking that would improve the security, and was pleasantly surprised to realise (with hindsight it's kind of obvious) that it also negates the issues with escape characters.

For anyone interested, the final working code I've ended up with is

$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -File -Recurse | Select DirectoryName, Name, Length, LastWriteTime 
ForEach ($file in $filelist)
{
    $sqlCommand.CommandText = "INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime) VALUES (@Folder,@File,@Length,@LastWrite)"
    $sqlCommand.Parameters.Clear()
    $sqlCommand.Parameters.Add("@folder", $($file.DirectoryName)) | Out-Null
    $sqlCommand.Parameters.Add("@file", $($file.Name)) | Out-Null
    $sqlCommand.Parameters.Add("@length", $($file.Length)) | Out-Null
    $sqlCommand.Parameters.Add("@lastwrite", $($file.LastWriteTime)) | Out-Null
    $PathArowCount = $PathArowCount + $sqlCommand.ExecuteNonQuery()
} 
write-output "$PathArowCount rows (eg files found) inserted into table PathA"

Note, from the comments below it appears the .Add( method as used here has been deprecated, so it might not work in newer versions. Confirmed to be working with Powershell 5.1 on Windows Server 2012 with SQL 2012, and Windows Server 2012 R2 with SQL 2016.

Interestingly, before I reached that point and while still to wrangle the escape characters I found that while

$insFile = "`'$($file.Name).Replace("'","''")`'"

gives a Powershell error, splitting it into two lines like this

$insFileTmp = $($file.Name).Replace("'","''")
$insFile = "`'$insFileTmp`'"

worked fine and did what I was previously (before knowing better!) looking for.