C# replace parameters in query with defined values

Question

string[] theParms = ['parm1', 'parm2'];
string theQuery = "SELECT something, somethingAgain " + 
                  "FROM aDBTable " +
                  "WHERE something = '{?}'" +
                  "AND something <> '{?}'";

I am needing to replace the {?}'s with the defined parms in theParms.

Is there some type of loop in C# that I can use in order to loop through the string and replace each found {?} with the corresponding parm value?

Something like this:

First loop:

SELECT something, somethingAgain 
FROM aDBTable 
WHERE something = 'parm1' AND something <> '{?}'

Second loop:

SELECT something, somethingAgain 
FROM aDBTable 
WHERE something = 'parm1' AND something <> 'parm2'

Is there some type of REGEX or common framework function that can do the above?

sql injection check

bool injectionCheckin = new injectionCheck().inCheck(theFinalQuery);

public class injectionCheck
{
    public bool inCheck(string queryString)
    {
        var badWords = new[] {
            "EXEC", "EXECUTE", ";", "-", "*", "--", "@",
            "UNION", "DROP","DELETE", "UPDATE", "INSERT",
            "MASTER", "TABLE", "XP_CMDSHELL", "CREATE",
            "XP_FIXEDDRIVES", "SYSCOLUMNS", "SYSOBJECTS",
            "BC_HOME_ADDRESS1", "BC_HOME_ADDRESS2", "BC_HOME_CITY", "BC_HOME_COUNTY", "BC_HOME_POSTAL", "BC_MAIL_ADDRESS1",
            "BC_MAIL_ADDRESS2", "BC_MAIL_CITY", "BC_MAIL_COUNTY", "BC_MAIL_POSTAL", "BC_MAIL_STATE", "FLSA_STATUS", "GRADE",
            "GRADE_ENTRY_DT", "HIGHEST_EDUC_LVL", "LAST_INCREASE_DT", "BC_SALP_DESCR", "BC_SALP_DESCRSHORT", "SAL_ADMIN_PLAN"
        };
        string pattern = "(?<!\\w)(" + Regex.Escape(badWords[0]);

        foreach (var key in badWords.Skip(1))
        {
            pattern += "|" + Regex.Escape(key);
        }

        pattern += ")(?!\\w)";

        dynamic _tmpCount = Regex.Matches(queryString, pattern, RegexOptions.IgnoreCase).Count;

        if (_tmpCount >= 1)
            return true;
        else
            return false;
    }
}

You should maybe use parameters to avoid injection issues but otherwise, what's against a simple `for` loop where you replace the first instance of `{?}` with your parameter? — Adwaenyth, Apr 19 '16 at 14:17
@Adwaenyth Then in the loop what about the 2nd occurrence of {?}... third occurrence... etc etc? — StealthRT, Apr 19 '16 at 14:19
I check for injection **after** i compile the full query. As in, place the parameters within the query then check it for any injection. — StealthRT, Apr 19 '16 at 14:23
Either use string.format like @Codor suggested or - If you don't know the number of parameters - in the for loop do `IndexOf("{?}")` which gets you the index position of the first occurance of `{?}` (or -1 if not found) and then call `Remove(index, 3)` and `Insert(index, theParms[i])`. — Adwaenyth, Apr 19 '16 at 14:23
Ok,so you check for sql injection. The question now is.. why? Why do you do it by yourself and go to that kind of parameter substitution instead of using parameterized queries? — Pikoh, Apr 19 '16 at 14:27
@Pikoh Simply because I set up a website that lets the user create their own query's in a GUI type of way. — StealthRT, Apr 19 '16 at 14:28
You can use parameterized queries anyway, just let the user enter any arbitrary code and replace every occurence of `{0}` by `@myParam` for example. Thus your query becomes something like `select * from table where attribute = @myParam` which you can now use within parameterized query. — MakePeaceGreatAgain, Apr 19 '16 at 18:43

MakePeaceGreatAgain · Answer 1 · 2016-04-19T14:40:02.537

Always create Sql-commands by parameterized queries:

using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
    var @params = new Dictionary<string, object>{
        { "something", myValue },
        { "somethingDifferent", anotherValue },
    };

    cmd.CommandText = "SELECT something, somethingAgain " + 
              "FROM aDBTable " +
              "WHERE something = @something'" +
              "AND something <> @somethingDifferent'";
    foreach (KeyValuePair<string, object> item in values)
    {
        cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
    }

    DataTable table = new DataTable();
    using (var reader = cmd.ExecuteReader())
        {
            table.Load(reader);
            return table;
        }
    }
}

This prevents all sort of SqlInjection and you won´t any weird checks as yours with the badlist which is quite messy and does not really prevent you, you can easily bypass the list with some escaping for instance. In particular: why do you want to write your own validation when there allready are ready-to-use methods that do exactly what you want?

