CODEWITHSUNDEEP
c#stringbuilder
Darren
Darren

Reputation: 67

StringBuilder.Append issue

Can someone please tell me why the below code adds 2 single quotes('' string '') before and after the string?

    List<string> rolls
    StringBuilder sb = new StringBuilder();

            foreach (var roll in rolls)
                sb.Append("'" + roll + "',");

            string rollList = sb.ToString().TrimEnd(',');

    string sql =
               @"SELECT enrolment_status, roll_number FROM dt_modular_enrolment WHERE id_student = ?
                    AND roll_number in " + "( " + rollList + " )"; 

    creates the below:
    in ( ''ROLL4'',''ROLL6'',''ROLL5'',''ROLL1'',''ROLL2'',''ROLL3'' )

Thanks!

Just to update - there was nothing wrong with the code (apart from the sql injection possibility) it is a sql profiler bug adding extra quotes.

Upvotes: 1

Views: 651

Answers (4)

Vidhya Sagar Reddy
Vidhya Sagar Reddy

Reputation: 1641

Instead of making the mess over there, make use of join method which is used to join all the values in the list to a string with a separator;

Rewrite your code as( if your rolls already contains single quotes)

List<string> rolls
string sql =@"SELECT enrolment_status, roll_number FROM 
dt_modular_enrolment WHERE id_student = ? AND 
roll_number in " + "( " + String.Join(",",rolls) + " )";

Else if your rolls doesn't contain single quotes, add these few lines before sql statement:

for(int i=0;i<rolls.Count;i++)
{
    rolls[i]="'"+ rolls[i]+ "'";
}

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062820

This is actually pretty dangerous from a SQL injection perspective. Unfortunately, IN queries are very awkward to parameterize correctly, since TSQL lacks a "split" function. However, a lot of tools will help you with this. For example, with most LINQ providers, it would be just:

List<string> rolls = ...
int studentId = ...
var query = from row in ctx.ModularEnrolment
            where row.StudentId = studentId
            and rolls.Contains(row.roll_number)
            select new { row.EnrolmentStatus, row.RollNumber };

or with a tool like dapper (which has special handling of in @someParameter when no parentheses are used):

List<string> rolls = ...
int studentId = ...
var rows = connection.Query(@"
SELECT enrolment_status, roll_number FROM dt_modular_enrolment
WHERE id_student = @studentId and roll_number in @rolls",
        new { rolls, studentId });

Upvotes: 3

Nisarg Shah
Nisarg Shah

Reputation: 354

Please check your code with the below code... As you have not mentioned that what was stored in the rolls i have rewritten your code to acheive the targeted result

List<string> rolls = new List<string>();
rolls.Add("ROLL4");//HERE THERE CAN BE A ISSUE
rolls.Add("ROLL6");
rolls.Add("ROLL5");
rolls.Add("ROLL1");
rolls.Add("ROLL2");
rolls.Add("ROLL3");
        StringBuilder sb = new StringBuilder();

        foreach (var roll in rolls)
            sb.Append("'" + roll + "',");

        string rollList = sb.ToString().TrimEnd(',');

        string sql =
                   @"SELECT enrolment_status, roll_number FROM dt_modular_enrolment WHERE id_student = ?
                AND roll_number in " + "( " + rollList + " )";

After writing the above code i am getting the following as my output sql = SELECT enrolment_status, roll_number FROM dt_modular_enrolment WHERE id_student = ? AND roll_number in ( 'ROLL4','ROLL6','ROLL5','ROLL1','ROLL2','ROLL3' ) & rollList = 'ROLL4','ROLL6','ROLL5','ROLL1','ROLL2','ROLL3'

Upvotes: -1

gregjer
gregjer

Reputation: 2843

Make sure that in rolls list strings don't contain already quotes

Upvotes: 2

Related Questions