库目录搜索sql语句
本文关键字:sql 语句 搜索 | 更新日期: 2023-09-27 18:21:32
我希望用户为他的书选择搜索条件这里是代码,请提供建议!!
String keyword=Textbox1.Text; //User types keyword
String userCriteria=Textbox2.Text;// Can be Title, Author, Subject or ISBN;
String sql="Select * from tableBooks WHERE '"+keyword+"' like '%"+userCriteria+"'%";
如何让用户选择自己的搜索数据库的标准?
您当然需要一种更好的方法来构建查询。如果没有一定的检查或筛选措施,就不会直接从用户那里获取输入并将其放入查询中。这将使您的应用程序暴露于sql注入。使用SQL参数。请尝试将此链接作为参考:http://www.dotnetperls.com/sqlparameter
示例:
using (SqlCommand command = new SqlCommand("Select * from tableBooks WHERE @Field LIKE @Value", connection))
{
//
// Add new SqlParameter to the command.
//
command.Parameters.Add(new SqlParameter("Field", Textbox1.Text)); // I do not recommend using a textbox and letting the user write anything. You have to limit his choices by the fields in your table. Use a dropdownlist and limit his choices by meaningful fields in your "tableBooks" table.
command.Parameters.Add(new SqlParameter("Value", Textbox2.Text));
//
// Read in the SELECT results.
//
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//GET YOUR BOOK
}
}
请注意我的评论:
//我不建议使用文本框并让用户将任何内容写为"关键字"。你必须根据表中的列来限制他的选择。使用下拉列表,并通过从"tableBooks"表中进行有意义的选择来限制他的选择。
使用参数化查询比您已经使用的表单更安全你可以试试这个,我想它会帮助
// Declare a connection
conn = new
SqlConnection("Server=.;DataBase=DataBase;Integrated Security=SSPI");
conn.Open();
//Create parameterized query
SqlCommand cmd = new SqlCommand(
"Select * from tableBooks WHERE (case @userCriteria when 'Title' then Title when 'Author' then Author when 'Subject' then Subject when 'ISBN' then ISBN else '' end) LIKE '%'+@keyword+ '%'", conn);
//Create parameter userCriteria
SqlParameter param = new SqlParameter();
param.ParameterName = "@userCriteria";
param.Value = userCriteria;
//Create parameter keyword
SqlParameter param = new SqlParameter();
param.ParameterName = "@keyword";
param.Value = userCriteria;
// add new parameter to command object
cmd.Parameters.Add(param);
// get data stream
reader = cmd.ExecuteReader();
// write each record
while(reader.Read())
{
//Get data
}