Powershell Script using ExecuteNonQuery () throws an "Invalid syntax near" exception. "

Question

I wrote a very simple script that collects data from files and folders and loads it into an SQL database. I believe that my problem is related to the parameterized sql problem, but I do not understand how and why.

I think I need to do a sql string reformatting to prevent some characters from getting into.

Any help was 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 result (I pushed the sql command line 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

No matter what data you try to insert after recording "Auran", it has one quote / apostrophe. When you use string concatenation to create a query, this is a huge risk and opens up SQL injection attacks.

Paste the line you create into SSMS or some other tool that can give you SQL syntax highlighting and you will see it.

The message you found in Coding Horror gives the right advice / answer - use a parameterized query and that goes away. String concatenation for SQL statements is usually discouraged these days for performance and security reasons. Not to mention that reading is much easier 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();