Sql error in asp.net c#

Question

If you please help me out i have an error in my code that i can not understand it.

the error is:

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 'Login'.

and my code:

 public static void ChangePassword(string login, string password)
    {
        var sqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string query = @"update Organizer set Password ="+ password + "where Login=" + login + "";
        SqlCommand cmd = new SqlCommand(query, sqlCon);
        cmd.CommandType = CommandType.Text;
        try
        {
            sqlCon.Open();
            cmd.ExecuteNonQuery();
            sqlCon.Close();
        }
        catch (Exception ee) { throw ee; }
    }

Remus Rusanu · Accepted Answer · 2011-09-07 01:01:51Z

4

We've seen enough sql injection attacks, we don't need another one, please fix your code and use parameters.
Use using blocks to avoid leaking connections.
Install an exception handler like ELMAH.

Don't save passwords in the database

using (var sqlCon = new SqlConnection(...))
{
    string query = @"update Organizer set Password =@password where Login=@login";
    SqlCommand cmd = new SqlCommand(query, sqlCon);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add("@password", SqlDbType.VarChar, 8000);
    cmd.Parameters["@password"].Value = password;  
    cmd.Parameters.Add("@login", SqlDbType.VarChar, 8000);
    cmd.Parameters["@login"].Value = login;  

    sqlCon.Open();
    cmd.ExecuteNonQuery();
    sqlCon.Close();

}

answered Sep 7, 2011 at 1:01

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pure.Krome Over a year ago

+111111 to -all- the points Remus has said. PLEASE for the sake of humanity, heed his words!

EKS · Accepted Answer · 2011-09-07 00:54:31Z

4

Try

string query = @"update Organizer set Password ='"+ password + "' where Login= '" + login + "'";

You are missing the ' around string, that being said you are likely very open to sql injection attacks ( Im guessing because of the code, and lack of a clearing function).

Also make sure your not storing passwords in plain text :)

The ' is used like " in sql.

answered Sep 7, 2011 at 0:54

EKS

5,6317 gold badges48 silver badges62 bronze badges

3 Comments

Justin Niessner Over a year ago

@emilios - This may work, but leads to dangerous code. You should check out parameterized queries to remove the security hole.

EKS Over a year ago

@Justin Niessner is correct, you either need to check yourself to security holes or use the built in systems.

Craig Stuntz Over a year ago

Parameterized queries and unencrypted passwords are only part of the problem. He's rolling his own authentication system. That won't end well.

Justin Niessner · Accepted Answer · 2011-09-07 01:23:30Z

3

If you were going to use the code above, your issue is that you're not wrapping the new password or login in single quotes:

 string query = 
     @"update Organizer set Password = '" + 
       password + 
       "' where Login= '" + login + "'";

But I wouldn't use that code at all. It's quite dangerous since it allows people to pass in arbitrary SQL. I would use parameterized queries instead:

var query = "update organizer set password = @password where login = @login";
var command = new SqlCommand(query, sqlCon);

command.Parameters.Add("@password", SqlDbType.VarChar, 100, password);
command.Parameters.Add("@login", SqlDbType.VarChar, 100, login);

edited Sep 7, 2011 at 1:23

answered Sep 7, 2011 at 0:55

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

2 Comments

Remus Rusanu Over a year ago

Never use command.Parameters.Add with a string. It will add the parameter as NVARCHAR and the rules of data type precedence will force the WHERE clause to ignore any index on the filtered column. Always use explicit SqlDbType and force the appropriate type. A second issue is that this pattern adds parameters of the exact length of the string and this causes unnecessary cache polution because SQL plans cannot be reused across different input types, and parameters with different lengths mean different types. Always use an explicit length.

Justin Niessner Over a year ago

@Remus - You learn something new every day. Thanks for the insight. Updated.

Chains · Accepted Answer · 2011-09-07 00:57:11Z

0

You need single quotes...

set Password = ' /*<---*/ "+ password + "' /*<---*/  where Login=' /*<---*/ " + login + "' /*<---*/ "

answered Sep 7, 2011 at 0:57

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Collectives™ on Stack Overflow

Sql error in asp.net c#

4 Answers 4

1 Comment

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related