How to pass parameter value In .aspx page to Sql Query?

Question

I am working in ASP.NET C# source, in that I have a process to pass parameter value from an ASPX page.

In the below code I need to pass a.cYear and AssignedDate through ASP.NET controls.

Note : I'm not using .aspx.cs page, I am creating this code in .aspx page.

I am passing like this in .aspx.cs page,

    bolMachineStatus.cyear = Request.Cookies["BCookies"]["SessionFinancialYear"];
    bolMachineStatus.AssignedDate = Calendar2.SelectedDate.ToString();

This is the full source, in this I have passed values directly,

<script runat="server">
    protected void Calendar2_SelectionChanged(object sender, System.EventArgs e)
    {
        SqlConnection strConn = new SqlConnection(ConfigurationManager.ConnectionStrings["BConnection"].ToString());            
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        DataSet ds = new DataSet();
        strConn.Open();

        cmd.CommandText = @"CREATE TABLE #PendingMachineStatus(cMBrnCode NVARCHAR(2),cMBrnName NVARCHAR(25),
                    cYear NVARCHAR(4),nCallNo INT,Date Datetime)
                    INSERT INTO #PendingMachineStatus
                    select c.cMBrnCode,c.cMBrnName,b.cYear,b.nCallNo,
                    MAX(b.StatusDate) as Date
                    from SvCallHead a
                    INNER JOIN MachineStatus b on a.cYear = b.cYear AND a.nCallNo = b.nCallNo
                    INNER JOIN SvMainBrn c on a.cMBrnCode=c.cMBrnCode
                    where b.cYear='**2017**'
                    and a.cCallSuff = 'A' AND a.cCallEnd = 'N' AND  a.cServType = 'A'
                    AND a.cStatFlg  <> 'C'
                    GROUP BY c.cMBrnCode,c.cMBrnName,b.cYear,b.nCallNo

                    CREATE TABLE #MachineStatus(cMBrnCode NVARCHAR(7),cMBrnName NVARCHAR(100),cYear NVARCHAR(4),nCallNo INT,
                    MainStatus varchar(10),Status varchar(10),cSvPrCode NVARCHAR(10),AssignedDate Datetime,
                    IsSMS BIT)
                    INSERT INTO #MachineStatus
                    SELECT a.cMBrnCode,a.cMBrnName,a.cYear,a.nCallNo,b.MainStatus,b.MachineStatus,b.cSvPrCode,b.AssignedDate,b.IsSMS
                    FROM #PendingMachineStatus a
                    INNER JOIN MachineStatus b ON a.nCallNo=b.nCallNo AND a.Date=b.StatusDate

                    select COUNT(*) AS Count,
                    CASE d.cSvPrCode
                    WHEN '0' THEN NULL
                    ELSE d.cSvPrCode
                    END cSvPrCode,
                    CASE d.AssignedDate
                    WHEN '1900-01-01 00:00:00.000' THEN NULL
                    ELSE d.AssignedDate
                    END AS AssignedDate,
                    DAY(d.AssignedDate) AS Day,
                    MONTH(d.AssignedDate) AS Month,
                    f.cSvPrName
                    from SvCallHead a
                    INNER JOIN SvMainBrn b on a.cMBrnCode=b.cMBrnCode
                    LEFT JOIN SvDelrMast c on a.cDelrCode=c.cDelrCode
                    LEFT JOIN #MachineStatus d on a.cYear=d.cYear and a.nCallNo=d.nCallNo
                    LEFT JOIN SvSvPrMast f on d.cSvPrCode=f.cSvPrCode
                    where a.cYear='**2017**' and **d.AssignedDate='2018/03/15'** and a.cCallSuff = 'A' AND a.cCallEnd= 'N'
                    AND a.cServType= 'A' AND a.cStatFlg<> 'C' AND f.cSvPrName<>'NULL'
                    group by d.cSvPrCode,d.AssignedDate,f.cSvPrName
                    order by AssignedDate asc

                    drop table #PendingMachineStatus
                    drop table #MachineStatus";
        da = new SqlDataAdapter(cmd.CommandText, strConn);
        da.Fill(ds);
        strConn.Close();
        if (ds.Tables[0].Rows.Count == 0)
        {
            DataGrid1.Visible = false;
        }
        else
        {
            DataGrid1.Visible = true;
            DataGrid1.DataSource = ds.Tables[0];
            DataGrid1.DataBind();
        }
    }
</script>

score 0 · Answer 1 · answered Mar 19 '18 at 11:29

It would be better if you put that in a stored procedure and make it parameterized. For now, you can look into string.Format to pass the values.

