Powershell script error with apostrophes

Question

Post reply

Powershell script error with apostrophes

abdalah.mehdoini

Ten Centuries

Points: 1005

April 13, 2020 at 10:35 am

#3742364

Hello Everyone!

I am working on a powershell script to centralize the history of all my sql jobs in a single table during insertion of the message column I have a problem with the apostrophe (see the print screen) Who can help me please

<#
  Mahdouani Abdallah @2020
#>

Import-Module "SQLPS" -DisableNameChecking
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"

cd c:

$CollectServer = "DESKTOP-95KKMMH"
$collectDatabase = "msdb"

$serverList ="DESKTOP-95KKMMH","DESKTOP-95KKMMH\production"

foreach($svr in $serverList)
{
    $servername = $svr
    
    $sqlRec ="select @@servername as servername 
                     ,job.name
                     ,run_time
                     ,STUFF(
STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
9,
0,
':'
) 'duration'
,message

    FROM sysjobhistory history
INNER JOIN sysjobs job
ON job.job_id = history.job_id
LEFT OUTER JOIN [dbo].[sysjobsteps] step
ON step.job_id = job.job_id
AND step.step_id = job.start_step_id
WHERE history.step_id = 0
AND CAST(dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 200
 

" 
    $result = Invoke-Sqlcmd -ErrorAction 'Stop' -ServerInstance $servername -query $sqlRec -database $collectDatabase
    foreach($line in $result){
                                      $insertReq =[String]::format(
                                      "insert into msdb.dbo.History_job
                                              (ServerName
                                              ,name
                                              ,run_time
                                              ,duration
                                              ,messsage)
                                               values
                                           ('{0}','{1}','{2}','{3}','{4}')"
                                      
                                      ,@{$true="NULL";$false=($line.ServerName).ToString()}[$line.ServerName -eq [System.DBNull]::Value]
                                      ,@{$true="NULL";$false=($line.name).ToString()}[$line.name -eq [System.DBNull]::Value]
                                      ,@{$true="NULL";$false=($line.run_time).ToString()}[$line.run_time -eq [System.DBNull]::Value]
                                      ,@{$true="NULL";$false=($line.duration).ToString()}[$line.duration -eq [System.DBNull]::Value]
                                      ,@{$true="NULL";$false=($line.message).ToString() }[$line.message -eq [System.DBNull]::Value]  
                                 
                                  )
            $insertReq = $insertReq.Replace("'NULL'","NULL")
            Invoke-Sqlcmd -ErrorAction 'Stop' -ServerInstance $CollectServer -Database $collectDatabase -query $insertReq
            }
        }

Sans titre

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16135 More actions · Answer 1

you can do it 2 ways

1 - use parameters instead of string replacements - this is the best option- google and you will find some code with it for powershell

2 - for each variable you are passing to the format function replace ' by ''

alevyinroc SSC Rookie Points: 30 More actions · Answer 2

frederico_fonseca is correct, you should be using parameterized queries. Your current method is prone to SQL injection problems and you need to always account for those. Simply escaping strings (replacing ' with '') isn't really sufficient.

However, there's a much easier way to go about this using the dbatools module (https://dbatools/io). This module has functions which can extract the job history and then write it out to a table for you, including any "special character handling." It also has Invoke-DbaQuery which replaces Invoke-SqlCmd and can handle parameterized queries.

Get-DbaAgentJobHistory -SqlInstance $serverList | select-object sqlinstance, job, rundate, runduration,message | Write-DbaDataTable -SqlInstance $CollectServer -database $CollectDatabase -Table History_job -schema dbo -AutoCreateTable

You can insert some additional filtering in the pipeline where necessary, or set an "oldest date" via the -StartDate parameter for Get-DbaAgentJobHistory. It looks like you're restricting the history to anything newer than a particular point in time, but it's unclear what's happening when you subtract an integer from a datetime (>= GETDATE() - 200) - please use dateadd()!

If you can't use dbatools, please switch to the sqlserver module. It superseded sqlps 4 years ago.