How to avoid SQL Injection with Update command in Npgsql?

Question

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

Avoid SQL injection, and
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

See their documentation under "Prepared statements". Manually composing query strings is extremely error prone, so avoid inserting data using string concatenation whenever possible. Also try and bump your Postgres version, 9.4 adds a lot of new features that are worth having. — tadman
– tadman, Commented Jun 19, 2015 at 17:51
@tadman I still don't get how to work with the single quote when it is part of the record, or does it not matter? Thanks. — Alan Wayne
– Alan Wayne, Commented Jun 19, 2015 at 17:57
Being concerned about quotes is a sign you're not escaping properly. The best way to do this is prepared statements where the database driver handles escaping for you. — tadman
– tadman, Commented Jun 19, 2015 at 18:03

Shay Rojansky · Accepted Answer · 2015-06-19 19:15:40Z

8

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();
}

answered Jun 19, 2015 at 19:15

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to avoid SQL Injection with Update command in Npgsql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related