2018-12-06

/// <summary>
/// Handles the Load event of the Page control.///</summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Gets departmentId from http request.
        string queryString = Request.QueryString["departmentID"];
        if (!string.IsNullOrEmpty(queryString))
        {
            // Gets data from database.
            gdvData.DataSource = GetData(queryString.Trim());

            // Binds data to gridview.
            gdvData.DataBind();
        }
    }
}

private static readonly Regex RegSystemThreats =
        new Regex(@"\s?or\s*|\s?;\s?|\s?drop\s|\s?grant\s|^'|\s?--|\s?union\s|\s?delete\s|\s?truncate\s|" +@"\s?sysobjects\s?|\s?xp_.*?|\s?syslogins\s?|\s?sysremote\s?|\s?sysusers\s?|\s?sysxlogins\s?|\s?sysdatabases\s?|\s?aspnet_.*?|\s?exec\s?",
            RegexOptions.Compiled | RegexOptions.IgnoreCase);

/// <summary>
/// A helper method to attempt to discover [known] SqlInjection attacks.  /// </summary>
/// <param name="whereClause">string of the whereClause to check</param>
/// <returns>true if found, false if not found </returns>public static bool DetectSqlInjection(string whereClause)
{
    return RegSystemThreats.IsMatch(whereClause);
}
/// <summary>
/// A helper method to attempt to discover [known] SqlInjection attacks.  /// </summary>
/// <param name="whereClause">string of the whereClause to check</param>
/// <param name="orderBy">string of the orderBy clause to check</param>
/// <returns>true if found, false if not found </returns>public static bool DetectSqlInjection(string whereClause, string orderBy)
{
    return RegSystemThreats.IsMatch(whereClause) || RegSystemThreats.IsMatch(orderBy);
}

/// <summary>
/// Handles the Load event of the Page control./// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Gets departmentId from http request.
        string queryString = Request.QueryString["jobId"];
        if (!string.IsNullOrEmpty(queryString))
        {
            if (!DetectSqlInjection(queryString) && !DetectSqlInjection(queryString, queryString))
            {
                // Gets data from database.
                gdvData.DataSource = GetData(queryString.Trim());

                // Binds data to gridview.
                gdvData.DataBind();
            }
            else
            {
                throw new Exception("Please enter correct field");
            }
        }
    }
}

-- =============================================
-- Author:        JKhuang
-- Create date: 12/31/2011
-- Description:    Get data from jobs table by specified jobId.
--=============================================
ALTER PROCEDURE [dbo].[GetJobs]
    -- ensure that the id type is int
    @jobId INT
AS
BEGIN--    SET NOCOUNT ON;
    SELECT job_id, job_desc, min_lvl, max_lvl
    FROM dbo.jobs
    WHERE job_id = @jobId
    GRANT EXECUTE ON GetJobs TO pubs END

using (var com = new SqlCommand("GetJobs", con))
{
    // Uses store procedure.
    com.CommandType = CommandType.StoredProcedure;
// Pass jobId to store procedure.
    com.Parameters.Add("@jobId", SqlDbType.Int).Value = jobId;
    com.Connection.Open();
    gdvData.DataSource = com.ExecuteScalar();
    gdvData.DataBind(); 
}

string sql1 = string.Format("SELECT job_id, job_desc, min_lvl, max_lvl FROM jobs WHERE job_id = @jobId");using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))using (var com = new SqlCommand(sql1, con))
{
    // Pass jobId to sql statement.
    com.Parameters.Add("@jobId", SqlDbType.Int).Value = jobId;
    com.Connection.Open();
    gdvData.DataSource = com.ExecuteReader();
    gdvData.DataBind(); 
}

var dc = new pubsDataContext();int result;
// Validates jobId is int or not.if (int.TryParse(jobId, out result))
{
 gdvData.DataSource = dc.jobs.Where(p => p.job_id == result);
    gdvData.DataBind();
}