SqlCommand with parameters as a string

Question

I have an application I need to create which, given some user input in the form of CSV, needs to parse and generate this CSV into multiple formats. One of these formats is a series of SQL INSERT statements (as a string) for each line of CSV.

(At this point you can assume I've already parsed the CSV into a list of values or something, so that is not the point of the question)

Given that this input could contain vulnerabilities, I wish to generate the INSERT statements which have been validated and sanitised.

I am familiar with creating an SqlCommand object and adding values to its list of Parameters, but looking over a similar question it doesn't appear to work in way I had hoped.

So is there a way to generate sanitised SQL statements, as strings, in the way I need to?

EDIT: This is an example what I want to do.

CSV:

id,name,country
1,Alice,France
2,Bob,Austria
3,Carol,Germany

SQL:

...
INSERT INTO Users (id, name, country) VALUES (1, 'Alice', 'France');
INSERT INTO Users (id, name, country) VALUES (2, 'Bob', 'Austria');
INSERT INTO Users (id, name, country) VALUES (3, 'Carol', 'Germany');
...

As there are no data types given in the CSV, the application has to determine that as well.

Answer 1

Because i don't know how you've stored your variables, i'll show you a complete, possible implementation with your sample data using a List<Dictionary<String, Object>>():

Add your sample-data:

var tableName = "Users";
var records = new List<Dictionary<String, Object>>();
var recordFields = new Dictionary<String, Object>();
recordFields.Add("id", 1);
recordFields.Add("name", "Alice");
recordFields.Add("country", "France");
records.Add(recordFields);
recordFields = new Dictionary<String, Object>();
recordFields.Add("id", 2);
recordFields.Add("name", "Bob");
recordFields.Add("country", "Austria");
records.Add(recordFields);
recordFields = new Dictionary<String, Object>();
recordFields.Add("id", 3);
recordFields.Add("name", "Carol");
recordFields.Add("country", "Germany");
records.Add(recordFields);

Generate the parametrized insert statements:

using (var con = new SqlConnection(Settings.Default.ConnectionString))
{
    con.Open();
    foreach (var record in records)
    {
        String insertSql = String.Format("INSERT INTO {0} ({1}) VALUES ({2});"
            , tableName
            , String.Join(",", record.Select(r => r.Key))
            , String.Join(",", record.Select(r => "@" + r.Key)));
        using (var insertCommand = new SqlCommand(insertSql, con))
        {
            foreach (var field in record)
            {
                var param = new SqlParameter("@" + field.Key, field.Value);
                insertCommand.Parameters.Add(param);
            }
            insertCommand.ExecuteNonQuery();
        }
    }
}

Note that this is not really tested(it compiles and looks good) but it should help you anyway.

Edit:

@Oded: I think the problem is that daniel doesn't want to run the inserts, but show/output/save them. So using the SqlCommand parameters is no good, because you don't see the generated SQL.

@Cylindric That's correct

That's not possible and a contradiciton, you cannot use a String with SqlParameters. So i'm afraid you're open to Sql-Injection if you would run these inserts later. I would suggest to use above code when you're actually running the statemenets.

Answer 2

Not so much an answer, as a cautionary note. If you end up needing to go the 'classic' escaping route to do this, and really need safety (i.e. the data is coming in from untrusted source), don't forget it's not just simple escaping you need to worry about.

Basic character escaping we hear about all the time:

• ' -> '' apostrophe's and stuff are quite obvious and documented ad-nauseum
• ; multiple-commands in one statement - not always allowed by the DB, but dangerous

If you're parsing for "nefarious behaviour" though, have you thought about:

• SELECT/*not important*/1/*really...*/FROM/*im serious*/users
• SELECT%09FIELDS%09FROM%0dTABLE_NAME
• WHERE username=CONCAT(CHAR(97),CHAR(100),CHAR(109),CHAR(105),CHAR(110))
• SELECT passwd FROM users WHERE username=0x61646d696e

In summary: Here Be Dragons.

http://www.ihteam.net/papers/blind-sqli-regexp-attack.pdf

http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/#HexbasedSamples

Answer 3

Well chances are if you don't wan't to use SQL objects then you would have to sanatize the entries yourself. I'm not aware of any recommended format for SQL however for MySQL the following would work. I've changed it to work with SQL however I cant garantee it has covered all possible injection attacks.

public string sqlEscape(string VAL)
{
    if (VAL == null)
    {
        return null;
    }
    return "\"" + Regex.Replace(VAL, @"[\r\n\x00\x1a\\'""]", @"\$0") + "\"";
}

to use you would then do (assuming your CSV line is stored in an array called csv):

string query = @"INSERT INTO Users (id, name, country) VALUES (" + sqlEscape(csv[0]) + ", " + sqlEscape(csv[1]) + ", " + sqlEscape(csv[2]) + ");";

if anyone can enhance this let me know!