I have some queries (to a MS Access database) like this:
string comando = "SELECT * FROM ANAGRAFICA WHERE E_MAIL='" + user + "' AND PASSWORD_AZIENDA='" + password + "'";
and I'd like to "escape" user and password, preventing an injection.
How can I do it with C# and .NET 3.5? I'm searching for something like mysql_escape_string in PHP.
You need to use parameters. Well dont have to but would be preferable.
SqlParameter[] myparm = new SqlParameter[2];
myparm[0] = new SqlParameter("@User",user);
myparm[1] = new SqlParameter("@Pass",password);
string comando = "SELECT * FROM ANAGRAFICA WHERE E_MAIL=@User AND PASSWORD_AZIENDA=@Pass";
Don't escape the strings to start with - use a parameterized query. Benefits of this over escaping:
The docs for SqlCommand.Parameters give a good, complete example.
You should use the SQL paramters to prevent SQL Injection look at the code
//
// The name we are trying to match.
//
string dogName = "Fido";
//
// Use preset string for connection and open it.
//
string connectionString = ConsoleApplication716.Properties.Settings.Default.ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
//
// Description of SQL command:
// 1. It selects all cells from rows matching the name.
// 2. It uses LIKE operator because Name is a Text field.
// 3. @Name must be added as a new SqlParameter.
//
using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
{
//
// Add new SqlParameter to the command.
//
command.Parameters.Add(new SqlParameter("Name", dogName));
//
// Read in the SELECT results.
//
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int weight = reader.GetInt32(0);
string name = reader.GetString(1);
string breed = reader.GetString(2);
Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed);
}
}
}
Yes, you can avoid injection by using Named Parameters
Use parameters instead of escaping strings:
var comando = "SELECT * FROM ANAGRAFICA WHERE E_MAIL=@user AND PASSWORD_AZIENDA=@password";
Then assign values to those parameters before you execute the SqlCommand.
You can check the below link to know how to prevent SQL injection in ASP.Net. I would prefer to use
@Jethro
You could also write it like this:
SqlParameter[] sqlParams = new SqlParameter[] {
new SqlParameter("@Name", contact.name),
new SqlParameter("@Number", contact.number),
new SqlParameter("@PhotoPath", contact.photoPath),
new SqlParameter("@ID", contact.id)
};
Follow the steps below and resolve the SQL INJECTION problem:
OracleParameter[] tmpParans = new OracleParameter[1];
tmpParans[0] = new Oracle.DataAccess.Client.OracleParameter("@User", txtUser.Text);
string tmpQuery = "SELECT COD_USER, PASS FROM TB_USERS WHERE COD_USER = @User";
OracleCommand tmpComand = new OracleCommand(tmpQuery, yourConnection);
tmpComand.Parameters.AddRange(tmpParans);
OracleDataReader tmpResult = tmpComand.ExecuteReader(CommandBehavior.SingleRow);
