0

The following is one of my old codes. It uses SQL CE as a database. I want to prevent sql injection in this code. For preventing sql injection, I need to parameterize sql query. I don't know how to use those parameter in the code. How can I revise it?

SqlCeConnection con = new SqlCeConnection();
sqlcon.ConnectionString = @"Data Source=dbLogin.sdf;
string query = "select * from [Login] where username = @user AND password = @pass";
SqlCeCommand myCMD = new SqlCeCommand();
myCMD.Connection = sqlcon;
myCMD.CommandText = query;
myCMD.Parameters.Add("@user", Username.Text);
myCMD.Parameters.Add("@pass", Password.Text);

SqlCeDataAdapter sda = new SqlCeDataAdapter(query, con);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dtbl.Rows.Count == 1)
{
    Main objMain = new Main();
    this.Hide();
    objMain.Show();
}
else
{
    MessageBox.Show("Invalid Username or Password", "Access Denied", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
  • You need to tag the programming language you are using. Then let us know what about the abundance of documentation and articles out there you didn't understand on the subject; parametrisation is a very well documented subject. – Thom A Apr 09 '22 at 07:23
  • I added my programming language. It is C#. –  Apr 09 '22 at 07:23
  • 1
    Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) – Thom A Apr 09 '22 at 07:24
  • @Larnu I updated my post. I tried to parameterize it. How can I fill datatable using the new parameterized query? –  Apr 09 '22 at 07:33
  • 2
    Why are you using `AddWithValue`? The question I linked to shows `Parameters.Add`. [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) though you have completely changed the question now: your title isn't reflective of your new goal posts. – Thom A Apr 09 '22 at 07:37
  • For your new question, again, what didn't you understand about the information already [out there](https://www.google.com/search?q=put%20sql%20results%20in%20datatable%20C%23)? – Thom A Apr 09 '22 at 07:42
  • @Larnu I changed it to `Add()`. When I debug, it cannot fill the datatable and gives error on line `sda.Fill(dtbl);` and says: `'A parameter is missing. [ Parameter ordinal = 2 ]' ` –  Apr 09 '22 at 07:43
  • Also, as a side note, your code implies you are storing plaintext passwords: **never** do this. Salt and hash your passwords when you stores them. – Thom A Apr 09 '22 at 07:43
  • You are not using the SqlCeCommand that you built, `SqlCeDataAdapter sda = new SqlCeDataAdapter(query, sqlcon);` - there is no reference to `myCommand`, hence parameters are not resolving in your query. – Anand Sowmithiran Apr 09 '22 at 07:50
  • 1
    change to `SqlCeDataAdapter sda = new SqlCeDataAdapter(myCommand);` – Anand Sowmithiran Apr 09 '22 at 07:52

1 Answers1

1

You have to pass the command that has the parameters defined and filled to the SqlCeDataAdapter, you have missed to do that.

change your code to below,

SqlCeConnection sqlcon = new SqlCeConnection();
sqlcon.ConnectionString = @"Data Source=dbLogin.sdf; password =rss900";
string query = "select * from [tblLogin] where username = @user AND password = @pass";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = sqlcon;
myCommand.CommandText = query;
myCommand.Parameters.Add("@user", txtUsername.Text);
myCommand.Parameters.Add("@pass", txtPassword.Text);

SqlCeDataAdapter sda = new SqlCeDataAdapter(myCommand);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
if (dtbl.Rows.Count == 1)
{
    Main objfrmMain = new Main();
    this.Hide();
    objfrmMain.Show();
}
else
{
    MessageBox.Show("Invalid Username or Password", "Access Denied", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
Anand Sowmithiran
  • 2,591
  • 2
  • 10
  • 22