Search code examples
arrayspowershelloracle-sqldeveloper

Run a sql query from PS, and loop in criteria in the where clause

I have code where I'm creating an index file by parsing out pieces of a file name.

This file name: 25643245_AjaWar_Prav_2_FT_20200701.pdf

Will create this line in the index file: 256432245|ST|W-HIGH SCHOOL TRANSCRIPT|@@TEST-LOCATION\25643245_AjaWar_Prav_2_FT_20200701.pdf

The problem is that the first parse of '256432245' isn't a primary key in our database, so I have to convert to a primary key, then store the primary key into the index file in place of '256432245'

I have part of the query that builds the index file working correctly, but not the part that queries and returns the converted ID. If I run only the part of the query that returns just one ID, that also works. I'm having a problem getting the query to work within the "foreach".

I currently get this a result:

|ST|W-HIGH SCHOOL TRANSCRIPT|@@TEST-LOCATION\25643245_AjaWar_Prav_2_FT_20200701.pdf

When I want to get:

8992004|ST|W-HIGH SCHOOL TRANSCRIPT|@@TEST-LOCATION\25643245_AjaWar_Prav_2_FT_20200701.pdf

Where '8992004' is the SPRIDEN_ID is the result of the sql query.

Thanks for any help you can provide.

foreach ($Filename in Get-ChildItem $ImagePath)
{
$Arr = $Filename -split '_'
$reworkedfilename = $Arr[0] + '_' + $Arr[1] + '_' + $Arr[2] + '_' + $Arr[3] + '_' + $Arr[4] + '_' + $Arr[5]
##$reworkedarray2 = $Arr[0] -replace ".pdf", "";
Write-host $Arr[0] ##this works because I can see the non-primary ID being returned

#Find Each SPRIDEN_ID translated from CAID
add-type -AssemblyName System.Data.OracleClient
$username = "U"
$password = "P"
$data_source = "DS"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"
$statement = "
Select Distinct SPRIDEN_ID
from SARACMT, SPRIDEN
where 
SPRIDEN_PIDM = SARACMT_PIDM
and SPRIDEN_CHANGE_IND is null
AND SARACMT_COMMENT_TEXT = '$Arr[0]'
"
##The "AND SARACMT_COMMENT_TEXT = '$Arr[0]'" doesn't work because nothing is being returned in the index file

try{
    $con = New-Object System.Data.OracleClient.OracleConnection($connection_string)

    $con.Open()
    $cmd = $con.CreateCommand()
    $cmd.CommandText = $statement
    $result = $cmd.ExecuteReader()
   
   # Do something with the results...
   $ArrConverted = while ($result.Read()) {
                    $result.GetString(0)
                    }
} catch {
    Write-Error (“Database Exception: {0}`n{1}” -f `
        $con.ConnectionString, $_.Exception.ToString())
} finally{
    if ($con.State -eq ‘Open’) { $con.close() }
}

$outputline =  $ArrConverted + '|' + $Arr[4] + '|' + $DocType + '|@@'+ $ImagePath + $reworkedfilename | out-file -filepath $IndexFilePath -Encoding "ascii" -append

#>
}
Solution

  • Your issue is to do with how you’re trying to inject variable values into your sql query string:

    $statement = "
Select Distinct SPRIDEN_ID
from SARACMT, SPRIDEN
where 
SPRIDEN_PIDM = SARACMT_PIDM
and SPRIDEN_CHANGE_IND is null
AND SARACMT_COMMENT_TEXT = '$Arr[0]'
"

    If you add a write-host $statement after this line you’ll see it’s replacing just the $Arr part and not the $Arr[0] part, so your query contains something like:

    Select Distinct SPRIDEN_ID
from SARACMT, SPRIDEN
where 
SPRIDEN_PIDM = SARACMT_PIDM
and SPRIDEN_CHANGE_IND is null
AND SARACMT_COMMENT_TEXT = '25643245 AjaWar Prav 2 FT 20200701[0]'

    instead of:

    Select Distinct SPRIDEN_ID
from SARACMT, SPRIDEN
where 
SPRIDEN_PIDM = SARACMT_PIDM
and SPRIDEN_CHANGE_IND is null
AND SARACMT_COMMENT_TEXT = '25643245’

    To get it to replace the value of $Arr[0] you can surround it with $( ... ) to use “command substitution” (see https://learn.microsoft.com/en-us/powershell/scripting/learn/deep-dives/everything-about-string-substitutions?view=powershell-7#command-substitution) so your query becomes:

    $statement = "
Select Distinct SPRIDEN_ID
from SARACMT, SPRIDEN
where 
SPRIDEN_PIDM = SARACMT_PIDM
and SPRIDEN_CHANGE_IND is null
AND SARACMT_COMMENT_TEXT = '$($Arr[0])’

    Having said that, you’d be much better off using a parameterised query rather than building a dynamic sql string (see Oracle Parameterized query in c#) because as your code stands it’s vulnerable to a sql injection attack if you come across a deliberately mischievous file name (e.g. ‘ or 1='1_AjaWar_Prav_2_FT_20200701.pdf).

    See https://blogs.oracle.com/sql/what-is-sql-injection-and-how-to-stop-it for more about sql injection attacks.