retseh G retseh G - 4 months ago 7
SQL Question

Really confused about sql injection

Got this detail page about bug that might lead to sql injection

URL encoded GET input classid was set to 1 AND 3*2*1=6 AND 608=608

Tests performed:


  • 1*1*1*1 => TRUE

  • 1*608*603*0 => FALSE

  • 11*5*2*999 => FALSE

  • 1*1*1 => TRUE

  • 1*1*1*1*1*1 => TRUE

  • 11*1*1*0*1*1*608 => FALSE

  • 1 AND 5*4=20 AND 608=608 => TRUE

  • 1 AND 5*4=21 AND 608=608 => FALSE ... (line truncated)



And this is the source code that might cause the matter:

if (!string.IsNullOrEmpty(Request.QueryString["classid"]))
{
string tSql = @" SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award WHERE ClassID={0} ";

DataTable data = DbSession.Default.FromSql(string.Format(tSql, Request.QueryString["classid"])).ToDataTable();

if (data.Rows.Count > 0)
{
rptList.DataSource = data;
rptList.DataBind();
}
}
else
{
string tSql = @" SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award ";

DataTable data = DbSession.Default.FromSql(tSql).ToDataTable();

if (data.Rows.Count > 0)
{
rptList.DataSource = data;
rptList.DataBind();
}
}


Can anyone tell me how to deal with this...thanks a lot!

Now i have modifeid my code to

if (!string.IsNullOrEmpty(Request.QueryString["classid"]))
{
//string tSql = @" SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award WHERE ClassID={0} ";
string tSql = "SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award WHERE ClassID = @ClassID";
//DataTable data = DbSession.Default.FromSql(string.Format(tSql, Request.QueryString["classid"])).ToDataTable();
SqlConnection connection = new SqlConnection("Server=(local);Integrated Security=SSPI;database=DaysQP");
connection.Open();
SqlCommand command = new SqlCommand(tSql, connection);
command.Parameters.Add(new SqlParameter("@ClassId", System.Data.SqlDbType.Int));
command.Parameters["@ClassID"].Value = 1;
using (SqlDataReader dr = command.ExecuteReader())
{
var data = new DataTable();
data.Load(dr);
if (data.Rows.Count > 0)
{
rptList.DataSource = data;
rptList.DataBind();
}
}
connection.Close();
}
else
{
string tSql = @" SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award ";
DataTable data = DbSession.Default.FromSql(tSql).ToDataTable();
if (data.Rows.Count > 0)
{
rptList.DataSource = data;
rptList.DataBind();
}
}


But the problem still exists..

Finally sovled the problem by using parameterized queries!

if (!string.IsNullOrEmpty(Request.QueryString["classid"]))
{
int number;
bool result = Int32.TryParse(Request.QueryString["classid"], out number);

if (result == false)
{
return;
}

//string tSql = @" SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award WHERE ClassID={0} ";
string tSql = "SELECT [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM dbo.Web_Award WHERE ClassID = @ClassID";
//DataTable data = DbSession.Default.FromSql(string.Format(tSql, Request.QueryString["classid"])).ToDataTable();

SqlConnection connection = (SqlConnection)DbSession.Default.CreateConnection();
//SqlConnection("Server=(local);Integrated Security=SSPI;database=DaysQP");
connection.Open();
SqlCommand command = new SqlCommand(tSql, connection);
command.Parameters.Add(new SqlParameter("@ClassId", System.Data.SqlDbType.Int));
command.Parameters["@ClassID"].Value = number;
using (SqlDataReader dr = command.ExecuteReader())
{
var data = new DataTable();
data.Load(dr);
if (data.Rows.Count > 0)
{
rptList.DataSource = data;
rptList.DataBind();
}
}
connection.Close();


}

Answer

The potential for injection would be here:

string tSql = @" SELECT  [Award_ID],[Award_Name],[Award_Info],[Award_Pic],[Award_Num],[Award_MoneyCost],[Award_MoneyGet],[Award_Type],[Award_AddDate],[Award_Hot],[Award_OnLineTime],[AwardProP],[PrizeSlidePic],[PrizeDetailPic],[PrizeBigSlidePic],[IsTop],[ClassID] FROM  dbo.Web_Award WHERE ClassID={0} ";
DataTable data = DbSession.Default.FromSql(string.Format(tSql, Request.QueryString["classid"])).ToDataTable();

You're expecting the query to return Web_Award table records whose classId matches Request.QueryString["classid"]

What happens if the value of Request.QueryString["classid"] is something like:

1 or 1=1

then the query becomes:

select award_id,..... from web_awards where classId=1 or 1=1

and you end up returning data that you never meant to.

This, in essence, is sql injection which you probably read up a bit more about. Using stored procedures or parameterized queries prevents this sort of attack.