Is passing by use of parameter necessary for data type int to prevent sql injection

Question

as per question once more: Do we really need to pass numbers datatype variable values as parameters to prevent sql injection.

I do have two sample function one with parameter and next without using parameter

function CheckThis(int UIN)
{
  var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
  var query = "Select * From Products Where ProductID = @ProductID";
  using (var conn = new SqlConnection(connect))
  {
    using (var cmd = new SqlCommand(query, conn))
    {
      cmd.Parameters.Add("@ProductID", SqlDbType.Int);
      cmd.Parameters["@ProductID"].Value = UIN;
      conn.Open();
      //Process results
    }
  }
}

OR is following ok

function CheckThis(int UIN)
{
  var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
  var query = "Select * From Products Where ProductID = " + UIN;
  using (var conn = new SqlConnection(connect))
  {
    using (var cmd = new SqlCommand(query, conn))
    {
      //cmd.Parameters.Add("@ProductID", SqlDbType.Int);
      //cmd.Parameters["@ProductID"].Value = UIN;
      conn.Open();
      //Process results
    }
  }
}

Jon Skeet · Accepted Answer · 2016-04-19 15:25:05Z

4

You don't need to do it to stop SQL injection attacks¹. However:

Your code is cleaner if you separate the SQL from the values
If the type is later changed from int to something else, you would then be at risk of an injection attack, and it's entirely possible that it would be missed in code review
It avoids any issues with number-to-string conversions involving unexpected thousands separators etc

In short, I would definitely still use parameters.

¹ Unless your attacker can also affect your locale settings. At that point, even string concatenation with integers can be vulnerable to SQL injection attacks.

edited Apr 19, 2016 at 15:25

answered Jul 18, 2013 at 9:54

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

KoolKabin Over a year ago

gr8... listing of advantages of using it

Panzercrisis Over a year ago

I wonder if it's the same for decimal, bignum, and floating-point types?

Jon Skeet Over a year ago

@Panzercrisis: Yes, absolutely. Parameterization is simply the way to go.

Panzercrisis Over a year ago

Are they able to be hacked?

Jon Skeet Over a year ago

@Panzercrisis: Ah. They're mostly as safe as integer types, unless someone can change your culture settings (in which case you're in trouble) but they're more likely to suffer from conversion issues.

|

Collectives™ on Stack Overflow

Is passing by use of parameter necessary for data type int to prevent sql injection

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related