CODEWITHSUNDEEP
c#sql-server
wentimo
wentimo

Reputation: 491

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

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?

Upvotes: 0

Views: 1538

Answers (2)

meda
meda

Reputation: 45490

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 + "%";

Upvotes: 2

mphilipp17
mphilipp17

Reputation: 31

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 + '%'";

Upvotes: 1

Related Questions