Something like:

           var str = string.Format(@"CREATE TABLE #PendingMachineStatus(cMBrnCode NVARCHAR(2),cMBrnName NVARCHAR(25),
                cYear NVARCHAR(4),nCallNo INT,Date Datetime)
                INSERT INTO #PendingMachineStatus
                select c.cMBrnCode,c.cMBrnName,b.cYear,b.nCallNo,
                MAX(b.StatusDate) as Date
                from SvCallHead a
                INNER JOIN MachineStatus b on a.cYear = b.cYear AND a.nCallNo = b.nCallNo
                INNER JOIN SvMainBrn c on a.cMBrnCode=c.cMBrnCode
                where b.cYear={0}
                and a.cCallSuff = 'A' AND a.cCallEnd = 'N' AND  a.cServType = 'A'
                AND a.cStatFlg  <> 'C'
                GROUP BY c.cMBrnCode,c.cMBrnName,b.cYear,b.nCallNo

                CREATE TABLE #MachineStatus(cMBrnCode NVARCHAR(7),cMBrnName NVARCHAR(100),cYear NVARCHAR(4),nCallNo INT,
                MainStatus varchar(10),Status varchar(10),cSvPrCode NVARCHAR(10),AssignedDate Datetime,
                IsSMS BIT)
                INSERT INTO #MachineStatus
                SELECT a.cMBrnCode,a.cMBrnName,a.cYear,a.nCallNo,b.MainStatus,b.MachineStatus,b.cSvPrCode,b.AssignedDate,b.IsSMS
                FROM #PendingMachineStatus a
                INNER JOIN MachineStatus b ON a.nCallNo=b.nCallNo AND a.Date=b.StatusDate

                select COUNT(*) AS Count,
                CASE d.cSvPrCode
                WHEN '0' THEN NULL
                ELSE d.cSvPrCode
                END cSvPrCode,
                CASE d.AssignedDate
                WHEN '1900-01-01 00:00:00.000' THEN NULL
                ELSE d.AssignedDate
                END AS AssignedDate,
                DAY(d.AssignedDate) AS Day,
                MONTH(d.AssignedDate) AS Month,
                f.cSvPrName
                from SvCallHead a
                INNER JOIN SvMainBrn b on a.cMBrnCode=b.cMBrnCode
                LEFT JOIN SvDelrMast c on a.cDelrCode=c.cDelrCode
                LEFT JOIN #MachineStatus d on a.cYear=d.cYear and a.nCallNo=d.nCallNo
                LEFT JOIN SvSvPrMast f on d.cSvPrCode=f.cSvPrCode
                where a.cYear={0} and d.AssignedDate={1} and a.cCallSuff = 'A' AND a.cCallEnd= 'N'
                AND a.cServType= 'A' AND a.cStatFlg<> 'C' AND f.cSvPrName<>'NULL'
                group by d.cSvPrCode,d.AssignedDate,f.cSvPrName
                order by AssignedDate asc

                drop table #PendingMachineStatus
                drop table #MachineStatus", Request.Cookies["BCookies"]["SessionFinancialYear"], Calendar2.SelectedDate.ToString());

Don't do it like this, this method is vulnerable to SQL INJECTION. Use SqlParameters like in the example: https://www.dotnetperls.com/sqlparameter — Sergiu Muresan, Nov 14 '18 at 14:15

score 0 · Answer 2 · edited Nov 14 '18 at 17:27

You can use SqlParameter

    //
    // The name we are trying to match.
    //
    string dogName = "Fido";
    //
    // Use preset string for connection and open it.
    //
    string connectionString =
        ConsoleApplication1.Properties.Settings.Default.ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        //
        // Description of SQL command:
        // 1. It selects all cells from rows matching the name.
        // 2. It uses LIKE operator because Name is a Text field.
        // 3. @Name must be added as a new SqlParameter.
        //
        using (SqlCommand command = new SqlCommand(
            "SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
        {
            //
            // Add new SqlParameter to the command.
            //
            command.Parameters.Add(new SqlParameter("Name", dogName));
            //
            // Read in the SELECT results.
            //
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int weight = reader.GetInt32(0);
                string name = reader.GetString(1);
                string breed = reader.GetString(2);
                Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}",
                    weight,
                    name,
                    breed);
            }
        }
    }

More details in the example: https://www.dotnetperls.com/sqlparameter

score 0 · Answer 3 · answered Nov 14 '18 at 17:35

This is high risk. SQL Injection is top security risk from OWASP.org for 2017.

You should create a Stored Procedure using the following link: Create a SP
You should then use ADO.Net/Entity Framework to execute the stored procedure through your code. The only thing you should be passing from client to server is through SQL parameters. For ADO use the link: Call a SP using ADO

How to pass parameter value In .aspx page to Sql Query?

3 Answers3