2

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.

Improve this question
3
  • 1
    Use two single quotes instead of escaping : $insFile = 'c:\Myfiles\Keith''s Document.docx' $insFile = $insFile.Replace("'","''") $insFile Commented May 18, 2023 at 11:23
  • 2
    you should be using parameterized queries, this is not the right way to go Commented May 18, 2023 at 12:26
  • 1
    Thanks for the nudge in the right direction @SantiagoSquarzon I initially thought that was purely from a security perspective... but now having found how to do that and got that working I know better. :) Commented May 18, 2023 at 13:29

2 Answers 2

Reset to default
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.

  • 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:

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

10 Comments

you can also use a System.Data.SqlClient.SqlConnection as argument for your System.Data.SqlClient.SqlCommand :) that way you dont need to hardcode the DB you are writing to in the query string
Cool. In this instance the entire script actually connects to two different databases on the same server, and it's the other one used in the initial connection string. Plus the uniquely named $dbcompare DB and PathA table are created by the script. So when reading my code it makes it a bit easier this way to keep track of what is being referenced where. :)
As for the escaping issue: note that, outside "...", you never need to enclose variable references in $(...) (and even inside "..." you only need it for expressions, not simple variable references). Thus, $sqlCommand.Parameters.Add("@file", $($file.Name)) can be simplified to $sqlCommand.Parameters.Add("@file", $file.Name) for instance. Conversely, your string interpolation attempt was broken, because the .Replace() call must be inside the embedded $(...): $insFile = "'$($file.Name.Replace("'","''"))'" - also note that no backticks (`) are needed.
@mklement0 they probably meant .AddWithValue not .Add prob. a typo on the answer
@mklement0 I grabbed from stackoverflow.com/questions/16734039/… and it worked, but having looked elsewhere now I see what you mean. Will probably tweak to use standard method rather than rely on luck with it inferring the data types. Remarks here learn.microsoft.com/en-us/dotnet/api/… might explain, suggests syntax I used works but deprecated + I'm running script with PS5.1 on Server 2012, so might not work on newer.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.