SQL 使用 ExecuteNonQuery() 的 Powershell 脚本抛出异常“'s' 附近的语法不正确。”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16734039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Powershell Script using ExecuteNonQuery() throws exception "Incorrect syntax near 's'."
提问by Matt Osborne
I have written a very simple script that gathers data from files and folder, and uploads it to an SQL Database. I believe my problem is related to the issue of parameterized sql, but I don't understand how or why.
我编写了一个非常简单的脚本，用于从文件和文件夹中收集数据，并将其上传到 SQL 数据库。我相信我的问题与参数化 sql的问题有关，但我不明白如何或为什么。
I think that what I need to do is reformat the sql string to prevent some characters getting in.
我认为我需要做的是重新格式化 sql 字符串以防止某些字符进入。
Any help appreciated.
任何帮助表示赞赏。
Here is the code:
这是代码：
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbConnection
$Command.CommandText = "INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES ('$i','$items','$temp','$currentDate')"
$Command.ExecuteNonQuery()
"INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES ('$i','$items','$temp','$currentDate')"
Here is the output (I pushed the sql command string out with it as a test):
这是输出（我将 sql 命令字符串作为测试推出）：
INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES ('ATI Te
chnologies','61.16 MB','39','05/24/2013 21:05:56')
ATI Technologies 61.16 MB 39
1
INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES ('ATIToo
l','0.00 MB','30','05/24/2013 21:05:56')
ATITool 0.00 MB 30
1
INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES ('Auran'
,'7,496.04 MB','28','05/24/2013 21:05:56')
Auran 7,496.04 MB 28
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near
's'.
Unclosed quotation mark after the character string ')'."
At line:143 char:25
+ $Command.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
回答by alroc
Whatever data you're attempting to insert after the "Auran" record has a single quote/apostrophe in it. When you use string concatenation to construct your query, this is a hugerisk and opens you up to SQL injection attacks.
无论您尝试在“Auran”记录之后插入什么数据，其中都有一个单引号/撇号。当您使用字符串连接来构建您的查询时，这是一个巨大的风险，并使您容易受到 SQL 注入攻击。
Paste the string you're constructing into SSMS or some other tool that can give you SQL syntax highlighting and you'll see it.
将您正在构建的字符串粘贴到 SSMS 或其他一些可以突出显示 SQL 语法的工具中，您就会看到它。
The post you found on Coding Horror gives the correct advice/answer - use a parameterized query and this goes away. String concatenation for SQL statements is generally discouraged these days for performance and security reasons. Not to mention being much easier to read as source code.
您在 Coding Horror 上找到的帖子给出了正确的建议/答案 - 使用参数化查询，这就会消失。出于性能和安全原因，现在通常不鼓励 SQL 语句的字符串连接。更不用说作为源代码更容易阅读了。
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbConnection
$Command.CommandText = "INSERT INTO FileSizeTable (FileName,FileSize,FileNameLength,Date) VALUES (@name,@size,@length,@dt)";
$Command.Parameters.Add("@name", $i);
$Command.Parameters.Add("@size", $items);
$Command.Parameters.Add("@length", $temp);
$Command.Parameters.Add("@dt", $currentdate);
$Command.ExecuteNonQuery();
