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)