Using a PoweShell parameter value in a SQL query

This topic contains 4 replies, has 3 voices, and was last updated by  Leandro Wajswajn Pereyra 3 weeks ago.

  • Author
    Posts
  • #80698

    tmack8080
    Participant

    Hey guys,

    I am trying to run a remote query against a MSSQL database and want to use a user provided parameter value as part of the query; e.g., CmdLet asks for a document number when CmdLet executed and that value is in the WHERE clause. I was hoping there was something like the $Using feature but I have not found anything.

    Thanks in advance.

    Function Search-TestDocumentNumber {
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            [String]$SqlServer,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            [String]$Database,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            $DocNumber
        )
        BEGIN {
            [int]$ConnectionTimeout = 30
            [int]$QueryTimeout = 120
            $TSQL = @'
            SELECT * FROM myTable WHERE docnum = $DocNumber
    '@
            $SqlConnection = new-object System.Data.SqlClient.SQLConnection
            $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
            $SqlConnection.ConnectionString = $ConnectionString
            $SqlConnection.Open()
            $cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
            $cmd.CommandTimeout = $QueryTimeout
        }
        PROCESS {
            $ds = New-Object system.Data.DataSet
            $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
            [void]$da.fill($ds)
            $Results = $ds.Tables[0].Rows
        }
        END {
            $SqlConnection.Close()
            $Results | Format-Table -AutoSize
        }
    }
    
  • #80704

    Will Prather
    Participant

    I think your real variable needs to be mafe with @" ... "@ in order for the variable to be evaluated. Once you make that change, I think you'll have to put $docnumber like so, '$docnumber'.

    Let me know how that works for you.

    • #80723

      tmack8080
      Participant

      That seems to do the trick. Obviously I was thinking single quotes do not expand variable values so didn't try this. Looks to be a SQL thing.

      Thank you.

      Updated code for reference:

      Function Search-TestDocumentNumber {
              [CmdletBinding()]
              param(
                  [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
                  [String]$SqlServer,
                  [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
                  [String]$Database,
                  [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
                  $DocNumber
              )
              BEGIN {
                  [int]$ConnectionTimeout = 30
                  [int]$QueryTimeout = 120
                  $TSQL = @"
                  SELECT * FROM myTable WHERE docnum = '$DocNumber'
      "@
                  $SqlConnection = new-object System.Data.SqlClient.SQLConnection
                  $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
                  $SqlConnection.ConnectionString = $ConnectionString
                  $SqlConnection.Open()
                  $cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
                  $cmd.CommandTimeout = $QueryTimeout
              }
              PROCESS {
                  $ds = New-Object system.Data.DataSet
                  $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
                  [void]$da.fill($ds)
                  $Results = $ds.Tables[0].Rows
              }
              END {
                  $SqlConnection.Close()
                  $Results | Format-Table -AutoSize
              }
      }
      
  • #80725

    Will Prather
    Participant

    Glad that worked for you!

  • #80764

    I would recommend using TSQL params instead in order to avoid SQL injection and I think it simplifies the overall since your $TSQL would change to:

    $TSQL = "SELECT * FROM myTable WHERE docnum = @docnum"
    

    And then your SQLCommand would be formed as

    $cmd.Parameters.AddWithValue("@docnum", $Doc number)
    

    Between lines 21 and 22

You must be logged in to reply to this topic.