Question

我正在撰写一个问询,以便用户能够了解他们在 as、C#和msql中提供的关键词:

 string projectPart = null;
 string categoryPart = null;
 string descriptionPart = null;

 if (this.Textbox_ProjectNr.Text.Trim().Length > 0)
   projectPart = " AND Number= " + this.Textbox_ProjectNr.Text.Trim() + "  ";

 if (this.Textbox_Category.Text.Trim().Length > 0)
   categoryPart = " AND Category LIKE  %" + this.Textbox_Category.Text.Trim() + "%  ";

 if (this.Textbox_pDescription.Text.Trim().Length > 0)
    descriptionPart = " AND ProductDescription LIKE  %" + this.Textbox_pDescription.Text.Trim() + "%  ";

 string query = "SELECT * from Project  = p.ID " + projectPart + descriptionPart + categoryPart;

我很想知道,这一询问是否足以进行传统查询。由于我看到这一搜索存在一些瓶颈:

if the user does not type anything, it returns all of the data => For this I only do the query when one of the fields are filled.
if the user provides some keywords "P" for each field, the result will be millions of data.

我知道如何从根本上改进查询。欢迎任何建议。

先进。

Answer 1

最重要的改进是保护你免受卡片剂攻击。

你们不应混淆KQ阵列中的原始投入。如有人搜查以下案文:

Bwah ha ha ; DROP DATABASE northwind; PRINT

将在您的问候中加上这一点。

SELECT *
FROM mytable
WHERE category LIKE  %Bwah ha ha ; DROP DATABASE northwind; PRINT %

这是一个有效的指挥机构,它会预示着执行和放弃你的数据库(或者说攻击者想要做任何事情)。

更多信息见:http://en.wikiedia.org/wiki/SQL_inapp” al = “noproduction”

Answer 2

你们必须证明这种怀疑。不要让用户进入价值,而是使用参数,如:

SqlCommand cmd = new SqlCommand(@"
    SELECT * from Project 
    WHERE 
    ( Number = @Number OR @Number IS NULL ) AND
    ( Category LIKE @Category OR @Category IS NULL ) AND
    ( ProductDescription LIKE @ProductDescription OR @ProductDescription IS NULL )", conn);
if(!String.IsNullOrEmpty(this.Textbox_ProjectNr.Text.Trim()))
   cmd.Parameters.AddWithValue("@Number", this.Textbox_ProjectNr.Text.Trim());
if(!String.IsNullOrEmpty(this.Textbox_Category.Text.Trim()))
   cmd.Parameters.AddWithValue("@Category", this.Textbox_Category.Text.Trim());
if(!String.IsNullOrEmpty(this.Textbox_pDescription.Text.Trim()))
   cmd.Parameters.AddWithValue("@ProductDescription", this.Textbox_pDescription.Text.Trim());

此外,你还可以在用户输入数值方面添加一些客户验证。例如,在进行询问之前,要求提供三个以上(吗)的果园。

<asp:TextBox ID="Textbox_ProjectNr" runat="server" />
<asp:RegularExpressionValidator ID="Textbox_ProjectNr_Validator" runat="server" 
   ControlToValidate="Textbox_ProjectNr"
   ErrorMessage="Minimum length is 3"
   ValidationExpression=".{3,}" />

Answer 3

First of all, you must protect yourself from sql injections. You haven t specified what connection to the database you are using but most libraries allow adding the parameters in a different field, so they are sanitized automatically. Secondly, you can (and should) limit the results count using the "LIMIT" (for mysql) or "TOP X" Like so: Select * from TableName LIMIT 100 or Select TOP 100 * from TableName

友情链接