How to properly sanitize (or parameterize) SQL Com

问题:

EDIT Notice that the statement isn't a single insert. It inserts multiple value arrays using the foreach block.

StringBuilder sbSQL = 
  new StringBuilder(
    "INSERT INTO [table] ([fk_id], [Description], [Title] ) VALUES");

foreach(var item in items) {
    sbSQL.AppendFormat("({0},'{1}','{2}'),", 
      item.fk_id, item.description, item.title)
}

myDataContext.ExecuteCommand(sbSQL.Remove(sbSQL.Length - 1, 1).ToString());
myDataContext.SubmitChanges();

I really would like to use this method, as it sped up DB inserts 2 times. But I don't know how I am supposed to paramaterize it.

回答1:

I started writing a comment, but this was too long.

The parameterized queries in LINQ-to-SQL should do just fine for you. Something like this:

StringBuilder sbSQL = new StringBuilder(
       "INSERT INTO [table] ([fk_id], [Description], [Title] ) VALUES");

int paramNum = 0;
List<object> paramValues = new List<object>();

foreach(var item in items) 
{
    sbSQL.AppendFormat("({{{0}}},{{{1}}},{{{2}}}),", 
        paramNum, 
        paramNum + 1, 
        paramNum + 2);

    paramValues.Add(item.fk_id);
    paramValues.Add(item.description);
    paramValues.Add(item.title);

    paramNum += 3;
}

myDataContext.ExecuteCommand(
    sbSQL.Remove(sbSQL.Length - 1, 1).ToString(), 
    paramValues.ToArray());

The ExecuteCommand function just takes your SQL command, complete with tokens ({0}) compatible with standard .NET string formatting functions, then turns the values you pass into parameters and uses its generated parameter names in place of the tokens. It's up to you to write the command and arrange the parameters however you like; it's not going to inspect or parse the command.

回答2:

Look at table value parameters (new to SQL Server 2008) - these allow you to pass all the values in one go.

The only issue is that these are not baked into Linq2SQL - so you need to provide your own method.

See this blog post for an example.

回答3:

Use parameterized queries.

public int ExecuteCommand(
    string command,
    params Object[] parameters
)

E.g.:

List<string> myList = new List<string>();

myList.Add("MyName");
myList.Add("MyAddress");

StringBuilder queryInsert = new StringBuilder();
queryInsert.Append("insert into Customers(name, address) values ({0},{1})");

this.myDataContext.ExecuteCommand(queryInsert.ToString(), myList.ToArray()); }