Missing comma in Oracle insert

Question

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

Please look at the colouring in the output: `G=NAME, CN='"NAME, USER"', O=ORGANIZATION` - `NAME, USER` is outside the quotes (coloured blue), but it should be inside (coloured green) I think. The date and time might also need quotes, but I don't know Oracle SQL. — Ken Y-N, Oct 06 '20 at 10:09
Does this answer your question? [Escaping quotes and double quotes](https://stackoverflow.com/questions/18116186/escaping-quotes-and-double-quotes) — Ken Y-N, Oct 06 '20 at 10:13
the issue here is that the value I am trying to insert comes with double quotation marks and some guide I found via google told if I want to insert, for example the value "Dylan, Bob" with the double quotation mark, I have to do it like this '"Dyln, Bob'". This screws up the coloring in my post though — K41N, Oct 06 '20 at 10:35
I'm not sure what scenario the advice you found was describing, but bear in mind that `"` (one doublequote character) and `''` (two single-quote characters) are very different things. — William Robertson, Oct 06 '20 at 12:28

marsze · Accepted Answer · 2020-10-06T10:22:40.763

2

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.

edited Oct 06 '20 at 10:22

answered Oct 06 '20 at 10:16

marsze

15,079
5
45
61

Thank you, your answer was really helpful! – K41N Oct 06 '20 at 12:03

Missing comma in Oracle insert

1 Answers1