How to add parameters to a query when query is unknown length

Question

I am working on a project where multiple rows are needed to be added to a MySQL table, due to the high latency of the connection I am trying to add all the values in one command to save time.

So far I have a SQL query that is determined by a string array(each string being a "token"), this, being done with a foreach loop which is adding a value on the to insert query, however, this method is making it difficult to prevent SQL injection as I cannot find a way to add parameters the command. I am unfamiliar with other methods of preventing injection but open to new ideas.

query = "INSERT INTO tokenlist(token, user_id) VALUES";
foreach (string tok in tokens)
{
    query += " ('" + tok + "', " + logedinId + "),";
}

if (query != "INSERT INTO tokenlist(token, user_id) VALUES")
{
    query = query.Remove(query.Length - 1);
    query += ";";
    if (OpenConnection() == true) {
        MySqlCommand cmd = new MySqlCommand(query, mysqlcon);
        cmd.ExecuteNonQuery();
        CloseConnection();
    }
}

`MySqlDataAdapter` is an option. See: https://stackoverflow.com/a/30781587/2419531 — Saravana, Dec 20 '19 at 14:26

Wachburn · Accepted Answer · 2019-12-20T14:47:39.900

2

Use SqlCommand.Parameters.Add.
Instead of using foreach use for, and add parameter names with appended number "@tok" + i

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=netframework-4.8

Just example draft code, I didn't try it:

var tokens = new List<dynamic>() { new { tok = 1, logedinId = 2 }, new { tok = 1, logedinId = 2 } };
var query = "INSERT INTO tokenlist(token, user_id) VALUES";

SqlCommand cmd = new SqlCommand(query, new SqlConnection());

for (int i = 0; i < tokens.Count; i++)
{
    var tokName = $"@tok{i}";
    var logedinIdName = $"@logedinId{i}";
    var token = tokens[i];
    query += $" ({tokName}, {logedinIdName}),";
    cmd.Parameters.Add(new SqlParameter(tokName, SqlDbType.Int)).Value = token.tok;
    cmd.Parameters.Add(new SqlParameter(logedinIdName, SqlDbType.Int)).Value = token.logedinId;
}

if (tokens.Any())
{
    query = query.Remove(query.Length - 1);
    query += ";";

    cmd.ExecuteNonQuery();
}

edited Dec 20 '19 at 14:47

answered Dec 20 '19 at 14:30

Wachburn

2,842
5
36
59

Mind if I edit your question to add a code example? – MindSwipe Dec 20 '19 at 14:32
1

Also, do not use `Add`, use `AddWithValue` as the `Add` command has been deprecated a while ago and is now obsolete [source](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.add?view=netframework-4.8) – MindSwipe Dec 20 '19 at 14:45
@MindSwipe AFAIK only `Add(string, object)` is obsolete. The other overloads are not. – Fildor Dec 20 '19 at 15:03
You can do "cmd.Parameters.Add(tokName, SqlDbType.Int).Value = token.tok;". No need for the "new SqlParameter" wrapper. – Polyfun Dec 20 '19 at 15:52

score 0 · Answer 2 · answered Dec 20 '19 at 14:26

0

var sql = "INSERT INTO tokenlist(token, user_id) VALUES(@token, @user_id)";
using var cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@token", "tkn");
cmd.Parameters.AddWithValue("@user_id", 3);
cmd.Prepare();
cmd.ExecuteNonQuery();

try adding parameters like this. + you will avoid sql injection

answered Dec 20 '19 at 14:26

irvin

23
7

2

Don't use `AddWithValue`, use `Add` and specify the type, size/length and value. Using `AddWithValue` has to infer the type and it may be wrong. – Trevor Dec 20 '19 at 14:27
This doesn't quite answer the question. The question is to add a dynamically long list of values to the query, not how to parameterize a query – MindSwipe Dec 20 '19 at 14:29

How to add parameters to a query when query is unknown length

2 Answers2