-2

I fill a data table with a SQL query and I get this error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near '='.

Here is my code:

SqlConnection sqlConnection = new SqlConnection("Data Source=OWNER;Initial Catalog=train-database;Integrated Security=True;Pooling=False");
SqlDataAdapter sda = new SqlDataAdapter("SELECT c_id FROM [user] WHERE E_mail = '"+ f3.E_Mail3.Text +"' and password = "+f3.Password3.Text+" ", sqlConnection);

DataTable dt = new DataTable();
sda.Fill(dt);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ibrahim Ramadan
  • 25
  • 3
  • 3
    Parametrise your SQL, then this won't be a problem. – Thom A May 01 '19 at 15:56
  • Please see https://stackoverflow.com/q/332365/11683 before you continue. – GSerg May 01 '19 at 15:56
  • 1
    Never ever concat strings like that to create SQL queries - always us SQL Parameters. Also, a DataTable and DataAdapter is overkill to select what appears to be a single id. – Ňɏssa Pøngjǣrdenlarp May 01 '19 at 15:57
  • 1
    Before you write another line of code you need to read about, understand and start using parameters. Your code is wide open to sql injection. http://bobby-tables.com/ Using parameters here would have eliminated this bug and protected your database. Also, storing password as clear text is borderline criminal behavior. They should be salted and hashed. – Sean Lange May 01 '19 at 15:57
  • Specifically your issue seems to be that you didn't put in single quotes around the password (I doubt it's any non-text datatype), but the correct fix is indeed to parametrize. – Fabo.sk May 01 '19 at 16:08
  • 1
    Also, I just realised, you have `password = "+f3.Password3.Text+"` this means you are storing plain text passwords in your database. This is another huge security flaw. You should be salting and hashing your passwords here. Along with the injection problem, this just means that anyone could find out the password for **every single user**. If this is real data, and you're within the EU, that is something you never want to happen – Thom A May 01 '19 at 16:13
  • @Larnu, if this is real data, that is something you never want to happen, full stop. – Brian May 01 '19 at 19:48

1 Answers1

0

The password value needs to be in a single quotation as well like the username part

SqlConnection sqlConnection = new SqlConnection("Data Source=OWNER;Initial Catalog=train-database;Integrated Security=True;Pooling=False");
    SqlDataAdapter sda = new SqlDataAdapter("select c_id From [user] Where E_mail = '"+ f3.E_Mail3.Text +"' and password = '"+f3.Password3.Text+"' ", sqlConnection); 
    DataTable dt = new DataTable(); 
    sda.Fill(dt);

But this code is vulnerable to SQL injection attack and it is really a bad practice to create SQL queries using string concatenation.

It is highly recommended to use SQL parameters.

The following code is not full working code because I am not near my ide but I am sure you can fix it.

SqlConnection sqlConnection = new SqlConnection("Data Source=OWNER;Initial Catalog=train-database;Integrated Security=True;Pooling=False");

SqlCommand command= new SqlCommand("select c_id from [users] where e_mail =@emailparam and password =@passwordparam",sqlConnection);
command.Parameters.Add(new SqlParameter("emailparam",f3.Email.text));
command.Parameters.Add(new SqlParameter("passwordparam", f3.password.text));

SqlDataAdapter sda = new SqlDataAdapter(command); 
DataTable dt = new DataTable(); sda.Fill(dt);
Khatibzadeh
  • 460
  • 3
  • 10