How can I fix this error in my C# and T-SQL code [duplicate]

Question

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

Please see stackoverflow.com/q/332365/11683 before you continue. — GSerg
– GSerg, Commented May 1, 2019 at 15:56
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
– Ňɏssa Pøngjǣrdenlarp, Commented May 1, 2019 at 15:57
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. 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
– Sean Lange, Commented May 1, 2019 at 15:57
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
– Thom A ♦, Commented May 1, 2019 at 16:13

Khatibzadeh · Accepted Answer · 2019-05-01 16:11:36Z

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

Collectives™ on Stack Overflow

How can I fix this error in my C# and T-SQL code [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related