Insert command with comma

Question

I am inserting a row in Oracle. But the problem is that I want to insert comma in the companyname column, which fails due to insert statement.

Wonder is it possible to add comma or apostrophy using following insert command?

string id=1;
string orgnr = "123123";
comanyname = "Test,company";

string sql = 
  string.Format("INSERT INTO VENDORS(ID, ORGNR, COMPANYNAME) " +
  "VALUES({0}, '{1}', '{2}')", id, orgnr, companyname);

update = new OracleCommand(sql, connection);
update.Connection.Open();
update.ExecuteNonQuery();
update.Connection.Close();

Also learn about prepared Statements: https://stackoverflow.com/questions/11070434/using-prepared-statement-in-c-sharp-with-mysql — Jens, Nov 30 '18 at 14:47
@Jens: Well, more parameterized queries than prepared statements. But yes, avoiding putting the values directly in the SQL is the way forward here. — Jon Skeet, Nov 30 '18 at 14:48
Also learn about [SQL Injection](https://www.w3schools.com/sql/sql_injection.asp). — LukStorms, Nov 30 '18 at 14:49
Here's how to prevent apostrophies from breaking Oracle (commas should be fine) https://stackoverflow.com/questions/11315340/pl-sql-how-to-escape-single-quote-in-a-string — Robin Bennett, Nov 30 '18 at 14:56
@Jens - there are legitimate reasons for having a comma in a company name that aren't an attempt to store CSV, Many companies have one before 'Inc.' or whatever. — Robin Bennett, Nov 30 '18 at 15:05
@RobinBennett Where is a comma in *Inc.*? Also the example Looks like a list of names and not a Name containing a comma — Jens, Nov 30 '18 at 15:06
Perhaps its failing because you didn't declare comanyname as string or because comanyname != companyname (Typos?) either way your code is subject to SQL Injection attacks. You should used parameterized SQL instead of formatted strings. — Sentinel, Nov 30 '18 at 22:06

score 2 · Accepted Answer · edited Nov 30 '18 at 22:03

It is failing because you have declared 'id' as string and you have been trying to insert it without single quote. Also there are illegal characters to escape in the string parameters. Would you like to get rid of these? Please continue to read...

There is a bigger issue in your code where you haven't used parameterized queries. This piece of code turning host application into a SQL Injection playground.

Please have a look at the following code:

int id=1;
string orgnr = "123123";
string companyName = "Test,company";

string connectionString = "Data Source= oraDB;User Id=;Password=;";
OracleConnection connection = new OracleConnection(connectionString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;

cmd.CommandText = "INSERT INTO VENDORS(ID, ORGNR, COMPANYNAME) VALUES (:1, :2, :3)";

cmd.Parameters.Add(new OracleParameter("1",
                                       OracleDbType.Int32,
                                       id,
                                       ParameterDirection.Input));

cmd.Parameters.Add(new OracleParameter("2",
                                       OracleDbType.Varchar2,
                                       orgnr,
                                       ParameterDirection.Input));

cmd.Parameters.Add(new OracleParameter("3",
                                       OracleDbType.Varchar2,
                                       companyName,
                                       ParameterDirection.Input));

int rowsUpdated = cmd.ExecuteNonQuery();
connection.Dispose();

Insert command with comma

1 Answers1