What is {0},{1},{2},{3} in the SQL query

Question

I want to know what {0} or {1} mean in the query given below

public static int EnsureObjectHasAliases(string type, int id, int programId, string[] aliasList)
{
    var sb = new StringBuilder();
    if(aliasList!=null)
    for (int i = 0; i < aliasList.Length; i++)
    {
        string alias = aliasList[i];
        if (NormalizeString(ref alias))
        {
        if (sb.Length > 0)
        sb.Append(",");
        sb.Append(FormatStringForSql(alias));
        }
    }
    if (sb.Length > 0)
    {
        string aliases = sb.ToString();
        var c = (int)GetValue("SELECT count(*) FROM {0}Alias WHERE ProgramId = {1} AND {0}Id = {2} and Alias in ({3})", type, programId, id, aliases);
    if (c > 0)
       return id;

    if (aliasList != null)
    if (aliasList.Any(alias => AddObjectAlias(type, programId, alias, id) < 0))
        return -1;
    }
        return id;
    }

I have no idea what this query means:

SELECT count(*) FROM {0}Alias WHERE ProgramId = {1} AND {0}Id = {2} and Alias in ({3})

I want to know what {0} or {1} or {2} and {3} and ALIAS is in the query. I know that it is representing some table but how?

Is it something related to String.Format()?

EDIT:-

My GetValue() function is like this:

public static object GetValue(string sql, params object[] args)
{
    return GetValue(GlobalFactories.GetLogger().GetDefaultTransaction(), sql, args);
}
public static object GetValue(SqlConnection db, string sql, params object[] args)
        {
            return GetValue(GlobalFactories.GetLogger().GetDefaultTransaction(), db, sql, args);
        }

public static object GetValue(LogTransactionBase logTransaction,SqlConnection db,  string sql, params object[] args)
{
    if (string.IsNullOrEmpty(sql))
    return null;
    if (args != null && args.Length > 0)
        sql = string.Format(sql, args);
        LastQuery = sql;
        var needToDispose = false;
        if(db==null)
        {
        db = GetDB();
        needToDispose = true;
        }
    try
    {
    using (var cmd = db.CreateCommand())
    {
     cmd.CommandText = sql;
     cmd.CommandTimeout = TIME_OUT;
     var v = cmd.ExecuteScalar();
     if (v == DBNull.Value) v = null;
     return v;
    }
    }catch(Exception ex)
    {
     logException(logTransaction,sql,ex);
     throw;
    }
    finally
    {
    if (needToDispose)
    db.Dispose();
    }
    }

What `GetValue` method does exactly? Would be better if we see it's definition. And looks like your code is open for _SQL Injection_ attakcs because of this string format (I assume). You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). — Soner Gönül, Mar 13 '14 at 13:01
Would be hard for us to know without seeing the implementation of `GetValue()` — Luc Morin, Mar 13 '14 at 13:01
It's secret code for "I like SQL injection attacks". *Don't do this*. Use parameterized SQL instead. Talk to whoever wrote this query, and show them http://bobby-tables.com — Jon Skeet, Mar 13 '14 at 13:02
Wow, its nice to see all the "answers" based on actually guessing what GetValue does — Luc Morin, Mar 13 '14 at 13:04
@JonSkeet:- I have updated my question with the GetValue() function. Does that make some sense now? Do let me know if I have to provide any further information? — Rahul Tripathi, Mar 13 '14 at 13:10
@user3414693: That's just one method calling a different `GetValue()` method... it doesn't really help you find out what's really going on. I still bet I'm right about SQL injection though. This is really *not* a good way of doing database access, and it should be fixed ASAP. — Jon Skeet, Mar 13 '14 at 13:12
Right, now that you've posted the full code, do you see the `sql = string.Format(sql, args);` call? That's the problem. — Jon Skeet, Mar 13 '14 at 13:22
@JonSkeet:- Yes I have seen that. And thats why I guessed that and also mentioned that in my question above. But I am not sure about it. Could I get some clarification from the legend? — Rahul Tripathi, Mar 13 '14 at 13:27
@user3414693: When you asked the question, you gave no indication that there *was* a `string.Format` call that you'd found. If your question *really* boils down to "What does string.Format do?" then you should really just read the documentation - http://msdn.microsoft.com/en-us/library/system.string.format(v=vs.110).aspx — Jon Skeet, Mar 13 '14 at 13:29
@JonSkeet:- So is it a bad idea to use String.Format in this case and then is it prone to SQL Injection also? — Rahul Tripathi, Mar 13 '14 at 13:51
@user3414693: Yes, when it comes to values. Again, read http://bobby-tables.com — Jon Skeet, Mar 13 '14 at 13:59

score 6 · Answer 1 · answered Mar 13 '14 at 13:02

6

It's related to String.Format(). {0}, {1}, {2} and {3} replaces by values from type, programId, id, aliases

answered Mar 13 '14 at 13:02

MikkaRin

3,026
19
34

score 6 · Accepted Answer · answered Mar 13 '14 at 13:03

Is it something related to String.Format()?

Probably. Those are string formatting placeholders. GetValue most likely generates the actual sql string in a manner similar to:

public object GetValue(string template, params object[] args)
{
    ...
    string sql = string.Format(template, args);
    ...
}

score 2 · Answer 3 · edited May 23 '17 at 10:31

I suppose that your GetValue method replaces the {x} with the value of the parameters passed after the string. This is simply done by using a call to string.Format that is able to replace this kind of placeholders through a tecnique called Composite Formatting

So perhaps your GetValue contains

string sql = string.Format(formatString, listOfParameters)

and is defined as

public int GetValue(string formatString, params object[] listOfParameters)

Then, if the parameters passed are

type = "CUSTOMERS"
programID = 1
id = 2
aliases = "1,2,3"

the inner string format returns a string like

SELECT COUNT(*) FROM CUSTOMERSalias WHERE ProgramID = 1 AND CUSTOMERSID = 2 and ALIAS IN (1,2,3)

Said that, I should add that this is a very bad practice.
It relies on string concatenation to build sql commands.
And this is the main path to Sql Injection. A very serious vulnerability that should be avoided at all costs.

score 0 · Answer 4 · answered Mar 13 '14 at 13:03

0

Those are place holders. the values that come in the comma delimited string afterward fill those positions. So for example.

{0} = Type {1} = ProgramId

etc.

answered Mar 13 '14 at 13:03

sjramsay

555
1
5
12

What is {0},{1},{2},{3} in the SQL query

4 Answers4

Linked