Inserting PowerShell values in array into SQL Server table

Question

The code below in powershell will insert into a SQL Server table like

a aa aaa | b bb bbb

in one row, but I want

  a |   b
 aa |  bb
aaa | bbb

on 3 separate rows.

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = 
    "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$stringA = 'a', 'aa', 'aaa'
$stirngB = 'b', 'bb', 'bbb'
for ($i = 0; $i -lt 3; $i++) {
    $sql = "INSERT INTO [dbo].[Server] (ServerName, UniqueID)
           VALUES ('$stringA[$i]','$stringB[$i]')"
    $Command.CommandText = $sql
    $Command.ExecuteReader()
}
$Connection.Close()

How can I manipulate my code to insert into 3 separate rows instead of 1 row?

Ansgar Wiechers · Accepted Answer · 2018-06-28 20:39:06Z

6

PowerShell does only simple variable expansion in strings. It doesn't expand complex expressions like index or property access the way you'd expect. An expression "$stringA[$i]" is expanded to the string representation of $stringA followed by an opening square bracket, the value of the variable $i, and a closing square bracket. The string representation of $stringA is all array elements joined by the $OFS character (Output Field Separator, by default a space), so you're inserting first a aa aaa[0], then a aa aaa[1], then a aa aaa[2].

There are several ways of putting individual elements of an array into a string, e.g.

string concatenation

"... VALUES ('" + $stringA[$i] + "','" + $stringB[$i] + "')"

subexpressions

"... VALUES ('$($stringA[$i])','$($stringB[$i])')"

the format operator

"... VALUES ('{0}','{1}')" -f $stringA[$i], $stringB[$i]

However, in your particular situation I do not recommend using any of the above approaches, because all of them make your code vulnerable to SQL injection.

What you want to do instead is use a prepared statement. Something like this:

$sql = 'INSERT INTO [dbo].[Server] (ServerName, UniqueID) VALUES (@foo, @bar)'
$command.CommandText = $sql
$command.Parameters.AddWithValue('@foo', $stringA[$i])
$command.Parameters.AddWithValue('@bar', $stringB[$i])
$Command.ExecuteReader()

answered Jun 28, 2018 at 20:39

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

2 Comments

Vivek Kumar Singh Over a year ago

Prepared statement is great. Didn't know about it earlier. Would definitely save a lot of fuss. Thanks for it Ansgar! :)

Suin Yun Over a year ago

Thank you Ansgar for this A++ answer!!

Collectives™ on Stack Overflow

Inserting PowerShell values in array into SQL Server table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related