I can get the result I expect by entering this in LINQPad:
SELECT * FROM WorkTable WHERE WTName LIKE "DSD__20090410014953000%"
(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
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);
...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?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…