Insert error in postgresql DB with c#

Question

I want to insert the String ' xxx'xxx ' in a field of a Table. The problem in the ' character. How i can insert this character?

Answer 1

You need to duplicate the single quote:

insert into foo (col_name)
values
('xxx''xxx');

But you should look into prepared statements which will not only make things like that a lot easier but will also protect you from SQL injection (I don't know C#, so I can't help you with the details).

Answer 2

double the single quote if you are inserting directly,

INSERT INTO tableName (colName) VALUES ('xxx''xxx')

but if you are doing it on C#, use parameterized query.

string connStr = "connection String here";
string val = "xxx'xxx";
string query = "INSERT INTO tableName (colName) VALUES (:val)";
using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
{
    using(NpgsqlCommand comm = new NpgsqlCommand())
    {
        comm.Connection = conn;
        comm.CommandText = query;
        NpgsqlParameter p = new NpgsqlParameter("val", NpgsqlDbType.Text);
        p.value = val;
        comm.Parameters.Add(p);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
        }
        catch(NpgsqlException e)
        {
            // do something with
            // e.ToString();
        }
    }
}

• PostgreSQL and C# - Working with Result Sets - Npgsql .NET Data Provider

Answer 3

In c# If you want to insert single quote you can do this by replacing original value so:

string x = "xxx'xxx";
string replacedText = x.Replace("'","''");

and when inserting to prevent from sql injection always use Parameters:

 myCommand.CommandText = "INSERT INTO TableName (x) VALUES (@x)";
 myCommand.Parameters.Add("@x", x);