Is it safe to not parameterize an SQL query when t

Question

In terms of SQL injection, I completely understand the necessity to parameterize a string parameter; that's one of the oldest tricks in the book. But when can it be justified to not parameterize an SqlCommand? Are any data types considered "safe" to not parameterize?

For example: I don't consider myself anywhere near an expert in SQL, but I can't think of any cases where it would be potentially vulnerable to SQL injection to accept a bool or an int and just concatenate it right into the query.

Is my assumption correct, or could that potentially leave a huge security vulnerability in my program?

For clarification, this question is tagged c# which is a strongly-typed language; when I say "parameter," think something like public int Query(int id).

Answer 1

This is not safe even for non-string types. Always use parameters. Period.

Consider following code example:

var utcNow = DateTime.UtcNow;
var sqlCommand = new SqlCommand("SELECT * FROM People WHERE created_on <= '" + utcNow + "'");

At the first glance code looks safe, but everything changes if you make some changes in Windows Regional Settings and add injection in short date format:

Now resulting command text looks like this:

SELECT * FROM People WHERE created_on <= '26.09.2015' OR '1'<>' 21:21:43'

The same can be done for int type as user can define custom negative sign which can be easily changed into SQL injection.

One could argue that invariant culture should be used instead of current culture, but I have seen string concatenations like this so many times and it is quite easy to miss when concatenating strings with objects using +.

Answer 2

In my opinion if you can guarantee that the parameter you working with will never contain a string it is safe but I would not do it in any case. Also, you will see a slight performance drop due to the fact that you are performing concatenation. The question I would ask you is why don't you want to use parameters?

Answer 3

It is ok but never safe.. and the security always depend on the inputs, for example if the input object is TextBox, the attackers can do something tricky since the textbox can accept string, so you have to put some kind of validation/conversion to be able prevent users the wrong input. But the thing is, it is not safe. As simply as that.

Answer 4

No you can get an SQL injection attack that way. I have written an old article in Turkish which shows how here. Article example in PHP and MySQL but concept works same in C# and SQL Server.

Basically you attack following way. Lets consider you have a page which shows information according to integer id value. You do not parametrized this in value, like below.

http://localhost/sqlEnjeksiyon//instructors.aspx?id=24

Okay, I assume you are using MySQL and I attack following way.

http://localhost/sqlEnjeksiyon//instructors.aspx?id=ASCII((SELECT%20DATABASE()))

Note that here injected value is not string. We are changing char value to int using ASCII function. You can accomplish same thing in SQL Server using "CAST(YourVarcharCol AS INT)".

After that I use length and substring functions to find about your database name.

http://localhost/sqlEnjeksiyon//instructors.aspx?id=LEN((SELECT%20DATABASE()))

http://localhost/sqlEnjeksiyon//instructors.aspx?id=ASCII(SUBSTR(SELECT%20DATABASE(),1,1))

Then using database name, you start to get table names in database.

http://localhost/sqlEnjeksiyon//instructors.aspx?id=ASCII(SUBSTR((SELECT table_name FROM INFORMATION_SCHEMA.TABLES LIMIT 1),1,1))

Of course you have to automate this process, since you only get ONE character per query. But you can easily automate it. My article shows one example in watir. Using only one page and not parameterized ID value. I can learn every table name in your database. After that I can look for important tables. It will take time but it is doable.

Answer 5

To add some info to Maciek answer:

It is easy to alter the culture info of a .NET third party app by calling the main-function of the assembly by reflection:

using System;
using System.Globalization;
using System.Reflection;
using System.Threading;

namespace ConsoleApplication2
{
  class Program
  {
    static void Main(string[] args)
    {
      Assembly asm = Assembly.LoadFile(@"C:\BobbysApp.exe");
      MethodInfo mi = asm.GetType("Test").GetMethod("Main");
      mi.Invoke(null, null);
      Console.ReadLine();
    }

    static Program()
    {
      InstallBobbyTablesCulture();
    }

    static void InstallBobbyTablesCulture()
    {
      CultureInfo bobby = (CultureInfo)CultureInfo.InvariantCulture.Clone();
      bobby.DateTimeFormat.ShortDatePattern = @"yyyy-MM-dd'' OR ' '=''";
      bobby.DateTimeFormat.LongTimePattern = "";
      bobby.NumberFormat.NegativeSign = "1 OR 1=1 OR 1=";
      Thread.CurrentThread.CurrentCulture = bobby;
    }
  }
}

This only works if the Main function of BobbysApp is public. If Main is not public, there might be other public functions you might call.

Answer 6

When using a strongly-typed platform on a computer you control (like a web server), you can prevent code injection for queries with only bool, DateTime, or int (and other numeric) values. What is a concern are performance issues caused by forcing sql server to re-compile every query, and by preventing it from getting good statistics on what queries are run with what frequency (which hurts cache management).

But that "on a computer you control" part is important, because otherwise a user can change the behavior used by the system for generating strings from those values to include arbitrary text.

I also like to think long-term. What happens when today's old-and-busted strongly-typed code base gets ported via automatic translation to the new-hotness dynamic language, and you suddenly lose the type checking, but don't have all the right unit tests yet for the dynamic code?

Really, there's no good reason not to use query parameters for these values. It's the right way to go about this. Go ahead and hard-code values into the sql string when they really are constants, but otherwise, why not just use a parameter? It's not like it's hard.

Ultimately, I wouldn't call this a bug, per se, but I would call it a smell: something that falls just short of a bug by itself, but is a strong indication that bugs are nearby, or will be eventually. Good code avoids leaving smells, and any good static analysis tool will flag this.

I'll add that this is not, unfortunately, the kind of argument you can win straight up. It sounds like a situation where being "right" is no longer enough, and stepping on your co-workers toes to fix this issue on your own isn't likely to promote good team dynamics; it could ultimately hurt more than it helps. A better approach in this case may be to promote the use of a static analysis tool. That would give legitimacy and credibility to efforts aimed and going back and fixing existing code.