CODEWITHSUNDEEP
c#asp.netsql
user3733078
user3733078

Reputation: 249

Confused about how to check user exist or not

I have a table with 4 columns named "ID","name","username","pass".what i am trying to do is that After the user enter username and password .XmlhttpRequest object returns a message something like user exist or not .But I am getting this exception at this line 

SqlDataReader dr = cmd.ExecuteReader();

exception message says 

Incorrect syntax near 'hatca'.(thats the username)

Unclosed quotation mark after the character string ''.

something wrong at this line

 SqlCommand cmd = new SqlCommand("select * from userstable where username='" + username + "'AND pass='" + pass + "'",con);

everything looks fine to me .What am i doing wrong here

this is the all code aspx.cs

 protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["ka"] != null && Request.QueryString["pass"] != null)
        {

            string username = Request.QueryString["ka"];
            string pass = Request.QueryString["pass"];
            SqlConnection con = new SqlConnection("server=.\\sqlexpress;database=Projects;UID=sa;Password=1234");
            con.Open();
           SqlCommand cmd = new SqlCommand("select * from userstable where username='" + username + "'AND pass='" + pass + "'",con);
            //SqlCommand cmd = new SqlCommand("select * from userstable where username=@name and pass=@pass",con);
            //cmd.Parameters.AddWithValue("@name"



            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {

                Response.Write("exist");
            }
            else {

                Response.Write("Doesntexist");
            }
            con.Close();


        }
        else {
            Response.Write("enter values");

        }

        Response.Close();

    }

and html page

    <script src="Scripts/jquery-2.1.1.js"></script>
   <script type="text/javascript">


       function createXHR() {

           var xhr;


           try {
               xhr = new XMLHttpRequest();
           } catch (e) {

           }
           return xhr;
       }


       function signIn() {

           var xhr = createXHR();
           var username=$('#txtusername').val();
           var pass=$('#txtpass').val()

           xhr.onreadystatechange=function(){

               if(xhr!=null){

                   if(xhr.readyState==4){

                       if(xhr.status>=200 && xhr.status<300){

                           var v = $('#result').html(xhr.responseText);
                           if (v == "exist") {
                               $('#result').html("Done");
                           }
                           else if (v == "Doesntexist") {

                               $('#result').html("Error");
                           }
                           else{
                               $('#result').html(v);

                           }

                           }

                   }

               }
               else{
                   $('#result').html("Error");


               }
           }

           xhr.open("GET", "Default.aspx?ka='" + username + "&pass=" + pass, true);
           xhr.send(null);
       }
   </script>

Upvotes: 0

Views: 74

Answers (3)

Jeremy Cook
Jeremy Cook

Reputation: 22063

Besides the fact that you are open to SQL injection attack, this line with the single-quote touching the AND is most likely the source of the error.

username='" + username + "'AND

Would work if:

username='" + username + "' AND

Definitely look into SQL injection and how to avoid it with parameterized queries!

Upvotes: 1

Alleo Indong
Alleo Indong

Reputation: 327

your full query output is select * from userstable where username='hatca'AND pass='pass'" notice that the ' and AND have no space? They should be separeted by space so the SQL parser will know what command you are executing.

Also what you are doing is a bad practice when it comes to querying and authentication. Search more about SQL Parameterized Queries or if you are using asp.net MVC try to look for EF(Entity Framework) it would become easier to communicate with the database.

What you are doing right now is UNSECURE

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156928

I guess there is a ' in the username since that is the only reason you would get this kind of message.

To circumvent this, and for many other reasons, you should use parameters:

SqlCommand cmd = new SqlCommand("select * from userstable where username=@username AND pass=@pass",con);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@pass", pass);

Upvotes: 1

Related Questions