score 1 · Answer 2 · answered Apr 19 '16 at 14:19

1

If you want to do it in either case, you could do it without a loopy as follows.

string theQuery = String.Format( "SELECT something, somethingAgain " + 
                                 "FROM aDBTable " +
                                 "WHERE something = '{0}'" +
                                 "AND something <> '{1}'",
                                 theParams[0], theParams[1] );

answered Apr 19 '16 at 14:19

Codor

17,447
9
29
56

2

Stinks after SqlInjection... baaad idea. – MakePeaceGreatAgain Apr 19 '16 at 14:20
1

**Good idea**.. it checks for injection **AFTER** it forms the query (in my case). – StealthRT Apr 19 '16 at 14:21
@StealthRT Where does this check for injections? – MakePeaceGreatAgain Apr 19 '16 at 14:23
How you check for sql-injection after the query is form? – Arturo Menchaca Apr 19 '16 at 14:24
@HimBromBeere Its not shown in the original post but I do have it after the fact. – StealthRT Apr 19 '16 at 14:24
@HimBromBeere My function that I use is now shown in the OP for checking for injection. – StealthRT Apr 19 '16 at 14:27
I think String.Format is your best option, if you have a limited amount of parameters. – Nils Apr 19 '16 at 14:29

score 1 · Accepted Answer · answered Apr 19 '16 at 14:22

1

Why not just use String.Format?

string[] theParms = new string[] { "parm1", "parm2" };
string theQuery = @"SELECT something, somethingAgain 
                    FROM aDBTable 
                    WHERE something = '{0}'
                    AND something <> '{1}'";
var res = string.Format(theQuery, theParms);

Result:

SELECT something, somethingAgain 
       FROM aDBTable 
       WHERE something = 'parm1'
       AND something <> 'parm2'

answered Apr 19 '16 at 14:22

teo van kot

12,350
10
38
70

Nice Teo. This should work just fine for what I am doing. – StealthRT Apr 19 '16 at 14:30
Would it be possible to modify this code in order for it to use the {?} instead of the {0}, {1}, etc? – StealthRT Apr 19 '16 at 14:37
@StealthRT I think this there is no way. [Here a bit more info](http://stackoverflow.com/a/1426629/1849444). – teo van kot Apr 19 '16 at 14:39

Adwaenyth · Answer 4 · 2016-04-19T14:46:00.880

Okay, to avoid Injection and all that, why don't you do it like this:

string[] theParms = // initialization
string theQuery = // initialization

SqlCommand cmd = new SqlCommand(/* enter connection string */, theQuery)

for(int i = 0; i < theParams.Length; i++)
{
    int index = cmd.Text.IndexOf("{?}");
    if(index > -1)
    {
        string pName = string.Format("@p{0}", i);
        cmd.Text = cmd.Text.Remove(index, 3).Insert(index, pName);
        cmd.Parameters.Add(new SqlParameter() { Name = pName, Value = theParms[i] });
    }
}

That should avoid any manual injection checks alltogether... at least if you can't get the query pre-compiled and have to load it at runtime. Otherwise just formulate the SqlCommand's text appropriately and you'll not need a loop or anything. Just a simple initialization:

SqlCommand cmd = new SqlCommand(/* enter connection string */, "SELECT something, somethingAgain FROM aDBTable WHERE something = @p0 AND something <> @p1");
cmd.Parameters.Add(new SqlParameter() { Name = "@p0", Value = theParms[0] });
cmd.Parameters.Add(new SqlParameter() { Name = "@p1", Value = theParms[1] });

score 0 · Answer 5 · answered Apr 19 '16 at 14:24

You can use IndexOf and substrings to find each instance

for(int i = 0; i < theParms.GetLength(0); i++)
{
    string[] tempStrings = new string[]{ theQuery.Substring(0,theQuery.IndexOf("{?}") - 1),
    theQuery.Substring(theQuery.IndexOf("{?}"), 3),
    theQuery.Substring(theQuery.IndexOf("{?}") + 4) }
    tempStrings[1] = tempStrings[1].Replace("{?}", theParms[i]);
    theQuery = String.Join("", tempStrings);
}

Though seeing as you check for injection afterwards it is definitely much better to use String.Format

score -2 · Answer 6 · edited Apr 19 '16 at 15:25

-2

You don't have to handle it by yourself. Instead, ADO.NET allows you define parameters and set their values. See the sample here .MSDN

edited Apr 19 '16 at 15:25

Martin Brandl

56,134
13
133
172

answered Apr 19 '16 at 14:17

ali

1,301
10
12

This is just a comment, no real answer to that question. – MakePeaceGreatAgain Apr 19 '16 at 14:19

C# replace parameters in query with defined values

6 Answers6