How to remove space from an array (powershell)

Question

I have a script that reads TAB seperated .TXT file and grabs information from a table and then it creates a .SQL script based off of the names in the list. But every time a $Variable[#] is called it adds an extra space. This space does not exist in the source data. I am looking for a method of trimming it.

$start | Out-File -filepath $target1 -append

$infile = $source1
$reader = [System.IO.File]::OpenText($infile)
$writer = New-Object System.IO.StreamWriter $file1;

$counter = 1
try {
    while (($line = $reader.ReadLine()) -ne $null)
    {
        $myarray=$line -split "\t" | foreach {$_.Trim()}
If ($myarray[1] -eq "") {$myarray[1]=”~”}
If ($myarray[2] -eq "") {$myarray[2]=”~”}
If ($myarray[3] -eq "") {$myarray[3]=”~”}
If ($myarray[4] -eq "") {$myarray[4]=”~”}
If ($myarray[5] -eq "") {$myarray[5]=”~”}
        if ($myarray[0] -Match "\d{1,4}\.\d{1,3}"){
"go
Insert into #mytable Select convert(varchar(60),replace('OSFI Name: "+$myarray[1],$myarray[2],$myarray[3],$myarray[4],$myarray[5],"')), no_,branch,name,surname,midname,usual,bname2 
from cust where cust.surname in ('"+$myarray[2].,"',"+$myarray[1],"',"+$myarray[3],"',"+$myarray[4],"',"+$myarray[5],"')'  and ( name in ('"+$myarray[1],"','"+$myarray[2],"','"+$myarray[3],"','"+$myarray[4],"','"+$myarray[5],"') or
midname in ('"+$myarray[1],"','"+$myarray[2],"','"+$myarray[3],"','"+$myarray[4],"','"+$myarray[5],"') or
usualy in ('"+$myarray[1],"','"+$myarray[2],"','"+$myarray[3],"','"+$myarray[4],"','"+$myarray[5],"') or
bname2 in ('"+$myarray[1],"','"+$myarray[2],"','"+$myarray[3],"','"+$myarray[4],"','"+$myarray[5],"') )
" -join "," | foreach {$_.Trim()} | Out-File -filepath $target1 -append


        }


            #$writer.WriteLine($original);

            #Write-Output  $original;
            #Write-Output  $newlin
    }
}
finally {
    $reader.Close()
    $writer.Close()
}

$end | Out-File -filepath $target1 -append

Every time it calls $myarray[1] or any other number it adds a space. This is not good as this will create a duplicate entry for every name it pulls in my DB.

We have an existing ".Java" script that does what I am trying to achieve so I know what my output should look like.

The output I should be getting looks like:

go

Insert into #mytable Select convert(varchar(60),replace('OSFI Name: Fake Name Faker unreal   ','''''','''')), no_,branch,name,surname,midname,usual,bname2 
from cust where cust.surname in ('Faker unreal','Fake Name','~','~','~') and ( name in ('Fake Name', 'Faker unreal', '~', '~', '~') or 
midname in ('Fake Name', 'Faker unreal', '~', '~', '~') or 
usual in ('Fake Name', 'Faker unreal', '~', '~', '~') or 
bname2 in ('Fake Name', 'Faker unreal', '~', '~', '~') )

But instead I am getting

go
Insert into #mytable Select convert(varchar(60),replace('OSFI Name: Fake Name Fake Faker unreal ~ ~ ')), no_,branch,name,surname,midname,usual,bname2 
from cust where cust.surname in ('Fake Name ',unreal ',~ ',~ ')'  and ( name in ('Fake Name ','Fake Faker ','unreal ','~ ','~ ') or
midname in ('Fake Name ','Fake Faker ','unreal ','~ ','~ ') or
usualy in ('Fake Name ','Fake Faker ','unreal ','~ ','~ ') or
bname2 in ('Fake Name ','Fake Faker ','unreal ','~ ','~ ') )

Can you attach some sample input that matches your sample output — Matt
– Matt, Commented May 10, 2018 at 13:51
Oh and the standard spiel about how this is vulnerable to sql injection if the output is blindly executed. — Matt
– Matt, Commented May 10, 2018 at 13:52
@AussieJoe In case of string padding in the source file for the individual elements would be a guess. Doing it before would not solve that. — Matt
– Matt, Commented May 10, 2018 at 16:16

Matt · Accepted Answer · 2018-05-10 16:31:55Z

2

The use of commas in your string building are contributing to this issue. I see that you are using a -join at the end of that string as well. This could explain why you were using commas as -join is an array operator. In essence you are muddying the waters in how you are building your string by combining that with basic string concatenation. A simple example that shows the issue (minor refactor of your code to accomplish the goal)

'"+$myarray[2].,"',"+$myarray[1],"',"+$myarray[3],"'

Fix those and your issue should go away (see section below about better approaches). That comma makes PowerShell take the next string as part of an array as supposed to a string concat. When arrays are flattened to strings in PowerShell the elements are space delimited. The issue is not a space in the array element but how you are building your string.

Compare these results to see what I mean

$myarray = 65..90 | %{[string]([char]$_) * 4}

"'"+$myarray[2],"'"
"'"+$myarray[2]+"'"

'CCCC '
'CCCC'

In the first output example the 2 element array $myarray[2],"'" is being added to the string "'". In the second the quote then the array element are being added to the string and then another quote. Pure string concatenation.

Consider better string building approaches

Know that there are other ways to do this as well. You can use subexpressions and the format operator if that helps.

"'$($myarray[2])','$($myarray[3])'"
"'{0}','{1}'" -f $myarray[2],$myarray[3]

SQL Injection Warning

You are manually building, with string concatenation, sql code. This means that an attacked could put something malicious in your input file and you could very well execute this. You need to be using command parameterization. Look up SQL injection as this is out of scope of the question.

edited May 10, 2018 at 16:31

answered May 10, 2018 at 13:58

Matt

47k9 gold badges90 silver badges125 bronze badges

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

3 Comments

Cleadus Fetus Over a year ago

Hmm when I changed From "'"+$myarray[2],"'" To "'"+$myarray[2]+"'" it does not generate any error but it does not generate any output either.. Unfortunately I cannot provide the input file as it contains internal information.

Matt Over a year ago

@CleadusFetus If you created scrubbed output I presume you can make scrubbed input. Although in this case I think the issue is clear without it. Can you update the question with the fully updated string. I notice now that you have a join at the end. I didnt notice that at first. I am still right but the implementation suggestion is wrong. I would like to see updated code.

AussieJoe Over a year ago

It's quite impossible to troubleshoot your code without knowing what $infile value is.

Collectives™ on Stack Overflow

How to remove space from an array (powershell)

1 Answer 1

Consider better string building approaches

SQL Injection Warning

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Consider better string building approaches

SQL Injection Warning

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related