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.

What is wrong with the answers on that question? Why do they not work for you? — Oded
– Oded, Commented Apr 25, 2012 at 10:45
You should show us an example Insert with it's field name/type and value. — Tim Schmelter
– Tim Schmelter, Commented Apr 25, 2012 at 10:51
I've included an edit. Does that make better sense? There is also supposed to be a CREATE TABLE statement, but I didn't bother including that. — daniel
– daniel, Commented Apr 25, 2012 at 11:02
@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
– Cylindric, Commented Apr 25, 2012 at 11:04

Cylindric · Accepted Answer · 2012-04-25 11:24:30Z

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

Neo · Accepted Answer · 2012-04-25 12:24:45Z

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!

Tim Schmelter · Accepted Answer · 2012-04-25 12:28:26Z

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.

edited Apr 25, 2012 at 12:28

answered Apr 25, 2012 at 11:03

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

2 Comments

Cylindric Over a year ago

Daniel specifically wants the SQL statements as strings though, I assume for output/saving/displaying, and your example executes the query but you never see the final commands.

Tim Schmelter Over a year ago

@Cylindric: Yes, i've just realized that. I've edited my answer.

Collectives™ on Stack Overflow

SqlCommand with parameters as a string

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related