我如何使用像运营商,在一个SQLite查询参数? [英] How can I use the Like Operator with a Parameter in a SQLite query?
问题描述
我可以通过在LINQPad中输入这个结果来获得我想要的结果:
I can get the result I expect by entering this in LINQPad:
SELECT * FROM WorkTable WHERE WTName LIKE "DSD__20090410014953000%"
(它显示了WTName值为DSD__20090410014953000.xml的记录)
(it shows me the record which has a WTName value of DSD__20090410014953000.xml")
但是试图这样做是编程方式正在尝试。我尝试:
But trying to do this programmatically is proving trying. I tried:
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());
}
...但是会导致应用程序崩溃,日志文件告诉我为什么:
...but it causes the app to crash, and my log file tells me why:
Message: From application-wide exception handler: System.Data.SQLite.SQLiteException: SQL logic error or missing database
near "%": syntax error
因此,也许它认为查询参数被命名为wtName%而不是wtName;但是用空格分隔参数和无论什么操作符(%)不起作用。
So maybe it thinks the query parameter is named "wtName%" instead of "wtName"; but separating the parameter and the "whatever" opertor ("%") with a space doesn't work, either.
我可以通过嵌入查询参数到字符串中,如下所示:
I could go retro/kludgy by just embedding the query parameter into the string like so:
const string qry = String.Format("SELECT SiteNum FROM WorkTable WHERE WTName LIKE {0}%", tableName);
...并且没有查询参数,但恐怕如果我这样做< a href =https://haveibeenpwned.com/ =nofollow> Troy Hunt 会出现在我的房子,在栏杆关于SQL注入时,我躺在床上。
...and doing without the query parameter altogether, but I'm afraid if I did that Troy Hunt would show up at my house and flail me with a bedrail while railing about SQL Injection.
如何获得我的数据并同时写入安全代码?
How can I get my data and simultaneously write safe code?
推荐答案
通配符%
应添加到参数值,而不是参数名称
The wildcard %
should be added to the parameter value, not to the parameter name
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)
And, I am not sure if it matters here, but, usually, I insert the parameter name with the exact name used in the placeholder ( @wtName )
这篇关于我如何使用像运营商,在一个SQLite查询参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!