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

One of your items has a single quote (i.e. an apostrophe) in the name. — Roger Lipscombe
– Roger Lipscombe, Commented May 24, 2013 at 11:41
Well that was quick. Roger was correct, there was an extra apostrophe occasionally. I couldn't see the error due to my output being upside down. I used replace to resolve the issue. $x = $x.Replace("'", "") Are there any other characters I should worry about? Or any better methods to clean up my strings? For now this works. — Matt Osborne
– Matt Osborne, Commented May 24, 2013 at 12:39
Character replacement will only get you so far - but there will always be some weird edge case it misses. Use a paramterized query as the link you found explains and my answer demonstrates. — alroc
– alroc, Commented May 24, 2013 at 12:45
Yep. I'll just give you a file named '; DROP TABLE FileSizeTable --'... — Roger Lipscombe
– Roger Lipscombe, Commented May 24, 2013 at 13:29

alroc · Accepted Answer · 2013-05-24 12:40:41Z

10

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

answered May 24, 2013 at 12:40

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Matt Osborne Over a year ago

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

alroc Over a year ago

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 Over a year ago

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 Over a year ago

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

Nathan McKaskle Over a year ago

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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related