How to prevent SQL injection in login page when SQL Server Compact Edition is used as a database

Question

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

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