Powershell Script using ExecuteNonQuery() throws exception "Incorrect syntax near 's'."

Question

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.

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

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

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

Answer 1

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.

Paste the string you're constructing into SSMS or some other tool that can give you SQL syntax highlighting and you'll see it.

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