1

I've found plenty of post explaining how to literally escape both single and double quotation marks using either """" for one double quotation mark, or '''' for a single quotation mark (or just doing `"). I find myself in a situation where I need to search through a list of names that is input in a different query:

Foreach($Username in $AllMyUsers.Username){

    $Filter = "User = '$Username'"
    # do some more code here using $Filter

}

The problem occurs when I reach a username like O'toole or O'brian which contains quotation marks. If the string is literal I could escape it with

O`'toole
O''brian
etc.

But, since it's in a loop I need to escape the quotation mark for each user.

I tried to use [regex]::Escape() but that doesn't escape quotation marks.
I could probably do something like $Username.Replace("'","''") but it feels like there should be a more generic solution than having to manually escape the quotation marks. In other circumstances I might need to escape both single and double, and just tacking on .Replace like so $VariableName.Replace("'","''").Replace('"','""') doesn't feel like it's the most efficient way to code.

Any help is appreciated!

EDIT: This feels exactly like a "how can I avoid SQL injection?" question but for handling strings in Powershell. I guess I'm looking for something like mysqli_real_escape_string but for Powershell.

mklement0
  • 382,024
  • 64
  • 607
  • 775
Tanaka Saito
  • 943
  • 1
  • 17
  • 40
  • 1
    What will you be using `$Filter` for? `''` is a valid escape sequence for a literal `'` _in PowerShell_, but not in WQL for example. – Mathias R. Jessen Sep 30 '21 at 01:13
  • More specifically it is a DataTable and I will use .RowFilter. I found a blogpost saying "just use the .Replace" but it felt like a dirty trick and only works for that particular escape. I mean that workaround still works, I'm just not happy with it haha :) – Tanaka Saito Sep 30 '21 at 01:23

1 Answers1

2

I could probably do something like $Username.Replace("'","''") but it feels like there should be a more generic solution than having to manually escape the quotation marks

As implied by Mathias R. Jessen's comment, there is no generic solution, given that you're dealing with an embedded '...' string, and the escaping requirements entirely depend on the ultimate target API or utility - which is unlikely to be PowerShell itself (where ' inside a '...' string must be doubled, i.e. represented as '').

In the case at hand, where you're passing the filter string to a System.Data.DataTable's .DataView's .RowFilter property, '' is required as well.

The conceptually cleanest way to handle the required escaping is to use -f, the format operator, combined with a separate string-replacement operation:

$Filter = "User = '{0}'" -f ($UserName -replace "'", "''")

Note how PowerShell's -replace operator - rather than the .NET [string] type's .Replace() method - is used to perform the replacement.

Aside from being more PowerShell-idiomatic (with respect to: syntax, being case-insensitive by default, accepting arrays as input, converting to strings on demand), -replace is regex-based, which also makes performing multiple replacements easier.

To demonstrate with your hypothetical .Replace("'","''").Replace('"','""') example:

@'
I'm "fine".
'@ -replace '[''"]', '$0$0'

Output:

I''m ""fine"".
mklement0
  • 382,024
  • 64
  • 607
  • 775