Escaping Strings Containing Single Quotes in Powershell Ready for SQL Query

Escaping strings containing single quotes in PowerShell ready for SQL query

You can try to update your code to to use a parametrised value that will cope with quotes in a string:

$query = "INSERT INTO People(name) VALUES(@name)"

$command = $connection.CreateCommand()
$command.CommandText = $query
$command.Parameters.Add("@name", $name)  -- | Out-Null (may be required on the end)
$command.ExecuteNonQuery()

I'm not experienced with powershell but referenced this post for a parametrised query:

Escape single quotes in PowerShell

You need to double the quotes when using single-quoted string literals:

'&$filter=(OwnerId eq 1234 and Status eq ''Ready'')'
                                         ^^     ^^

Demo:

PS > '&$filter=(OwnerId eq 1234 and Status eq ''Ready'')'
&$filter=(OwnerId eq 1234 and Status eq 'Ready')
PS >

Using backticks only works with double-quoted string literals (since they process escape sequences), but then you also need to escape all other special characters (e.g. the $ in variable names):

PS > "&`$filter=(OwnerId eq 1234 and Status eq `"Ready`")"
&$filter=(OwnerId eq 1234 and Status eq "Ready")
PS >

But in a single-quoted string literal, the ` is treated as a literal backtick.

Escape single quote character for use in an SQLite query

Try doubling up the single quotes (many databases expect it that way), so it would be :

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there''s');

Relevant quote from the documentation:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. ... A literal value can also be the token "NULL".

Formatting escaped literal quotes in Ansible for PowerShell invoke-sqlcmd command

As long as the XML does not contain any single quotes, this should work:

- name 'add config to DB'
  win_shell: |
    Invoke-Sqlcmd -username "{{db_user}}" -password "{{db_pass}}" -Query "
      INSERT INTO ConfigData (Data, ConfigId) 
      VALUES ('{{ configData.ConfigDataXML }}', '{{ item.configId }}')
    "

According to the documentation, the | introduces a multi-line shell command that removes the need for enclosing quotes and thus the need for any additional quote escaping.

---

If you expect single quotes in the XML, or just want to be on the safe side, you can use the @"here-string"@ syntax like this (PowerShell requires a line break right after the opening @" and right before the closing "@, but between the two any combination of quotes and newlines is allowed without further escaping, which is very convenient for {{placeholders}} that could contain anything):

- name 'add config to DB'
  win_shell: |
    $xmlstring = @"
    {{ configData.ConfigDataXML }}
    "@
    $xmlstring = $xmlstring -replace "'","''"

    Invoke-Sqlcmd -username "{{db_user}}" -password "{{db_pass}}" -Query "
      INSERT INTO ConfigData (Data, ConfigId) 
      VALUES ('$xmlstring', '{{ item.configId }}')
    "

Note/warning None of this protects you against maliciously crafted values of ConfigDataXML. This might not be a likely scenario for your usage context, but "concatenating SQL from strings" is still unclean.

Powershell - Single Quote in String Variable with XPath

It seems there's no escape character in XPath string literals, so you can't use your string delimiter inside a literal string as it terminates the string - i.e. this:

$name = "Don't display"

# single quotes:
# //q1:Options[q1:Name = 'Don't display']
#                            ^ terminates string literal

A quick (but naive) solution to your specific issue would be to just use double-quotes as delimiters instead:

$name = "Don't display"

# double quotes:
# //q1:Options[q1:Name = "Don't display"]
#                            ^ *doesn't* terminate string literal

but what if your data contains double quotes? Then you're back at square one..

$name = "Don""t display"

# double quotes:
# //q1:Options[q1:Name = "Don"t display"]
#                            ^ terminates string literal again

And in a pathological case, if your literal contains both single and double quotes then you can't use either as delimiters:

$name = "Don't d""isplay"

# single quotes:
# //q1:Options[q1:Name = 'Don't d"isplay']
#                            ^ terminates string literal

# double quotes:
# //q1:Options[q1:Name = "Don't d"isplay"]
#                                ^ also terminates string literal

In that case, you could resort to this answer which suggests converting your string literal into a concat expression so that you get:

$name = "Don't d""isplay"

# //q1:Options[q1:Name = concat('Don', "'", 't d"isplay')]
#                               ^^^^^ use single quotes
#                                      ^^^ use double quotes
#                                           ^^^^^^^^^^^^ use single quotes

which you could generate with this:

$name = "Don't d""isplay"

$squote = "', `"'`", '"
$expr   = "concat('{0}')" -f $name.Replace("'", $squote)

# Select-Xml -Xml $xml -XPath "//q1:Options[q1:Name = $expr]"
# ->
# //q1:Options[q1:Name = concat('Don', "'", 't d"isplay')]

and then the parts of your data that contain double-quotes are delimited with single quotes, and vice versa so they all terminate properly.

Note - you could probably optimise this for literals without one type of quote or the other and for consecutive single quotes, and it'll need some error handling added for $null and other edge cases, but it basically does the job...

---

Update

Here's a full code sample to show it in action...

$xml = [xml] "<root><child Name=`"my'name`" /></root>"

$name = "my'name"

$squote = "', `"'`", '"
$expr   = "concat('{0}')" -f $name.Replace("'", $squote)

Select-Xml -Xml $xml -XPath "//child[@Name = $expr]"

# Node  Path        Pattern
# ----  ----        -------
# child InputStream //child[@Name = concat('my', "'", 'name')]

PowerShell Param string always in single quote

What you're asking for cannot be done, if the string is to be passed as an argument, because that would require deactivating the command-line parser - the very mechanism that recognizes individual arguments, evaluates variable references and subexpressions contained in them, and binds them to parameters.

^{With a limited set of special characters you could ignore the value bound by the parser and manually parse $MyInvocation.Line, the raw command line, but not only is that ill-advised, it would break with characters such as | and ;}

However, you can achieve what you want via an interactive prompt.
While you also get such a prompt with your code if the user happens not to pass a -SqlServerPassword argument on the command line, it doesn't prevent potentially incorrect command-line use.
(Also, this automatic prompting is not user-friendly and has quirks, such as not being able to start a value with !).

If feasible, you could require users to enter the value interactively (and not also allow passing a value as an argument) using Read-Host, in which case quoting need not - and must not - be used:

param(
  # Do NOT define $SqlServerPassword as a parameter (but define others)
)

# Prompt the user for the password.
# The input is invariably treated as a literal.
Write-Host "Please enter the SQL Server password:"
[string] $SqlServerPassword = Read-Host

# Validate the user's input.
if (-not $SqlServerPassword) { Throw 'Aborted.' }
# ...

Note: Read-Host has a -Prompt parameter, which accepts a prompt string directly, but if you were to use it, you wouldn't be able to enter values that start with !; therefore, the prompt was written with a separate Write-Host statement above.

Can I use a single quote in a PowerShell 'string'?

'Escape a single quote '' using a double single quote'

See the help for the quoting rules.

You can check out the help in the powershell command line by typing:

Get-Help about_Quoting_Rules

It explains that backticks are interpreted literally in single-quoted strings.

Because the contents of single-quoted strings are interpreted literally, you cannot use the backtick character to force a literal character interpretation in a single-quoted string.

---

Related Topics