In an existing codebase there is hardcoded SQL and I want to avoid SQL injection.
The below code uses SqlCommand together with SqlParameters. The query does not return any data. However, when I remove the parameters the query returns the correct results.
How can I use SqlParameters with a SELECT statement?
string atUsername = "@username"; //does not work
//string atUsername = "Demo1"; //THIS WORKS
string atPassword = "@password"; //does not work
//string atPassword = "222"; //THIS WORKS
string sql = @"SELECT userId, userName, password, status, roleId, vendorId
FROM users
WHERE username = '" + atUsername + "' AND password = '" + atPassword + "'";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add(atUsername, SqlDbType.NVarChar, 20);
cmd.Parameters[atUsername].Value = "Demo1";
//cmd.Parameters.AddWithValue //also does not work
cmd.Parameters.Add(atPassword, SqlDbType.NVarChar, 20);
cmd.Parameters[atPassword].Value = "222";
//cmd.Parameters.AddWithValue //also does not work
SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
Console.WriteLine(dt.Rows != null);
if (dt.Rows != null)
{
Console.WriteLine(dt.Rows.Count);
}
conn.Close();
conn.Dispose();
I have also unsuccessfully tried alternatives using
- SqlCommand.ExecuteReader and SqlDataReader
- IDisposable pattern
Replace
cmd.Parameters.Add(atUsernamewithSqlParameter pUsername = new SqlParameter(); pUsername.ParameterName = atUsername; pUsername.Value = "Demo1"; cmd.Parameters.Add(pUsername);"
PS. I've heard of EntityFramework but I cannot use EF in this case (long story).
