ADO.Net Inline SQL with command parameter issue

[Sudip_inn]

i am trying to fetch data with command parameter to get rid of sql injection. this line causing issue

if (MappedLineItems != null && MappedLineItems.Count > 0)
{
strSQL = strSQL + "AND TblLineItemTemplate.LineItem NOT IN ('@Filter')";
}

i am not getting data.


string strSQL = "SELECT 0 as IsMapped, TblLineItemTemplate.LineItem ";
strSQL = strSQL + "FROM tblSectionLineItemTemplate INNER JOIN TblLineItemTemplate ON tblSectionLineItemTemplate.LineItemID = TblLineItemTemplate.ID ";
strSQL = strSQL + "WHERE tblSectionLineItemTemplate.Active='A' AND TblLineItemTemplate.Action<>'D' ";
strSQL = strSQL + "AND TblLineItemTemplate.TickerID='@TickerID' ";

if (MappedLineItems != null && MappedLineItems.Count > 0)
{
strSQL = strSQL + "AND TblLineItemTemplate.LineItem NOT IN ('@Filter')";
}

SqlConnection objConn = null;
DataSet dsLi = null;
SqlDataAdapter daSection = null;


objConn = new SqlConnection(sConnectionString);
objConn.Open();

SqlCommand command = new SqlCommand(strSQL, objConn);
command.Parameters.Add("@TickerID", System.Data.SqlDbType.VarChar, 50);
command.Parameters.Add("@Filter", System.Data.SqlDbType.NVarChar, 1000);
command.Parameters["@TickerID"].Value = TickerID;
command.Parameters["@Filter"].Value = string.Join<string>("','", MappedLineItems.Select(x => x.LineItem).ToList());

daSection = new SqlDataAdapter(command);
dsLi = new DataSet();
daSection.Fill(dsLi, "tblLi");




string.Join<string>("','", MappedLineItems.Select(x => x.LineItem).ToList())

The above line return data like 'test1','Tes't2','Test,3' see in this data there is single quote and comman in data which causing issue and that is why i use command parameter.

i assume this below line creating problem...how to fix it?

command.Parameters["@Filter"].Value = string.Join<string>("','", MappedLineItems.Select(x => x.LineItem).ToList());


thanks

Continue reading...