使用 ExecuteNonQuery() 的 Powershell 脚本抛出异常“'s'附近的语法不正确". [英] Powershell Script using ExecuteNonQuery() throws exception "Incorrect syntax near 's'."

问题描述

我编写了一个非常简单的脚本，用于从文件和文件夹收集数据，并将其上传到 SQL 数据库.我相信我的问题与 参数化 sql，但我不明白如何或为什么.

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字符串以防止某些字符进入.

I think that what I need to do is reformat the sql string to prevent some characters getting in.

感谢任何帮助.

代码如下:

$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')"

这是输出(我将 sql 命令字符串推送出去作为测试):

Here is the output (I pushed the sql command string out with it as a test):

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

推荐答案

在Auran"记录中包含单引号/撇号之后尝试插入的任何数据.当您使用字符串连接来构建查询时，这是一个巨大风险，并使您面临 SQL 注入攻击.

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 huge risk and opens you up to SQL injection attacks.

将您正在构建的字符串粘贴到 SSMS 或其他一些可以突出显示 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.

您在 Coding Horror 上找到的帖子给出了正确的建议/答案 - 使用参数化查询，这就会消失.出于性能和安全原因，现在通常不鼓励 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.

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

这篇关于使用 ExecuteNonQuery() 的 Powershell 脚本抛出异常“'s'附近的语法不正确".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！