Easy way to escape sql strings?

Question

In my code I have this everywhere

command.Parameters.Add("@name", DbType.String).Value = name;

Is there an easier way? I would love to do something like

command.command.CommandTextFn("insert into tbl(key,val) values(?, ?);", key, value);

and have it figure out if the key/value is a string or int. I wouldn't mind if I had to use {0} instead of ?

Answer 1

Using parametrized queries protects your system against SQL injection attacks.

Of course, you can deal with SQL escaping, but why bother? You can make a mistake and just discover it when its too late.

Please read this article as it explain pros/cons using parametrized queries.

Invest your time improving other code pieces.

Answer 2

You're doing the right thing - using parameters is the best solution both for reuse of code and to protect you from SQL injection attacks.. It ain't broke so don't fix it!

If it really bugs you (and it shouldn't) then you could probably do something creative with an extension method to allow you wrap that bit of code up in something a bit smaller but in truth there are better things to worry about.

Answer 3

You could write your own extension method (untested, might require minor modifications):

private static DbType MapToDbType(Type t) {
    return something;
}

public static void CommandTextFn(this IDbCommand cmd, string stmt, params object[] parameters) {
    try {
        for (int i = 0; i < params.Length; i++) {
            IDbParameter p = cmd.Parameters.Add(string.Format(NumberFormatInfo.InvariantInfo, "@{0}", i), object.ReferenceEquals(parameters[i], null) ? DbType.String : MapToDbType(parameters[i].GetType());
            p.Value = parameters[i];
        }
        int currentIndex;
        cmd.CommandText = new Regex("\\?").Replace(stmt, m => cmd.Parameters.Add(string.Format(NumberFormatInfo.InvariantInfo, "@{0}", currentIndex++);
        cmd.ExecuteNonQuery();
    }
    finally {
        cmd.Parameters.Clear();
    }
}

Answer 4

Use the AddWithValue method:

command.Parameters.AddWithValue( "@name", name );

You could use this in conjunction with an extension method:

public static SqlCommand CreateCommand( this SqlConnection connection, string command, string[] names, object[] values )
{
     if (names.Length != values.Length)
     {
          throw new ArgumentException( "name/value mismatch" );
     }

     var command = connection.CreateCommand();
     for (int i = 0; i < names.Length; ++i )
     {
         command.Parameters.AddWithValue( name[i], value[i] );
     }

     return command;
}

used as

var command = connection.CreateCommand( "insert into tbl (key,val) values(@key,@val)",
                                        new string[] { "@key", "@val" },
                                        new object[] { key, val } );

Answer 5

Try to replace such calls with linq to sql. You will gain type checking on compile time and it will be no longer needed to escape strings and think about sql injections.

sample:

Dim newCustomer = New Customer With {.CustomerID = "MCSFT", .CompanyName = "Microsoft", .ContactName = "John Doe", .ContactTitle = "Sales Manager", .Address = "1 Microsoft Way", .City = "Redmond", .Region = "WA", .PostalCode = "98052", .Country = "USA", .Phone = "(425) 555-1234", .Fax = Nothing}
db.Customers.Add(newCustomer)
db.SubmitChanges()

Answer 6

Create a set of generic functions that take the parameter name and the value. Wrap and hide away all your boilerplate code.