Sql喜欢查询不检索数据 [英] Sql like query not retrieving data

问题描述

我在VB.NET中搜索了一个datagridview选项，用户选择一个类别并在文本框中输入内容。单击搜索按钮时，将执行以下代码。问题是数据网格视图中没有数据。这有什么问题？

 Private Sub btnSearchCustomers_Click（sender As Object，e As EventArgs）处理btnSearchCustomers.Click 
 Dim searchTable As New DataTable 
 Dim SearchItem As String = txtCustomerSearch.Text 
 Select Case ComboBoxCustomersSearchBy.SelectedItem 
 CaseName
 SqlQuery =SELECT * FROM tblCustomers WHERE CustomerName LIKE'％& SearchItem& &'
 CustomerTable = db.getData（SqlQuery）
 DataGridViewCustomers.DataSource = CustomerTable 
 End Select 
 End Sub 
 

我尝试了什么：



我试过将代码更改为一个等于而不是像所以它只找到完全匹配，这似乎工作。我希望Like工作代替。

解决方案

&符应该是百分号...

  LIKE  ' ％& SearchItem&&'    

将

 LIKE'％& SearchItem& ％'

但是，您不应该使用字符串连接来创建SQL查询。学习使用sql参数

引用：

它出了什么问题？

sql查询有2个问题，第一个是在解决方案1中发现，第二个是sql注入。

 SqlQuery =   SELECT * FROM tblCustomers WHERE CustomerName LIKE'％& SearchItem&  &' 

不是你的问题的解决方案，但你有另一个问题。

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

I have a search by option for a datagridview in VB.NET where the user selects a category and enters something in a text box. When the search button is clicked, the code below is executed. The problem is that there is no data in the data gridview. What is wrong with it?

Private Sub btnSearchCustomers_Click(sender As Object, e As EventArgs) Handles btnSearchCustomers.Click
      Dim searchTable As New DataTable
      Dim SearchItem As String = txtCustomerSearch.Text
      Select Case ComboBoxCustomersSearchBy.SelectedItem
          Case "Name"
              SqlQuery = "SELECT * FROM tblCustomers WHERE CustomerName LIKE '%" & SearchItem & "&'"
              CustomerTable = db.getData(SqlQuery)
              DataGridViewCustomers.DataSource = CustomerTable
      End Select
  End Sub

What I have tried:

I have tried changing the code to an equal to instead of like so it only finds exact matches and this seems to work. I want the Like one to work instead though.

解决方案

That ampersand should be a percent sign...

LIKE '%" & SearchItem & "&'"

would be

LIKE '%" & SearchItem & "%'"

However you should not use string concatenation to create sql queries. Learn to use sql parameters

Quote:

What is wrong with it?

The sql query have 2 problems, the first is spotted in solution 1, second is sql injection.

SqlQuery = "SELECT * FROM tblCustomers WHERE CustomerName LIKE '%" & SearchItem & "&'"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

这篇关于Sql喜欢查询不检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！