如何解决此错误 - ''附近的语法不正确。在C＃中 [英] How to I solve this error - incorrect syntax near ' '. In C#

问题描述

Incorrect syntax near ' '.
Incorrect syntax near ' '.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ' '.
Incorrect syntax near ' '.

Source Error:
     
Line 24:         SqlDataAdapter sda = new SqlDataAdapter(query, conn);
Line 25: 
Line 26:         sda.Fill(dt);
Line 27:         conn.Close();
Line 28:

我尝试过：



源代码：

What I have tried:

Source Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class Controls_PeopleYouMayKnow : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        PeopleYouMayKnow();
    }
    private void PeopleYouMayKnow()
    {
        DataTable dt = new DataTable();
        string query = "Select * from Userregistration R where RegisterId not in ((select F.FriendId as RegisterId  from Friends f where f.MyId =" + Session["UserId"] + "and f.Status=1) Union  (select F.MyId as RegisterId  from Friends f where f.FriendId =" + Session["UserId"] + "and f.Status=1))and R.RegisterId !=" + Session["UserId"] ;
        // "Select * from Userregistration R where RegisterId not in ((select F.FriendId as RegisterId  from Friends f where f.MyId = '" + Session["UserId"] + "' and f.Status=1) Union  (select F.MyId as RegisterId  from Friends f where f.FriendId = '" + Session["UserId"] + "' and f.Status=1))and R.RegisterId != '" + Session["UserId"]+"'"
       // string query = "Select * from UserRegistration";
        SqlConnection conn = new SqlConnection(@"Data Source=ABHISHEK-PC\ABHISHEK;Initial Catalog=FrirndsZoneDB;Integrated Security=True");
        conn.Open();
        SqlDataAdapter sda = new SqlDataAdapter(query, conn);

        sda.Fill(dt);
        conn.Close();
        
        // dt = Database.GetData(query);
        if (dt.Rows.Count > 0)
        {
             
            ProfileDataList.Visible = true;
            ProfileDataList.DataSource = dt;
            ProfileDataList.DataBind();
        }
        else
        {
 
        }
    }
    protected void ImagePeopleYouMayknow_Click(object sender, ImageClickEventArgs e)
    {
        Session["CurrentProfileId"] = (((ImageButton)sender).CommandArgument).ToString();
        Response.Redirect("Main.aspx");
    }
}

推荐答案

您的SQL查询受SQL注入攻击。同样的问题使得它无法帮助你，因为你的实际查询取决于用于构建它的变量的值。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]



首先解决注入问题。

Your SQL query is subject to SQL injection attack. This same problem makes it impossible to help you because your actual query depend on the values of variables used to build it.
SQL injection - Wikipedia[^]
SQL Injection[^]

First fix the injection problem.

你的问题字符串很糟糕。您正在使用字符串连接来构建它。这种构建查询字符串的方法不仅可以打开SQL注入攻击（谷歌它），它还会使您的代码更难以解决问题。查看查询字符串2秒后出现的一个问题是，您不能很好地管理空格字符。你有一个参数被添加到字符串中，但参数值和下一个SQL关键字之间没有空格，比如和......。



说真的，Google for C＃参数化查询以找出如何正确执行此操作并使其更容易调试。

Your question string is bad. You're using string concatenation to build it. Not only does this method of building a query string open you up to SQL Injection Attacks (Google it) it also makes your code much more difficult to troubleshot. One problem that sticks out after looking at your query string for 2 seconds is that you're not managing space characters very well. You have a parameter being added to the string but no space between the parameter value and the next SQL keyword, like "and ...".

Seriously, Google for "C# parameterized queries" to find out how to do this properly and make it far easier to debug.

正如已经指出的，一个问题是缺少参数。这使得您可以打开SQL注入，但当数据库尝试将文本数据隐式转换为相应的数据类型时，也会引入潜在的转换问题。



另一件事是你不要处置对象，以免资源被释放。解决这个问题的最好方法是使用块。



第三件事是你没有任何错误处理。一些潜在的例外是编程错误，但有些可能因数据库中的情况而发生，例如考虑重复数据。您应该抓住这些并正确通知用户。



总体而言，我建议您浏览正确执行数据库操作 [ ^ ]

As already pointed out, one problem is the lack of parameters. This leaves you open to SQL injections but also introduces potential conversion problems when the database tries to convert the text data implicitly to the corresponding data type.

Another thing is that you don't dispose the objects so the resources won't be freed as they should. The best way to tackle this is to use using blocks.

Third thing is that you don't have any error handling. Some of the potential exceptions are programming errors but some may occur because of the situation in the database, consider for example duplicate data. You should catch these and inform the user properly.

In overall I would recommend going through Properly executing database operations[^]

这篇关于如何解决此错误 - ''附近的语法不正确。在C＃中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！