How to avoid SQL Injection with Update command in Npgsql?

Question

I am trying to use the Npgsql PostgreSQL client to accomplish two things:

1. Avoid SQL injection, and
2. Manage data containing the single quote '

I cannot see how to do either :(

PostrgeSQL version 9.1

In the below code, dx.chronic is of type bool? and cdesc of table dx may contain single quote, as "Tom's dog". Clearly, UpdateCmd, as written, will fail when Npgsql/PostgreSQL hits the single quote.

string sChronic = (dx.chronic == null) ? "null" : dx.chronic.ToString(); 

string UpdateCmd = "update dx "+
            "set chronic = " + sChronic  +
            " where (trim(lower(cdesc)), trim(cicd9)) = "+
            " ('"+dx.description.Trim().ToLower()+"','"+dx.icd9.Trim() +"');";

 using (NpgsqlCommand command = new NpgsqlCommand(UpdateCmd, conn))
            {
               command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Text));

               command.Parameters[0].Value = "Big Tom's Dog";

             ....... ? ? ? ? ? ? ? ? ? ? ? ? ? ...................

How is this done? Any help is most appreciated.

TIA

Answer 1

As @tadman says, you should never use string concatenation to compose your query - that is the source of SQL injection. However, there's no need to prepare your statement. Use parameter placeholders in your query, something like the following should work:

string UpdateCmd = "update dx set chronic = @p1 where (trim(lower(cdesc)), trim(cicd9)) = (@p2);";

using (NpgsqlCommand command = new NpgsqlCommand(UpdateCmd, conn))
{
    cmd.Parameters.AddWithValue("p1", "chronic");
    cmd.Parameters.AddWithValue("p2", "value");
    cmd.ExecuteNonQuery();
}