Easy way to escape sql strings?

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 开发者_JS百科it figure out if the key/value is a string or int. I wouldn't mind if I had to use {0} instead of ?

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 cmd = connection.CreateCommand();
     cmd.CommandText = command;
     for (int i = 0; i < names.Length; ++i )
     {
         cmd.Parameters.AddWithValue(names[i], values[i]);
     }

     return cmd;
}

used as

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

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.

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.

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

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()