C# replace parameters in query with defined values [duplicate]

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
– Adwaenyth, Commented Apr 19, 2016 at 14:17
@Adwaenyth Then in the loop what about the 2nd occurrence of {?}... third occurrence... etc etc? — StealthRT
– StealthRT, Commented Apr 19, 2016 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
– StealthRT, Commented Apr 19, 2016 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
– Pikoh, Commented Apr 19, 2016 at 14:27

MakePeaceGreatAgain · Accepted Answer · 2016-04-19 14:40:02Z

4

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?

edited Apr 19, 2016 at 14:40

answered Apr 19, 2016 at 14:35

MakePeaceGreatAgain

37.4k6 gold badges67 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pikoh Over a year ago

I couldn't agree more

teo van kot · Accepted Answer · 2016-04-19 14:22:49Z

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, 2016 at 14:22

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

3 Comments

StealthRT Over a year ago

Nice Teo. This should work just fine for what I am doing.

StealthRT Over a year ago

Would it be possible to modify this code in order for it to use the {?} instead of the {0}, {1}, etc?

teo van kot Over a year ago

@StealthRT I think this there is no way. Here a bit more info.

Codor · Accepted Answer · 2016-04-19 14:19:47Z

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, 2016 at 14:19

Codor

17.6k9 gold badges32 silver badges59 bronze badges

7 Comments

MakePeaceGreatAgain Over a year ago

Stinks after SqlInjection... baaad idea.

StealthRT Over a year ago

Good idea.. it checks for injection AFTER it forms the query (in my case).

MakePeaceGreatAgain Over a year ago

@StealthRT Where does this check for injections?

Arturo Menchaca Over a year ago

How you check for sql-injection after the query is form?

StealthRT Over a year ago

@HimBromBeere Its not shown in the original post but I do have it after the fact.

|

Adwaenyth · Accepted Answer · 2016-04-19 14:46:00Z

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] });

Alfie Goodacre · Accepted Answer · 2016-04-19 14:24:09Z

0

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

answered Apr 19, 2016 at 14:24

Alfie Goodacre

2,7931 gold badge16 silver badges27 bronze badges

Comments

Martin Brandl · Accepted Answer · 2016-04-19 15:25:37Z

-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, 2016 at 15:25

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

answered Apr 19, 2016 at 14:17

ali

1,36110 silver badges13 bronze badges

1 Comment

MakePeaceGreatAgain Over a year ago

This is just a comment, no real answer to that question.

Collectives™ on Stack Overflow

C# replace parameters in query with defined values [duplicate]

6 Answers 6

1 Comment

3 Comments

7 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

7 Comments

Comments

Comments

1 Comment

Linked

Related