Add data into SQL database by PowerShell Script – ExecuteNonQuery with 0 arguments: Incorrect syntax near ‘S’

I have a PowerShell script for PST scan in the file system.

The result is in a PSObject. When I added the result into SQL database, it threw an error.

After debugging, I found that the error “ExecuteNonQuery with 0 arguments: Incorrect syntax near ‘S'” was from a directory path.

Here is the part of the code, it is string concatenation query which is not secure:

foreach ( $entry in $pstarray){

$cmd.commandtext =  "INSERT INTO data (pstdirectory,pstname,pstsize,pstowner,lastwritetime,SSOID,timestamp,pstsize2) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f $entry.directory ,$entry. name,$entry .length, $entry.owner ,$entry. lastwritetime,$entry .SSOID, $stamp,$entry .Length2

$cmd.executenonquery()
}

$pstarray is a PSObject contained Directory path, PST file name, PST Owner…

The problem is on the Directory path. The SQL query failed if there is a  special character.

For example, the directory path looked like this: \\fileServerName\OwnerName\mike‘s folder.

The ” ‘ ” character causes the problem, even I tried to escape the character.

 # Replace ` character string 
$directory2 = $entry. Directory -replace "'","`'" 

After some researches, I found this article.
The problem solved by using a parameterized query:

foreach ( $entry in $pstarray){
               
  $cmd.CommandText = "INSERT INTO data (pstdirectory,pstname,pstsize,pstowner,lastwritetime,SSOID,timestamp,pstsize2) VALUES (@pstdirectory,@pstname,@pstsize,@pstowner,@lastwritetime,@SSOID,@timestamp,@pstsize2)";
  $cmd.Parameters .clear();
  $cmd.Parameters .Add("@pstdirectory" , $entry .directory);
  $cmd.Parameters .Add("@pstname" , $entry .name);
  $cmd.Parameters .Add("@pstsize" , $entry .length);
  $cmd.Parameters .Add("@pstowner" , $entry .owner);
  $cmd.Parameters .Add("@lastwritetime" , $entry .lastwritetime);
  $cmd.Parameters .Add("@SSOID" , $entry .SSOID);
  $cmd.Parameters .Add("@timestamp" , $stamp );
  $cmd.Parameters .Add("@pstsize2" , $entry .Length2);
               
  $cmd.executenonquery()
}

Line 4 is is essential or you will see the error “The variable name ‘@pstdirectory’ has already been declared. Variable names must be unique within a query batch or stored procedure.”