23

I have some queries (to an acccess 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 somethings like mysql_escape_string on PHP...

markzzz
  • 47,390
  • 120
  • 299
  • 507

9 Answers9

45

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";
Jethro
  • 5,896
  • 3
  • 23
  • 24
  • Not necessarily *need* to use, but they're the best option there is for that and work reliably ;-). Every homegrown variant is bound to have some problems, probably. – Joey Jul 01 '11 at 12:35
  • @Jethro : but after, can I pass the real value to the query? I need to pass the myparam array to the SqlExecute query, ain't? – markzzz Jul 14 '11 at 11:08
  • @Markzzz, yes you will need to pass your sql parameters with your query so it can get executed. What do you mean about passing the real value to the query? You will pass the real value to the paramenters which builds your query. – Jethro Jul 14 '11 at 11:14
  • so the code above (without any code) should works? In fact it doesnt. The field user is escaped into myparm[0] right? The query doesnt know that param... – markzzz Jul 14 '11 at 11:16
  • I mean : I don't need to add these parameters to the SQL Connection? – markzzz Jul 14 '11 at 11:22
  • The above is just an example to get you started. Take a look http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx as Jon Skeet suggested to see example of how to add parameters to your query and execute the query. – Jethro Jul 14 '11 at 11:26
  • `SqlCommand cmdGetUser = new SqlCommand(comando, openSqlConnection);` `cmdGetUser.Parameters.AddRange(myparm);` or as @Serghei says `cmdGetUser.Parameters.Add(new SqlParameter("Name", dogName));` – Benjineer May 29 '14 at 06:18
  • 99% of the time, you are correct and a developer should use a parameterized query. But 1% of the time, that is not an option. Sometimes your parameter is a table name or a column name. A parameterized query does not work for either. – Rhyous Aug 07 '17 at 15:43
20

Don't escape the strings to start with - use a parameterized query. Benefits of this over escaping:

  • The code is easier to read
  • You don't have to rely on getting the escaping correct
  • It's possible that there are performance improvements (DB-specific etc)
  • It separates "code" (the SQL) from the data, which is just good sense logically
  • It means you don't need to worry about data formats for things like numbers and dates/times.

The docs for SqlCommand.Parameters give a good, complete example.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
5

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);
    }
    }
}
Sergey K
  • 4,071
  • 2
  • 23
  • 34
2

Yes, you can avoid injection by using Named Parameters

George Johnston
  • 31,652
  • 27
  • 127
  • 172
1

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.

Yuck
  • 49,664
  • 13
  • 105
  • 135
1

You can check the below link to know how to prevent SQL injection in ASP.Net. I would prefer to use

  1. Using parametrized queries or Stored Procedures.
  2. Validating special characters like '(very dangerous)

http://dotnet.dzone.com/news/aspnet-preventing-sql-injectio

suryakiran
  • 1,976
  • 25
  • 41
0

@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)
};
biegleux
  • 13,179
  • 11
  • 45
  • 52
Chriz
  • 572
  • 7
  • 14
0

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);
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Dominus Farib
  • 1
  • 1
0

If you can convert these to Named Parameters, I think you would be better served.

tjg184
  • 4,508
  • 1
  • 27
  • 54