Creating safe SQL statements as strings

Question

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

AddWithValue is great (I use it often), BUT it can suffer from the wrong implicit type conversion if not used carefully. — Mitch Wheat, Nov 16 '08 at 00:30

score 19 · Answer 1 · answered Nov 16 '08 at 00:09

19

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

answered Nov 16 '08 at 00:09

Mitch Wheat

295,962
43
465
541

5

Using "cmd.AddWithValue("@id", employee.ID);" is much terser. – Brad Wilson Nov 16 '08 at 02:25
I get that every so often and got that on my answer to this one as well. – Cade Roux Oct 06 '10 at 00:11
Brad Wilson: An extra ".Parameters" is needed: `cmd.Parameters.AddWithValue("@id", employee.ID);` – Csaba Toth Mar 29 '13 at 00:20
How does this answer the question? The OP clearly states they do not have access to the DB and instead need to output commands that can be run at a later time. – O. R. Mapper May 08 '17 at 13:03

score 8 · Accepted Answer · answered Nov 16 '08 at 00:06

8

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.

answered Nov 16 '08 at 00:06

Jon Skeet

1,421,763
867
9,128
9,194

Just going to bed now, actually. Preaching tomorrow morning 10.30-11.30, so you're guaranteed a good crack at the whip then! ;) – Jon Skeet Nov 16 '08 at 00:21
Preaching? Are there recordings? – tofutim Jan 30 '14 at 21:58
@tofutim: Not that I'm aware of. – Jon Skeet Jan 30 '14 at 21:59
1

This doesn't seem to provide a solution when it's a hard requirement that the output is an `.sql` script file with ready-made `INSERT` commands with the data to be inserted. – O. R. Mapper May 08 '17 at 13:02
@O.R.Mapper: Indeed - note that the OP added that requirement after I'd answered, although they still accepted my answer, oddly enough... – Jon Skeet May 08 '17 at 14:00
@JonSkeet: True - probably fine for the OP, yet somewhat annoying for future visitors who actually face the problem as described in the question. – O. R. Mapper May 08 '17 at 14:05

score 4 · Answer 3 · answered May 31 '12 at 02:57

4

Fix your replace quotes function this way:

void string replaceQuotes(string value) {
     string tmp = value;
     tmp = tmp.Replace("'", "''");
     return tmp;
}

Cheers!

answered May 31 '12 at 02:57

KMX

2,631
1
23
28

3

It is much easier to use the parameters, think about all the characters that must be escaped. And also this is not SQL injection safe. – Karel Frajták May 21 '13 at 10:22
@KarelFrajták: How can the parameters be used when the formatted commands are only going to be executed later (presumably, after execution of the C# code has finished), as described in the question? – O. R. Mapper May 08 '17 at 13:05
@O.R.Mapper, in that case, I would queue the statements and a dictionary with parameters names and values. – Karel Frajták May 16 '17 at 08:47
@KarelFrajták: *"this is not SQL injection safe"* Really? I challenge you to provide a concrete `value` such that `"INSERT INTO Employees (name) VALUES ('" + replaceQuotes(value) + "');"` leads to SQL injection on any recent version of SQL Server. If you can provide such a `value`, I will reward a question or answer of your choice with 500 rep. – Heinzi May 10 '23 at 14:46

score 1 · Answer 4 · answered Nov 16 '08 at 02:38

Hmm I agree with everyone else that you should be using parameterized queries and will leave it at that. But how are you going pass these sql statements to your remote server? Do you have some type of service like a web service which will accept and execute arbitrary Sql commands or is your client app going to hit the DB directly?

If your going through some sort of proxy then no matter how much you sanitize your data on the client, a hacker could just bypass your app and hit the service. In which case do what Cade recommends and pass the data as XML for example or whatever format you choose (JSON, Binary etc..) Then build your SQL right before you actually run the command.

score 0 · Answer 5 · answered Nov 16 '08 at 02:14

0

To avoid injection, you need to ship the data to the remote server (perhaps in XML) and then on the remote server, the data should be converted back to appropriate data types and used in parameterized queries or stored procs.

answered Nov 16 '08 at 02:14

Cade Roux

88,164
40
182
265

Creating safe SQL statements as strings

5 Answers5

Linked