在 PowerShell 中转义包含单引号的字符串准备进行 SQL 查询 [英] Escaping strings containing single quotes in PowerShell ready for SQL query

问题描述

我正在尝试运行以下查询，该查询获取某人的姓名并尝试将其插入到 SQL Server 数据库表中.

I am trying to run the following query, which takes someone's name and attempts to insert it into an SQL Server database table.

$name = "Ronnie O'Sullivan"

$dataSource = "127.0.0.1"
$database = "Danny"
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

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

$command = $connection.CreateCommand()
$command.CommandText = $query
$command.ExecuteNonQuery()

$connection.Close()

我面临的问题是单引号导致我的查询出现问题.查询被执行为

The problem I am facing is that the single quote is causing an issue in my query. The query is being executed as

INSERT INTO People(name) VALUES('Ronnie O'Sullivan')

导致 SQL 语法错误.

which causes an SQL syntax error.

我的问题是如何转义 $name 变量以便它在 SQL 端呈现.

My question is how do I escape my $name variable so that it renders on the SQL side.

一种解决方案是对我的 $name 变量进行查找和替换， find: ' replace: ''

One solution is to do a find and replace on my $name variable, find: ' replace: ''

$name.Replace("'", "''")

是否有更优雅的解决方案，或者我似乎找不到的功能?

Is there a more elegant solution out there, or a function that I can't seem to find?

谢谢.

推荐答案

您可以尝试更新您的代码以使用参数化值来处理字符串中的引号:

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()

我对 powershell 没有经验，但参考了这篇文章 参数化查询:

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

这篇关于在 PowerShell 中转义包含单引号的字符串准备进行 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！