是否需要在使用String.Format()的DML语句中显式指定单引号
本文关键字:语句 DML 单引号 Format String 是否 | 更新日期: 2023-09-27 18:22:11
我想重构一些代码,如下所示:
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 + "')";
到此:
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);
这种方法是否足够,或者我必须将格式值用单引号括起来?
更新
我刚刚注意到这个评论,我添加了"很久以前"关于这个代码:
// 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)
我建议使用参数。
这个。。。
- 将您从是否使用单引号的问题中解放出来
- 保护您免受SQL注入攻击等
看看这篇文章,例如:第06课:向命令添加参数这将描述你如何做到这一点。
使用参数化查询需要三个步骤:
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.
在您的情况下,您的代码可能如下所示。
步骤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);
步骤2:
对所有参数重复此操作。
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@id";
paramId.Value = 12345;
步骤3:
对所有参数重复此操作。
cmd.Parameters.Add(paramId);
如果所有值都是数字类型,如int
、decimal
、bigint
等,则不需要将它们用单引号括起来。这个代码有效(我刚刚测试过)
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);
您的代码中缺少开始和结束大括号。
如果单引号是文本(char、nvarchar、varchar、nchar),则需要添加它们,例如数字就不需要。测试:)
不过,我建议您使用sql参数:)