作者:葉の鋼琴曲 | 来源:互联网 | 2023-05-25 14:15
通过在LINQPad中输入,我可以得到我期望的结果:
SELECT * FROM WorkTable WHERE WTName LIKE "DSD__20090410014953000%"
(它显示了WTName值为DSD__20090410014953000.xml的记录")
但尝试以编程方式执行此操作正在尝试.我试过了:
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%";
using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
con.Open();
SQLiteCommand cmd = new SQLiteCommand(qry, con);
cmd.Parameters.Add(new SQLiteParameter("wtName", tableName));
siteNum = Convert.ToInt32(cmd.ExecuteScalar());
}
...但它会导致应用程序崩溃,我的日志文件告诉我原因:
Message: From application-wide exception handler: System.Data.SQLite.SQLiteException: SQL logic error or missing database
near "%": syntax error
所以也许它认为查询参数被命名为"wtName%"而不是"wtName"; 但是将参数和"what"opertor("%")与空格分开也不起作用.
我可以通过将查询参数嵌入到字符串中来实现retro/kludgy,如下所示:
const string qry = String.Format("SELECT SiteNum FROM WorkTable WHERE WTName LIKE {0}%", tableName);
...和做没有查询参数完全,但我怕如果我这样做,特洛伊·亨特将在我的房子出现了,与床栏枷我而责骂SQL注入.
如何获取数据并同时编写安全代码?
1> Steve..:
%
应将通配符添加到参数值,而不是参数名称
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName";
using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
con.Open();
SQLiteCommand cmd = new SQLiteCommand(qry, con);
cmd.Parameters.Add(new SQLiteParameter("@wtName", tableName + "%"));
siteNum = Convert.ToInt32(cmd.ExecuteScalar());
}
而且,我不确定这里是否重要,但通常我会插入参数名称以及占位符中使用的确切名称(@wtName)
2> Kas..:
最简单的方法是使用“ ||”
使用 :
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName || '%' ";
代替:
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%";