Is it necessary to explicitly specify single quotes in a DML statement that uses String.Format()?

Question

I want to refactor some code like this:

dSQL = "INSERT INTO inventory ( id, pksize, Description, supplier_id, UnitCost, UnitList," +
      " Qty, UPC, dept, subdept, upc_pack_size, supplier_item, bqu_id)" +
      " VALUES" + "('" + id +"'" + ", " + 
      pksize + ",'" + desc +"'" + 
      ",'" + supplierID +"'" + ", " + cost + ", " 
      + list + ", " + qty + 
      ",'" + UPC +"'" + ", " + dept + ", " + 
      subdept + ", " + UPCpkSize + 
      ",'" + supplierItem +"','" + redemption + "')";

...to this:

dSQL = string.Format(
      "INSERT INTO inventory ( id, pksize, Description, supplier_id, UnitCost, UnitList," +
      " Qty, UPC, dept, subdept, upc_pack_size, supplier_item, bqu_id)" +
      " VALUES {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}",
      id, pksize, desc, supplierID, cost, list, qty, UPC, dept, subdept, UPCpkSize, supplierItem, redemption);

Will this methodology suffice, or must I enclose the format values in single quotes?

UPDATE

I just noticed this comment I added "way back when" regarding this code:

// This works as a string.Format() assignment without param "?"s or single quotes because dSQL is not executed, it is simply passed to DBCommand for conditional display (if there is an exception)

Why would it be any different? Both methods produce a string. — H H, Jul 12 '13 at 15:46
Also using a `Verbatim string literal` (`@` sign) will help readability. — Dave Zych, Jul 12 '13 at 15:49

score 2 · Answer 1 · answered Jul 12 '13 at 16:05

I suggest using parameters.

This...

frees you from the question whether or not to use single quotes.
secures you from SQL injection attacks and the like.

Have a look at this article, for example: Lesson 06: Adding Parameters to Commands This will describe you how to do it.

Using parameterized queries is a three step process:

Construct the SqlCommand command string with parameters.
Declare a SqlParameter object, assigning values as appropriate.
Assign the SqlParameter object to the SqlCommand object's Parameters property.

In you case, your code might then look like the following.

Step 1:

SqlCommand cmd = new SqlCommand(
                 "INSERT INTO inventory ( id, pksize, Description, supplier_id, UnitCost, UnitList, Qty, UPC, dept, subdept, upc_pack_size, supplier_item, bqu_id) " +
                 "VALUES" + "(@id, @pksize, [ ... AND YOU OTHER PARAMETERS ... ])";", conn);

Step 2:

Repeat this for all your parameters.

SqlParameter paramId  = new SqlParameter();
    paramId.ParameterName = "@id";
    paramId.Value         = 12345;

Step 3:

Repeat this for all your parameters.

cmd.Parameters.Add(paramId);

Rwiti · Accepted Answer · 2013-07-12T15:50:57.283

If all of the values are of numerical types such as int, decimal, bigint, etc. then you don't need to enclose them in single quotes. This code works (I just tested it)

dSQL = string.Format(
  "INSERT INTO inventory ( id, pksize, Description, supplier_id, UnitCost, UnitList," +
  " Qty, UPC, dept, subdept, upc_pack_size, supplier_item, bqu_id)" +
  " VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})",
  id, pksize, desc, supplierID, cost, list, qty, UPC, dept, subdept, UPCpkSize, supplierItem,    
redemption);

You were missing the starting and closing braces in your code.

score 1 · Answer 3 · answered Jul 12 '13 at 15:45

1

You need to add the single quotes if they are text(char,nvarchar,varchar,nchar), numbers don't for example. Test it :)

I advise you use sql parameters though :)

answered Jul 12 '13 at 15:45

sm_

2,572
2
17
34

Is it necessary to explicitly specify single quotes in a DML statement that uses String.Format()?

UPDATE

3 Answers3