Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating safe SQL statements as strings

I'm using C# and .NET 3.5. I need to generate and store some T-SQL insert statements which will be executed later on a remote server.

For example, I have an array of Employees:

new Employee[]
{
   new Employee { ID = 5, Name = "Frank Grimes" },
   new Employee { ID = 6, Name = "Tim O'Reilly" }
}

and I need to end up with an array of strings, like this:

"INSERT INTO Employees (id, name) VALUES (5, 'Frank Grimes')",
"INSERT INTO Employees (id, name) VALUES (6, 'Tim O''Reilly')"

I'm looking at some code that creates the insert statements with String.Format, but that doesn't feel right. I considered using SqlCommand (hoping to do something like this), but it doesn't offer a way to combine the command text with parameters.

Is it enough just to replace single quotes and build a string?

string.Format("INSERT INTO Employees (id, name) VALUES ({0}, '{1}')",
    employee.ID,
    replaceQuotes(employee.Name)
    );

What should I be concerned about when doing this? The source data is fairly safe, but I don't want to make too many assumptions.

EDIT: Just want to point out that in this case, I don't have a SqlConnection or any way to directly connect to SQL Server. This particular app needs to generate sql statements and queue them up to be executed somewhere else - otherwise I'd be using SqlCommand.Parameters.AddWithValue()

like image 341
Jason Anderson Avatar asked Nov 15 '08 23:11

Jason Anderson


2 Answers

Create your SqlCommand object like so:

SqlCommand cmd = new SqlCommand(
        "INSERT INTO Employees (id, name) VALUES (@id, @name)", conn);

SqlParameter param  = new SqlParameter();
param.ParameterName = "@id";
param.Value         = employee.ID;

cmd.Parameters.Add(param);

param  = new SqlParameter();
param.ParameterName = "@name";
param.Value         = employee.Name;

cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();
like image 182
Mitch Wheat Avatar answered Oct 13 '22 08:10

Mitch Wheat


Use parameterised commands. Pass the parameters along to your remote server as well, and get that to call into SQL Server, still maintaining the distinction between the SQL itself and the parameter values.

As long as you never mix treat data as code, you should be okay.

like image 41
Jon Skeet Avatar answered Oct 13 '22 08:10

Jon Skeet