using the literal '@' with variable within the string

Question

I am trying to write an SQL statement as string literal with two variables as follows-

String str="abc"; int val=123;
String sql=@"SELECT Column1, Column2 
             FROM Table
             WHERE Column1= '"" + str + ""' AND Column2 > "" + val + ""
             ORDER BY Column1";

But those variables are not treated as variables. Any help?

UPDATE: Added screenshot with order by clause. There are red curly underlines. enter image description here

Those variables are treated as variables? Do you mean treated as strings? — Sayse, Aug 06 '14 at 07:23
change to WHERE Column1= '" + str + "' AND Column2 > " + val; but i would suggest you use WHERE Column1= @str AND Column2 > @val" and then user command parameters. — Peter, Aug 06 '14 at 07:23
@frenchie There's no need to use LINQ for that (although it's possible). Passing user-supplied data through parameters is generally sufficient. — , Aug 06 '14 at 07:27

score 5 · Accepted Answer · edited Jan 16 '17 at 22:31

Personally I would rewrite the query to use parameterized queries. You will likely have better performance under load and you prevent SQL Injection if the string you are entering comes from user enterable values.

String sql=@"SELECT Column1, Column2 
             FROM Table
             WHERE Column1 = @str AND Column2 > @val
             ORDER BY Column1";

using(var connection = new SqlConnection(_connectionString)
using(var command = new SqlCommand(sql, connection)
{
    connection.Open();
    command.Parameters.Add("@str", SqlDbType.NVarChar, Column1MaxTextLength).Value = str;
    command.Parameters.Add("@val", SqlDbType.Int).Value = val;

    using(var reader = command.ExecuteReader())
    {
        //...
    }
}

EDIT: To address your screenshot, you need another @ symbol before the last set of quotes.

String sql=@"SELECT Column1, Column2 
             FROM Table                                       
             WHERE Column1= '" + str + "' AND Column2 > " + val + @"
             ORDER BY Column1";                               //  ^-- You are missing this guy

But I still think you should re-write to use parametrized queries.

score 3 · Answer 2 · edited Aug 06 '14 at 07:41

3

First of all try using parameterized queries to avoid SQL Injection. You may read more here.

using (SqlConnection conn = new SqlConnection(connstring))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Column1, Column2 FROM Table WHERE Column1=@col1 AND Column2 > @col2 ORDER BY Column1, Column2 " , conn))
    {
        cmd.Parameters.AddWithValue("@col1", str);
        cmd.Parameters.AddWithValue("@col2", val);
        using(var reader = cmd.ExecuteReader())
        {
            //...
        }            
    }
}

But to answer your issue:

String str="abc"; 
int val=123;
String sql=@"SELECT Column1, Column2 
             FROM Table
             WHERE Column1= '" + str + "' AND Column2 > " + val + "";

Or you could use String.Format as suggested by Aron

String str="abc"; 
int val=123;
String sql=String.Format(@"SELECT Column1, Column2 
             FROM Table
             WHERE Column1= '{0}' AND Column2 > {1}",str,val);

edited Aug 06 '14 at 07:41

Aron

15,464
3
31
64

answered Aug 06 '14 at 07:23

Giannis Paraskevopoulos

18,261
1
49
69

You don't need to call `ToString()` on `val`. – Romain Verdier Aug 06 '14 at 07:29
If i want to use order by clause at the end? see the updated question. – s.k.paul Aug 06 '14 at 07:30
@SKPaul just add it at the end of your command as you would in a normal query. – Giannis Paraskevopoulos Aug 06 '14 at 07:32
+1 for both explaining where the OP went wrong and showing the better solution, I would like to see the words SQL Injection in your answer somewhere though! – Liath Aug 06 '14 at 07:33
1

I always liked using [this overload](http://msdn.microsoft.com/en-us/library/wbys3e9s%28v=vs.110%29.aspx) of `Add(` instead of `AddWithValue` that way you can be more explicit on the data type of the declared variable. I have gotten [bitten in the past](http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms) where a unexpected cast causes the database not to use the indexes you expected it to use. – Scott Chamberlain Aug 06 '14 at 07:38
1

@ScottChamberlain you mean when `string` gets cast to `nvarchar` and a widening operator is implicitly applied to the the table side of the query plan, resulting in the index being by passed? I am sure that has NEVER happened to me...>_ – Aron Aug 06 '14 at 07:40
@Aron thanks, i edited my answer to also provide this example. – Giannis Paraskevopoulos Aug 06 '14 at 07:41
@ScottChamberlain Thanks for the tip. I won't change my answer though, as you have already covered this on yours. I `stole` your reader part already :-). I had the `ExecuteNonQuery` that would make no sense... – Giannis Paraskevopoulos Aug 06 '14 at 07:43

score 0 · Answer 3 · answered Aug 06 '14 at 07:30

0

        String str = "abc"; int val = 123;
        StringBuilder strbuilder = new StringBuilder();
        strbuilder.Append("SELECT Column1, Column2  FROM Table WHERE Column1=");
        strbuilder.Append("'" + str + "'");
        strbuilder.Append(" AND Column2 >");
        strbuilder.Append("'" + val.ToString() + "'");

user string builder may this helps you.

answered Aug 06 '14 at 07:30

USER87

547
5
3

I don't want to use StringBuilder. That is not my problem. – s.k.paul Aug 06 '14 at 07:32
ok. you can try "Scott Chamberlain" answer.without using sqlinjection. – USER87 Aug 06 '14 at 07:38

using the literal '@' with variable within the string

3 Answers3