Return Value in Web Method

Question

User1997423929 posted

   [WebMethod]
        public String attendance_shift_details(string pno, string frm_date, string to_date)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["newconn"].ToString();
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select OffDay1,OffDay2,Saturday_Half,ShiftDuty,DivisionName,DepartmentName,SectionName,Intime,Outtime,Shift from App_EM where Pno='"+pno+"'";
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "App_EM");

            if (Convert.ToBoolean(ds.Tables[0].Rows[0]["ShiftDuty"]) == true)
            {
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = "select Shift,Intime,OutTime,DayWeek from App_SM where Pno='"+pno+"' and Date between '"+frm_date+"' and '"+to_date+"' order by Date asc";
                cmd1.Connection = con;


                SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
                DataSet ds1 = new DataSet();
                da1.Fill(ds1, "App_SM");
            }
            else
            {

            }

            return JsonConvert.SerializeObject(ds, Newtonsoft.Json.Formatting.Indented);

            con.Close();
        }

I have two dataset (i.e. ds and ds1). If my "if condition" will true then I want to return ds1 value otherwise it  will return ds value. Pls help me to do this.

Answer 1

User1120430333 posted

Your code is wide open to SQL injection attack.

https://en.wikipedia.org/wiki/SQL_injection

You should be using parametrized inline T-SQL or  parametrized stored procedure to mitigate the attack. 

Also this....

https://www.hanselman.com/blog/ReturningDataSetsFromWebServicesIsTheSpawnOfSatanAndRepresentsAllThatIsTrulyEvilInTheWorld.aspx

https://dzone.com/articles/reasons-move-datatables

https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

Maybe, you could learn how to use ADO.NET,  SQL Command Objects, with parametrized inline T-SQL or sproc, a datareader and the DTO pattern.

https://en.wikipedia.org/wiki/Data_transfer_object

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

Another way to mitigate SQL injection attack is to use Linq.

http://www.ijarcst.com/conference/first/conf83.pdf

https://entityframework.net/linq-prevent-sql-injection

Answer 2

User1997423929 posted

Dear DA924,<br>
<br>
I am a fresher and i don't have that level of knowledge. So can you please tell me by using my code, that how I should make more strong and mitigate the sql injection attacks.

Answer 3

User1120430333 posted

Dear DA924,<br>
<br>
I am a fresher and i don't have that level of knowledge. So can you please tell me by using my code, that how I should make more strong and mitigate the sql injection attacks.

You use parmterized inline t-sql. The example links show you haw to do it.

https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-injection?view=sql-server-2017

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.7.2

Answer 4

User839733648 posted

Hi Adwin Jha,

As DA924 has mentioned you could use parmterized inline t-sql to achieve your requirement.

And I suggest that you could clear the cmd and dataset instead of defining a new one.

For more, you could modifiy your code like below.

        [WebMethod]
        string constr = ConfigurationManager.ConnectionStrings["newconn"].ConnectionString;
        public String attendance_shift_details(string pno, string frm_date, string to_date)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select OffDay1,OffDay2,Saturday_Half,ShiftDuty,DivisionName,DepartmentName,SectionName,Intime,Outtime,Shift from App_EM where Pno='" + pno + "'";
                cmd.Connection = con;
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "App_EM");
                if (Convert.ToBoolean(ds.Tables[0].Rows[0]["ShiftDuty"]) == true)
                {
                    cmd.Parameters.Clear();
                    ds.Clear();
                    cmd.CommandText = "select Shift,Intime,OutTime,DayWeek from App_SM where Pno='" + pno + "' and Date between '" + frm_date + "' and '" + to_date + "' order by Date asc";
                    cmd.Connection = con;
                    da.Fill(ds, "App_SM");
                }
                else
                {
                    cmd.Parameters.Clear();
                    ds.Clear();
                    cmd.CommandText = "xxxxx";
                    cmd.Connection = con;
                    da.Fill(ds, "xxx");
                }
            }
            return JsonConvert.SerializeObject(ds, Newtonsoft.Json.Formatting.Indented);
        }

Best Regards,

Jenifer