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

Solution

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

Comments

[Matt Osborne]

is $Command.CommandText still meant to be in that code block ?

[alroc]

Absent any other information about the script, yes. If you're doing this in a loop, you'll want to move it outside the loop, and instead of using Parameters.Add() here, you'll add empty parameters outside the loop and set their values inside it.

[Matt Osborne]

That makes a lot of sense, thanks for the assistance. When I use the code as you have provided, I get the following exception. "The variable name '@ name' has already been declared." I checked, and there are no other instances of @name and changing it to @ fname produces the same result. Any further assistance appreciated.

[Matt Osborne]

Caught it. needed to put $Command.CommandText after the parameters. Works great now. Thank you for all the help.

[Nathan McKaskle]

This doesn't work at all, I get more errors saying I can't do the @variables. "The splatting operator '@' cannot be used to reference variables in an expression. '@dt' can be used only as an argument to a command.

[alroc]

@NathanMcKaskle you should post a new question, not tack a comment onto a 4 year old answer. There is no variable splatting in the code posted in this answer, so posting your code as its own question is required to give you any assistance.

[duct_tape_coder]

@alroc Could you post an example of the loop you mentioned to Matt?

[Allen]

Only laziness will make me use concatenation now - which, of course, I succumb to. The performance difference is negligible from using parameterized sql, and the other benefit is it often allows the execution engine to use the saved version with the new parameter values, saving parsing time.

[Allen]

The @parametername convention is a leftover from OLEDB, is it not? Just use a plain old name with out garnishes.