Missing comma in Oracle insert

K41N Published at Java

K41N

I have to extract some data from certificates with the help of a powershell skript and then insert said data into an oracle database.

Now I am having some issues with the oracle syntax as it seems because some of the data contains commas and double quotation marks. My code (deducted) looks like this:

Add-Type -Path "C:\Program Files (x86)\oracle\instantclient_odac_12_32\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$CRTARR = Get-ChildItem Cert:\CurrentUser\My #just for testing purposes

$date = $CRTARR[0].NotAfter 

$ActiveConnection = [Oracle.ManagedDataAccess.Client.OracleConnection]::new()
$ActiveConnection .ConnectionString = "Data Source=SOURCE;User USER;Password=PASSWORD"

    $eins = $CRTARR[0].SerialNumber

    
    $zwei ="'"+ $CRTARR[0].Issuer +"'"

    $String = $CRTARR[0].Subject
    $SSTRING = @($String.Split('"'))
    $FINAL ="'"+ $SSTRING[0] + "'" + '"' + $SSTRING[1] + '"' + "'" + $SSTRING[2] + "'"


    $drei = $FINAL

    $vier = $Date.ToString("MM\/dd\/yyyy hh:mm:ss")

    $fuenf = "0"

    $sechs = $CRTARR[0].Subject -replace "[^0-9]"
    $sechs = $sechs.trim('0')

$query = "insert into TABLENAME (VALUEONE, VALUETWO, VALUETHREE, VALUEFOUR, VALUEFIVE, VALUESIX) values (" + $eins + " , " + $zwei +" , "+ $drei +" , "+ $vier +" , "+ $fuenf +" , "+ $sechs +")" 

$ActiveConnection.open()
$command = $ActiveConnection.CreateCommand()
$command.CommandType = [System.Data.CommandType]::Text
$command.CommandText = $query

$command.ExecuteNonQuery()

The query looks like this

echo $query
insert into TABLENAME (VALUEONE, VALUETWO, VALUETHREE, VALUEFOUR, VALUEFIVE, VALUESIX) values (0C87B3 , "CN=SOMECN, OU=OU WITH SPACES, O=ORGANIZATION WITH SPACES, C=DE" , "SERIALNUMBER=NUMBER, SN=
NAME, G=NAME, CN='"NAME, USER"', O=ORGANIZATION WITH SPACES, C=DE" , 07/02/2027 09:49:35 , 0 , 98915)

(I censored the code, but the format is the same.)

Oracle is throwing the following error

ORA-00917: missing comma

Now I can't find the missing comma, can anybody help?

Thanks in advance

marsze

You should always use parameters. (Why?)

$query = @"
insert into TABLENAME (VALUEONE, VALUETWO, VALUETHREE, VALUEFOUR, VALUEFIVE, VALUESIX)
values (:eins, :zwei, :drei, :vier, :fuenf, :sechs)
"@

$command.Parameters.Add("eins", $eins)
$command.Parameters.Add("zwei", $zwei)
$command.Parameters.Add("drei", $drei)
$command.Parameters.Add("vier", $vier)
$command.Parameters.Add("fuenf", $fuenf)
$command.Parameters.Add("sechs", $sechs)

This will also solve your problem with stray quotes and commas.

Collected from the Internet

Please contact [email protected] to delete if infringement.