Powershell script communicating with SQL, won't take in values with apostrophes

I'm trying to automate inserting values from a CSV into a table in a SQL database. Everything is getting transferred into the database just fine with exception of any names with apostrophes. I tried adding a second apostrophe next to the first one in the hope of it escaping, but no luck.

The error I'm getting looks like this:

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near 'BRIEN'.
Incorrect syntax near 'BRIEN'.
Unclosed quotation mark after the character string '
                        WHERE NPINum = (something);
                    END
            '."
At C:\REAL SCRIPTING HOURS\NPI\NPI-SQL.ps1:232 char:13
+             $SqlDataReader = $SqlCommand.ExecuteReader();
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

The section giving me issues looks like this (pardon the messiness):

$csvData = Import-Csv -Path $moveToSQL.Item($iterator)
$correctCols = $csvData | Select-Object 'NPI', 'Provider Last Name (Legal Name)', 'Provider First Name', 'Provider Middle Name', 'Provider Credential Text'
## Get it into SQL
for ($i = 0; $i -lt $correctCols.Count; $i++) {
    ## Make sure there are no issues with apostrophe'd names (I know there's likely a more visually appealing way of doing this... but alas)
    $apostropheCheck = 0

    ## Check the last name listed
    $apostropheCheck = $correctCols.Get($i).'Provider Last Name (Legal Name)'.Contains("'")    # bool to see if an apostrophe even exists
    if ($apostropheCheck -eq 'True') {
        # if it does, do something
        $apostropheCheck = $correctCols.Get($i).'Provider Last Name (Legal Name)'.IndexOf("'") # get the index of where the apostrophe is
        $correctCols.Get($i).'Provider Last Name (Legal Name)'.Insert($apostropheCheck, "'")   # add in another one for good measure (break out)
    }                                                                                          # rinse and repeat

    ## Check the first name listed
    $apostropheCheck = $correctCols.Get($i).'Provider First Name'.Contains("'")
    if ($apostropheCheck -eq 'True') {
        $apostropheCheck = $correctCols.Get($i).'Provider First Name'.IndexOf("'")
        $correctCols.Get($i).'Provider First Name'.Insert($apostropheCheck, "'")
    }

    ## Check the middle name listed
    $apostropheCheck = $correctCols.Get($i).'Provider Middle Name'.Contains("'")
    if ($apostropheCheck -eq 'True') {
        $apostropheCheck = $correctCols.Get($i).'Provider Middle Name'.IndexOf("'")
        $correctCols.Get($i).'Provider Middle Name'.Insert($apostropheCheck, "'")
    }

    ## Create query to run
    $SqlQuery = "
    IF NOT EXISTS (SELECT 1 FROM (my.dbo.table) WHERE NPINum = {0})
        BEGIN
            INSERT INTO (my.dbo.table)
                VALUES
                ('{0}'
                ,'{1}'
                ,'{2}'
                ,'{3}'
                ,'{4}')
        END
    ELSE
        IF NOT EXISTS
                (SELECT 1 FROM (my.dbo.table)
                WHERE NPINum = {0} AND LastName = '{1}'
                AND FirstName = '{2}'
                AND MiddleName = '{3}'
                AND Credentials = '{4}')
            BEGIN
                UPDATE (my.dbo.table)
                SET LastName = '{1}', FirstName = '{2}', MiddleName = '{3}', Credentials = '{4}'
                WHERE NPINum = {0};
            END
    " -f $correctCols.Get($i).NPI, $correctCols.Get($i).'Provider Last Name (Legal Name)', $correctCols.Get($i).'Provider First Name', $correctCols.Get($i).'Provider Middle Name', $correctCols.Get($i).'Provider Credential Text'
    $SqlCommand.CommandText = $SqlQuery
    ## Open up SQL communication and execute query in the database
    $SqlConnection.Open()
    $SqlDataReader = $SqlCommand.ExecuteReader()
    $SqlConnection.Close()
}

Solution

The recommendation is to avoid String.Format (-f) at all costs. Manual interpolation of values in your query string opens for SQL injection attacks.

What you should do instead is use the .AddWithValue Method from the SqlParameter Class, this way the API will handle the interpolation of values for you, avoiding this vulnerability and handling the proper escaping of punctuation marks (i.e.: handling the issue you're facing, escaping single quotes ').

This is how your code more or less should look, I'm reducing by great amount the number of parameters to be injected here to give a simplified answer. Answer also assumes that in your CSV, the columns are Foo, Bar, Baz.

$command = [System.Data.SqlClient.SqlCommand]::new()
$command.Connection = $connection # <= SQLConnection object needs to be defined beforehand
$command.CommandText = @'
IF NOT EXISTS (SELECT 1 FROM (my.dbo.table) WHERE NPINum = @foo)
    BEGIN
        INSERT INTO (my.dbo.table)
            VALUES (@foo, @bar, @baz)
    END
ELSE
    IF NOT EXISTS
        (SELECT 1 FROM (my.dbo.table)
            WHERE NPINum = @foo AND LastName = @bar
            AND FirstName = @baz)
        BEGIN
            UPDATE (my.dbo.table)
            SET LastName = @bar, FirstName = @baz
            WHERE NPINum = @foo;
        END
'@

try {
    $command.Connection.Open()

    foreach ($line in $csv) {
        try {
            $command.Parameters.Clear()
            $null = $command.Parameters.AddWithValue('@foo', $line.Foo)
            $null = $command.Parameters.AddWithValue('@bar', $line.Bar)
            $null = $command.Parameters.AddWithValue('@baz', $line.Baz)
            $reader = $command.ExecuteReader()
        }
        catch {
            # handle possible writing errors here
        }
    }
}
finally {
    if ($command.Connection) {
        $command.Connection.Dispose()
        $command.Dispose()
    }
}