C# Hard-coded SQL works but using SqlCommand parameters doesn't

Question

I have some C# code that's being called to generate a string by querying a db. Originally I created the SQL by using string interpolation like the following:

string queryString = $"SELECT * FROM SECTION Where " +
                     $"Product_Code = (SELECT Product_Code FROM PRODUCT WHERE SHORT_NAME = '{productName}') " +
                     $"AND UPPER(description) like '{sectionName}%'";

This code works without issue; sectionName and productName are strings. After having read up on how SQL injection works I decided to change it to using parameters (even though this particular application doesn't need to worry about injection). Here's how I have that setup:

string queryString = "SELECT filename, description FROM SECTION Where " +
                "Product_Code = (SELECT Product_Code FROM PRODUCT WHERE SHORT_NAME = '@ProductCode') " +
                "AND UPPER(description) like '@SectionName%'";

var command = new SqlCommand(queryString, connection);
command.Parameters.Add("@ProductCode", SqlDbType.VarChar).Value = productName;
command.Parameters.Add("@SectionName", SqlDbType.VarChar).Value = sectionName;

This is failing to get any returns. At first I thought the SQL must be different so I looked at the SQL generated from the parameters method by using the follow code:

string query = command.CommandText;

foreach (SqlParameter p in command.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

According to the above code the SQL generated by either method is the same. If I execute the query created by using the parameters method in SSMS I get the proper returns so it appears to be correctly formatted. I would like to use the parameters method simply because it seems like best practice but I can't figure out why it's not getting returns while the hard-coded version is getting returns. What could I be overlooking or not understanding?

In your SQL, '@ProductCode' is a string literal, not a parameter. Remove the quotes. For your like statement use @SectionName + '%' — Glorin Oakenfoot
– Glorin Oakenfoot, Commented Dec 29, 2015 at 22:18
Okay I get it now. I only needed the 's because of interpolation in the first string. Thanks! — wentimo
– wentimo, Commented Dec 29, 2015 at 22:24

meda · Accepted Answer · 2015-12-29 22:26:14Z

2

Here is the correct way:

string queryString = 
@"SELECT filename, description 
FROM SECTION 
Where Product_Code = (
SELECT Product_Code 
FROM PRODUCT 
WHERE SHORT_NAME = @ProductCode
) 
AND UPPER(description) like @SectionName";

var command = new SqlCommand(queryString, connection);
command.Parameters.Add("@ProductCode", SqlDbType.VarChar).Value = productName;
command.Parameters.Add("@SectionName", SqlDbType.VarChar).Value = "%" + sectionName + "%";

answered Dec 29, 2015 at 22:26

meda

45.5k14 gold badges96 silver badges123 bronze badges

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

Comments

mphilipp17 · Accepted Answer · 2015-12-29 22:22:04Z

1

The SQL that gets generated has your variable name in quotes. It should be:

string queryString = "SELECT filename, description FROM SECTION Where " +
            "Product_Code = (SELECT Product_Code FROM PRODUCT WHERE SHORT_NAME = @ProductCode) " +
            "AND UPPER(description) like '%' + @SectionName + '%'";

answered Dec 29, 2015 at 22:22

mphilipp17

313 bronze badges

Collectives™ on Stack Overflow

C# Hard-coded SQL works but using SqlCommand parameters doesn't

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related