C# use string variable in string

[Slickuser]

Hi,

I'm trying to use my variable in another variable. How can I achieve
that?

I can use + (concat) but it will be a lot me to concatenate.

I have to use 3 variables with 15 replacement.

Ex:
C# Code:

string table = "TABLE_AD";
string name = "BOB";
//???
string abc = (@"SELECT EMAIL
FROM {0}
WHERE IDSID='{1}';",table,name);

My final abc variable will be
SELECT EMAIL FROM TABLE_AD WHERE IDSID='BOB'";

In perl is super easy:
$table = "TABLE_AD";
$name = "BOB";
$abc = "SELECT EMAIL FROM $table WHERE IDSID='$name';";

 

[gerry]

string abc = string.Format ( "SELECT EMAIL FROM {0} WHERE IDSID='{1}';" ,
table , name );

 

[Tim Jarvis]

Otherwise, you should definitely use a parameterized query instead.

X 2

 

[Slickuser]

What does "it will be a lot me to concatenate" mean?  Are you concerned 
there's a performance cost?  If so, then I think your concern is  
unwarranted in this particular example.





You could use the String.Format() method.  And in many situations, it'sa  
great way to accomplish what you're asking.  However, your example is  
specifically inserting data into a SQL command string, which is generally 
considered a bad idea, due to the security vulnerabilities it can  
introduce (Google "SQL injection").

I looked at SQL injection:

using (SqlCommand myCommand = new SqlCommand("select * from Users
where UserName=@username and Password=@password", myConnection))
{
myCommand.Parameters.AddWithValue("@username",
user);
myCommand.Parameters.AddWithValue("@password",
pass);

myConnection.Open();
SqlDataReader myReader =
myCommand.ExecuteReader())
...................
}
This work great.

What if I have my table name to be different time I call this
function?
string abc = @"SELECT EMAIL
FROM @TABLE_NAME
WHERE IDSID=@user";

using (SqlCommand myCommand = new SqlCommand(abc, myConnection))
{
myCommand.Parameters.AddWithValue("@user", userName);
myCommand.Parameters.AddWithValue("@TABLE_NAME", tableName);

}
This doesn't get my get database to query because @TABLE_NAME doesn't
pass in.

 

[JTC^..^]

I looked at SQL injection:

using (SqlCommand myCommand = new SqlCommand("select * from Users
where UserName=@username and Password=@password", myConnection))
                {
                    myCommand.Parameters.AddWithValue("@username",
user);
                    myCommand.Parameters.AddWithValue("@password",
pass);

                    myConnection.Open();
                    SqlDataReader myReader =
myCommand.ExecuteReader())
                    ...................
                }
This work great.

What if I have my table name to be different time I call this
function?
string abc = @"SELECT EMAIL
FROM @TABLE_NAME
WHERE IDSID=@user";

using (SqlCommand myCommand = new SqlCommand(abc, myConnection))
                {
myCommand.Parameters.AddWithValue("@user", userName);
myCommand.Parameters.AddWithValue("@TABLE_NAME", tableName);

}

This doesn't get my get database to query because @TABLE_NAME doesn't
pass in.







- Show quoted text -- Hide quoted text -

- Show quoted text -

Use EXEC in your stored Procedure

ALTER PROCEDURE GetEmail
@TABLE_NAME varchar(100),
@user varchar(30)
AS
EXEC('SELECT EMAIL FROM [' + @TABLE_NAME + '] WHERE IDSID = ''' +
@user + '''')

 

[Jon Skeet [C# MVP]]

Use EXEC in your stored Procedure

That just moves SQL injection into the stored proc.

I would recommend using query parameterisation for everything
possible, and then having really, really tight checking where you
can't (e.g. table names) and try to avoid getting into that situation
in the first place.

Jon

 

[Göran Andersson]

What if I have my table name to be different time I call this
function?
string abc = @"SELECT EMAIL
FROM @TABLE_NAME
WHERE IDSID=@user";

using (SqlCommand myCommand = new SqlCommand(abc, myConnection))
{
myCommand.Parameters.AddWithValue("@user", userName);
myCommand.Parameters.AddWithValue("@TABLE_NAME", tableName);

}
This doesn't get my get database to query because @TABLE_NAME doesn't
pass in.

If you need to dynamically set the table name, that suggests that the
database design is wrong. Data should be stored as data in the tables,
not as table names or field names.

Put the data in a single table, and put whatever it is that you now have
as table name into a column in the table.

 

[Marc Gravell]

That just moves SQL injection into the stored proc.

Just for completeness, you could avoid the EXEC injection risk using
(parameterised) sp_ExecuteSQL (to avoid concatenating user data), and
white-listing the table name (the only bit that gets concatenated). But
I'd have to agree that avoiding this in the first place if possible is
desirable; in this example there isn't a lot to choose between a
parameterised SqlCommand and parameterised TSQL (via sp_ExecuteSQL) in
an SP, so I'd go for the simplest, which probably means the first.

Marc

 

[Jeff Johnson]

If you need to dynamically set the table name, that suggests that the
database design is wrong. Data should be stored as data in the tables, not
as table names or field names.

Put the data in a single table, and put whatever it is that you now have
as table name into a column in the table.

That's a beautiful theory, and, in theory, I agree with it 100%.
Unfortunately, I have to work in the real world, not the theory world, and
it has been my experience that sometimes you really need multiple tables,
and sometimes you need to loop over those tables. So while table name
substitution shouldn't be the first choice, it's nearsighted to say that its
usage automatically indicates bad design.