使用文本框值和通配符搜索

I want to search the empid of a name that with the characters entered in my textbox. using wildcard characters. I wrote the statement

我想搜索一个名称的empid，其中包含在我的文本框中输入的字符。使用通配符。我写了这个声明

da = new SqlDataAdapter(
 "Select empID from emp where FirstName like ' "+textbox1.text+" ' % "
   , connstring); 
da.Fill(ds);

Is this statement correct?

这个陈述是否正确？

3 个解决方案

#1

The statement you have entered would allow for spaces in front of the first name and after the first name before the wildcard search. If you want to search for any part of a first name, you should change your SQL to something like this:

您输入的语句将允许在通配符搜索之前的名字前面和第一个名称后面的空格。如果要搜索名字的任何部分，则应将SQL更改为以下内容：

SELECT empID FROM emp WHERE FirstName LIKE '@FirstName%'

Also, it's much safer to use parameretized queries like this versus just concatenating your arguments:

此外，使用像这样的参数化查询比仅连接您的参数更安全：

StringBuilder sb = new StringBuilder();
sb.Append("SELECT empID FROM emp WHERE FirstName LIKE '@FirstName%'");

SqlConnection conn = new SqlConnection(connStr);
SqlCommand command = new SqlCommand(sb.ToString());
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("FirstName", textbox1.Text);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);

If you want to use a stored procedure, you'll need to setup your SqlCommand object like so:

如果要使用存储过程，则需要设置SqlCommand对象，如下所示：

SqlCommand command = new SqlCommand("Procedure", conn);
command.CommandType = Command.StoredProcedure;

#2

You are open for sql-injection, use sql-parameters instead:

你是开放sql注入，而是使用sql-parameters：

string sql = "SELECT empID " +
              "FROM emp " + 
              "WHERE FirstName like @FirstName";
using(var con = new SqlConnection(connstring))
using (SqlCommand command = new SqlCommand(sql, con))
{
    command.Parameters.AddWithValue("@FirstName",  textbox1.text + "%");
    using(var da = new SqlDataAdapter(command))
    {
        da.Fill(ds);
    }
}

The % signs need to be part of the parameter value, and you don't need the single quotes at all when using binding parameters.

％符号需要是参数值的一部分，并且在使用绑定参数时根本不需要单引号。

#3

There are numerous things wrong with that statement.

这个陈述有很多问题。

The simple one is that you have spaces between your single quotes and the textbox value and the percent sign is outside of where it needs to be. Also, textbox1.text is misspelled. It should be closer to:

简单的一点是，您的单引号和文本框值之间有空格，百分号超出了需要的位置。此外，textbox1.text拼写错误。它应该更接近：

da = new SqlDataAdapter(
 "Select empID from emp where FirstName like '"+textbox1.Text+"%' ", connstring);

But that's just the first problem. The bigger issue is that this is a prime candidate for SQL Injection. See How do parameterized queries help against SQL injection?

但那只是第一个问题。更大的问题是这是SQL注入的主要候选者。请参阅参数化查询如何帮助SQL注入？

#1