0

I am trying to write basic insert statement. I have columns with date type. In C# I get datetimepicker value and convert it to string, then try to insert it with to_date. But it shows ORA-01756: quoted string not properly terminated. I found question related to this error, but it is not the same with my case. What is wrong with my script:

"Insert Into Booklets (id, exam, number_of_booklets, who_gave, when_gave, return_date) values(booklet_seq.NEXTVAL, '" + exam + "', '" + bookletNumbers + "', '" + whoGaveId + "', '" + "to_date(" + gaveTime + ", 'DD/MM/YYYY'))"
Abra
  • 19,142
  • 7
  • 29
  • 41
This Immortal
  • 29
  • 4
  • You forgot the single quotes around `gaveTime`. – Abra Aug 09 '22 at 06:09
  • 2
    Consider using a [Prepared Statement](https://stackoverflow.com/questions/42777550/benefit-of-using-prepare-statement-in-c-sharp) – Abra Aug 09 '22 at 06:19

2 Answers2

1

As Abra mentioned, you are ending up with

values ( ...., to_date(20/01/2020,'DD/MM/YYYY'), ... )

when you need to have

values ( ...., to_date('20/01/2020','DD/MM/YYYY'), ... )

but please, please, please do not proceed with the formatting of a SQL statement in this way if this is going to be building a true application for your workplace.

Building SQL statements by concatenation is probably the number 1 way people get hacked.

Here's a video I did on this, showing that there are tools out there that can hack your application in just a few minutes the moment you head down this path

https://youtu.be/GRh800IvllY

Binding makes your SQL immune to such hacks, eg

string sql = "select department_name from departments where department_id = " +
  ":department_id";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
OracleParameter p_department_id = new OracleParameter(); 
p_department_id.OracleDbType = OracleDbType.Decimal;     
p_department_id.Value = 20;                              
cmd.Parameters.Add(p_department_id);
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
0

It is very old bug for to_date function

Quick solution: use it with seconds like:

TO_DATE(yourTime || ' 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
utrucceh
  • 1,076
  • 6
  • 11