CODEWITHSUNDEEP
sql-serverpowershellreplaceexecutenonqueryforeach-object
Shay Kelly
Shay Kelly

Reputation: 1

How to replace apostrophe which is causing SQL Syntax error

Expected behavior:

  1. Have a PS script that browses a file directory and grabs all the directory details (working)
  2. Write those details back to a SQL table (working)

The script is working, but fails with an error like this:

ForEach-Object : Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 'EGIDIO'"

which I know is caused by an apostrophe. The folder its reading when it fails is named "D'EGIDIO, SHAH &TOWNSEND".

The problem is I cant seem to figure out how to replace that text in the PS script, or the sql insert from the script. No matter what I try It still fails. 

#Invoke-sqlcmd Connection string parameters
$params = @{'server'='TESTServer';'Database'='_Maintenance'}
#Server to query WMI class win32_logicalDisks
$server = 'TESTServer'
#Prepare Insert Statement

$insert = @' 
    INSERT INTO [_PBS_Maintenance].[dbo].[Maint_DiskSpace](Path,SizeInMB,SizeInGB,FileLastModified,CreatedDate,CreatedBY)
    VALUES (
                '{0}',
                '{1}',
                '{2}',
                '{3}',
                GetDate(),
                SYSTEM_USER
           )
'@
 write-host $insert
Try {
    #Define connction string of target database
    $connectionString = 'Data Source=PBSNV800IMG1\PBSNV800IMG1;Initial Catalog=_PBS_Maintenance;Integrated Security=SSPI'
    # connection object initialization
    $conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    #Open the Connection 
    $conn.Open()
    # Prepare the SQL 
    $cmd = $conn.CreateCommand()

    $array= @() 
    $folder = "\\fsvr\Public\Users\Valorie L\Valorie\ATTORNEY\EIC-ERMG\" 
    $Source = $folder

Get-ChildItem -Recurse $Source | Where-Object { $_.PSIsContainer } | 
    ForEach-Object { 
            $obj = New-Object PSObject  
            $SizeMB = [Math]::Round((Get-ChildItem -Recurse $_.FullName | Measure-Object Length -Sum -ErrorAction SilentlyContinue).Sum / 1MB, 2)
            $SizeGB = [Math]::Round((Get-ChildItem -Recurse $_.FullName | Measure-Object Length -Sum -ErrorAction SilentlyContinue).Sum / 1GB, 2)  
            $obj |Add-Member -MemberType NoteProperty -Name "Path" $_.FullName
            $obj |Add-Member -MemberType NoteProperty -Name "SizeMB" $SizeMB 
            $obj |Add-Member -MemberType NoteProperty -Name "SizeGB" $SizeGB 
            $obj |Add-Member -MemberType NoteProperty -Name "DateModified" $_.LastWritetime
                    $array +=$obj 


    select Path,SizeMB,DateModified
        ForEach-Object{
            $cmd.CommandText = $insert -f $obj.Path,$obj.SizeMB,$obj.SizeGB, $obj.DateModified
            $cmd.ExecuteNonQuery()
    }
}
    #Close the connection
    $conn.Close()

}
Catch {
    Throw $_
}

Invoke-Sqlcmd @params -Query "SELECT  * FROM Maint_DiskSpace" | format-table -AutoSize

Upvotes: 0

Views: 1575

Answers (1)

codewario
codewario

Reputation: 21468

Single quotes are escaped in SQL by doubling them up (two single quotes ' ', not a double-quote "). What you'll want to do in the case of the path, on this line:

$obj |Add-Member -MemberType NoteProperty -Name "Path" $_.FullName

Modify it slightly to replace any single quotes with two single quotes like so:

$obj |Add-Member -MemberType NoteProperty -Name "Path" ( $_.FullName -replace "'", "''" )

How this works is we subexpress $_.FullName so we can use the -replace operator to replace any single quotes in the path with two single quotes. The subexpression is evaluated first, and the returned value is then applied to the parent scope as the value to the Path property on $obj.

Note that it would be prudent to make sure you are escaping all characters allowed in a folder path that would need to be escaped as part of a SQL query, as well as characters that should be escaped to prevent SQL injection attacks.

Upvotes: 1

Related Questions