error 42601 syntax error at or near

Question

I'm using a c# application to load a postgresql table with appropriate data. Here is the code:

NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;UserId=postgres;Password=***** ;Database=postgres;");
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = conn;
conn.Open();
try {
  command.CommandText = "insert into projets (ID, Title, Path, Description, DateCreated) values('" + pro.ID + "','" + pro.Title + "','" + pro.Path + "', '' ,'" + pro.DateCreated + "')";
  command.ExecuteNonQuery();
} catch {
  throw;
}
conn.Close();

However, when executing the code, i keep getting the same error:

error 42601 syntax error at or near...

I didnt find how to escape the apostroph.

Are you sure you're inserting into `projets` and not `projects`? — Brandon, May 14 '13 at 14:52
In addition to what @Brandon has said, i would recommend using [SqlParameters](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx) which would resolve the issue of escaping strings altogether and would help to prevent SQL Injection attacks at the same time. — DGibbs, May 14 '13 at 14:58

score 3 · Accepted Answer · edited May 23 '17 at 12:29

Try to write your command using a parametrized query

command.CommandText = "insert into projets (ID, Title, Path, Description, DateCreated) " + 
                     "values(@id, @title, @path, '', @dt);";
command.Parameters.AddWithValue("@id", pro.ID);
command.Parameters.AddWithValue("@title", pro.Title);
command.Parameters.AddWithValue("@path", pro.PAth)
command.Parameters.AddWithValue("@dt", pro.DateCreated);
command.ExecuteNonQuery();

In this way, if one of your strings values contain a single quote, you leave the job to correctly parse your values to the framework code and you avoid problems with Sql Injection

error 42601 syntax error at or near

1 Answers1