CODEWITHSUNDEEP
c#asp.netsql-server
user1778175
user1778175

Reputation: 47

How can I create SQL Query which Takes Parameters from URL in asp.net C#

my link is like 

http://localhost/default.aspx?phone=9057897874&order=124556

Here Is my basic Page for passing Parameter In URL from ASP.net

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"     Inherits="WebApplication2._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form method="get" action="default.aspx">

<label>Phone No</label>
<input type="text" name="phone" />
<br />
<label>Order No</label>
<input type="text" name="order" />
<br />
<input type="submit" value="submit" />
<br />
</form>

my c# file where I can store the prameters in Variables 

namespace WebApplication2
 {
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strQuery;

        string phone = Request.QueryString["phone"];
        if (phone != null)
        {
            Response.Write("phone no is ");
            Response.Write(phone);
        }
        else

        {
            Response.Write("You phone number is not correct");
        }

        string order_no = Request.QueryString["order"];
        if (order_no != null)
        {
            Response.Write("Order No is ");
            Response.Write(order_no);
        }
        else
        {
            Response.Write("You Order number is not correct");
        }

//How I can Connect to Mysql Server

        strQuery = "SELECT order_status where orde01=''" + order_no + "'' and phone01=''" + phone + "''";

        Response.Write(strQuery);
}
}

I'm trying to doing something like this but it's only give me whole query as string. I am new on this topic. Any help will be appreciate Thanks

Upvotes: 0

Views: 5759

Answers (2)

Frank Thomas
Frank Thomas

Reputation: 2514

First off, concatenating a sql statement based on input that the user can change, especially when stored as a string is how SQL Injection Vulnerabilities are created. Don't be that guy.

as for tokenalizing your query string, use named parameters. assume this is your query string

?orderid=777&phone=777-777-7777

Response.QueryString["orderid"]

would return '777' and 

Response.QueryString["phone"]

woudl return '777-777-7777'

as for your sql injection issue, you have a couple options. one is a parameterized sql statement, see the C# example here: http://rosettacode.org/wiki/Parametrized_SQL_statement or use a stored procedure with parameters. the least desirable but minimally acceptable option is to regex validate your input parameters strictly, especially killing characters like '=;% -- and a few others.

EDIT: now that I've had some time to work up a sample, check this out. This sample needs to be customized to your database, but its working on my mysql DB with a test table. you will need to install the MySQLConnector pack and add a project reference to 'MySql.Data' before the code will compile correctly.

namespace WebApplication2
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e) {
            //define some regex patterns for validating our data.
            const string PHONEREGEX = @"((\(\d{3}\))|(\d{3}-))\d{3}-\d{4}";
            const string ORDERNUMREGEX = @"\d*";

            bool isValid = true;

            string phone = Request.QueryString["phone"]; //read phone from querystring.

            //validate that arg was provided, and matches our regular expression. this means it contains only numbers and single hyphens
            if(!string.IsNullOrWhiteSpace(phone) && System.Text.RegularExpressions.Regex.IsMatch(phone, PHONEREGEX)){
                Response.Write(HttpUtility.HtmlEncode(string.Format("The phone number is {0}", phone))); //HTML Encode the value before output, to prevent any toxic markup.
            } else {
                Response.Write("Phone number not provided.");
                isValid = false;
            }

            string orderStr = Request.QueryString["order"]; //read ordernum from querystring
            long order = long.MinValue;

            //validate that order was provided and matches the regex meaning it is only numbers. then it parses the value into 'long order'.
            if(!string.IsNullOrWhiteSpace(orderStr) && System.Text.RegularExpressions.Regex.IsMatch(orderStr, ORDERNUMREGEX) && long.TryParse(orderStr, out order)){
                Response.Write(HttpUtility.HtmlEncode(string.Format("The order number is {0}", order))); //use 'long order' instead of orderStr.
            } else {
                Response.Write("Order number not provided.");
                isValid = false;
            }

            //if all arguments are valid, query the DB.
            if (isValid) {
                Response.Write(GetOrderStatus( phone, order));
            }

        }

        private static string GetOrderStatus(string phone, long order) {
            string status = "";

            //create a connection object
            string connstring = "SERVER=<YOUR MYSQL SERVER>;DATABASE=<YOUR DATABASE>;UID=<YOUR USER>;PASSWORD=<YOUR PASSWORD>-";//this is a connection string for mysql. customize it to your needs.
            MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connstring); //put your connection string in this constructor call 

            //create a SQL command object
            using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) { //use a using clause so resources are always released when done.
                cmd.Connection = conn;
                cmd.CommandText = "SELECT `Order_Status` FROM `<YOUR TABLE>` WHERE `Order` = @order AND `Phone` = @phone"; //this needs a From statement 

                //add parameters for your command. they fill in the @order and @phone in the sql statement above. customize these to match the data types in your database.
                cmd.Parameters.Add("order", MySql.Data.MySqlClient.MySqlDbType.Int64,11).Value = order; //do not use @ sign in parameter name
                cmd.Parameters.Add("phone", MySql.Data.MySqlClient.MySqlDbType.VarChar, 50).Value = phone;

                //execute the command, read the results from the query.
                cmd.Connection.Open();
                using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        status = reader.GetString("Order_Status");
                    }
                    cmd.Connection.Close();
                }

            }
            return status;
        }
    }
}

Upvotes: 2

Riaan Walters
Riaan Walters

Reputation: 2676

You should be using 

Request.Form["phone"]
Request.Form["order"]

instead of

Request.QueryString["phone"]
Request.QueryString["order"]

The reason for this is, you are doing a postback and never redirect to a url with those values set as a querystring

However your question title would suggestion your have a url which contains something like

http://yourdomain.com?phone=0123456789&order=17

Upvotes: 0

Related Questions