CODEWITHSUNDEEP
c#sqlsql-server-ce
Ian Richards
Ian Richards

Reputation: 1618

C#/SQL Parse Error

I am trying to build a register user script using C# and SQL. However when wver I try to add the users details to the database I run into a parse error. This error is below

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

The script I am using is 

@{// 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>
}

I assume there is something wrong with the SQL command but as I am unfamiliar with MS SQL I can not see the issue. Any help with this would be appreciated.

Upvotes: 0

Views: 1987

Answers (2)

Mike Wade
Mike Wade

Reputation: 1746

You should change your code to use the parametrization as you have in your insert:

var user = db.QuerySingle("SELECT * FROM Users WHERE Username = @0", username);

Currently your query does not contain the username from username it is just 

SELECT * FROM Users WHERE Username =

Which is invalid sytax.

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);

Upvotes: 0

Oded
Oded

Reputation: 499302

The SQL is not valid. If Username is a VARCHAR or CHAR type, you need to enclose the value in ', though a better option is to use a parameterized query, as using string concatenation/formatting means your application is open to SQL Injection .

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

Upvotes: 1

Related Questions