c# /SQL解析错误

本文关键字：错误 SQL | 更新日期: 2023-09-27 17:50:49

我正在尝试使用c#和SQL构建注册用户脚本。但是，当我尝试将用户详细信息添加到数据库时，我遇到了解析错误。这个错误在

下面
There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = = ]
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.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = = ]
Source Error: 

Line 48:         {
Line 49:             var db = Database.Open("Database");
Line 50:             var users = db.QuerySingle("SELECT * FROM Users WHERE Username =  ", username);
Line 51:             if (users == null)
Line 52:             {
Source File: c:'Users'***'Documents'Visual Studio 2012'WebSites'CatSystem'Account'Login.cshtml    Line: 50 
Stack Trace: 

[SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = = ]]
   System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) +136
   System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() +798
   System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) +363
   System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) +59
   System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader() +12
   WebMatrix.Data.<QueryInternal>d__0.MoveNext() +152
   System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source) +164
   WebMatrix.Data.Database.QuerySingle(String commandText, Object[] args) +103
   ASP._Page_Account_Login_cshtml.Execute() in c:'Users'***'Documents'Visual Studio 2012'WebSites'CatSystem'Account'Login.cshtml:50
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197
   System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69
   System.Web.WebPages.WebPage.ExecutePageHierarchy() +151
   System.Web.WebPages.StartPage.RunPage() +17
   System.Web.WebPages.StartPage.ExecutePageHierarchy() +62
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext,     TextWriter writer, WebPageRenderingBase startPage) +76
   System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContext context) +249
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET     Version:4.0.30319.18010

我使用的脚本是

@{// Initialize page
var email = "";
var username = "";
var password = "";
var confirmPassword = "";
var firstname = "";
var lastname = "";
var housenumberorname = "";
var street = "";
var city = "";
var county = "";
var postcode = "";
var tel = "";
var mobile = "";
var dob = "";
var ErrorMessage = "";
// If this is a POST request, validate and process data
if (IsPost)
{
    email = Request.Form["email"];
    username = Request.Form["username"];
    password = Request.Form["password"];
    confirmPassword = Request.Form["confirmPassword"];
    firstname = Request.Form["firstname"];
    lastname = Request.Form["lastname"];
    housenumberorname = Request.Form["housenumberorname"];
    street = Request.Form["street"];
    city = Request.Form["city"];
    county = Request.Form["county"];
    postcode = Request.Form["postcode"];
    tel = Request.Form["tel"];
    mobile = Request.Form["mobile"];
    dob = Request.Form["dob"]; 
    if (username.IsEmpty() || password.IsEmpty()) {
        ErrorMessage = "You must specify both email and password.";
    } 
    if (password != confirmPassword) 
    {
        ErrorMessage = "Password and confirmation do not match.";
    }

    // If all information is valid, create a new account
    if (ErrorMessage=="")
    {
        var db = Database.Open("Database");
        var user = db.QuerySingle("SELECT * FROM Users WHERE Username = ", username);
        if (user == null)
        {
            db.Execute("INSERT INTO User (Username, Password, Firstname, Lastname, House, Street, City, County, Postscode, Tel, Mobile, Email, Dob) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12)", username, password, firstname, lastname, housenumberorname, street, city, county, postcode, tel, mobile, email, dob);
            WebSecurity.CreateAccount(username, password, false);
            // Navigate back to the homepage and exit
            Response.Redirect("~/");
        } 
        else 
        {
            ErrorMessage = "Email address is already in use.";
        }
    }
}
}
@if (ErrorMessage!="")
{
<p>@ErrorMessage</p> 
<p>Please correct the errors and try again.</p>
}

我假设SQL命令有问题，但由于我不熟悉MS SQL，我看不到这个问题。

c# /SQL解析错误

SQL无效。如果Username是VARCHAR或CHAR类型，则需要将值包含在'中，尽管更好的选择是使用参数化查询，因为使用字符串连接/格式化意味着应用程序对SQL注入开放。

var users = db.QuerySingle(
               string.Format("SELECT * FROM Users WHERE Username = '{0}'", 
                             username));

您应该更改代码以使用insert:

中的参数化。
var user = db.QuerySingle("SELECT * FROM Users WHERE Username = @0", username);

当前您的查询不包含username的用户名，它只是

SELECT * FROM Users WHERE Username =

语法错误

From: http://wekeroad.com/2011/01/13/someone-hit-their-head

var db = Database.Open("TDL");
var selectQueryString = "SELECT * FROM Articles WHERE slug = @0";
show =  db.QuerySingle(selectQueryString, slug);
相关文章：