oracle syntax: select from textbox selected value as table name in asp.net

Question

User can choose view name from dropdownlist whose value is then stored in "TextBoxTable.Text".

<asp:DropDownList ID="ddSelect_table" runat="server" Width="200px"  AutoPostBack="false" AppendDataBoundItems="true" OnSelectedIndexChanged="ddSelect_table_SelectedIndexChanged" >
                          <asp:ListItem Text="SELECT TABLE" Value=""></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_20" Value="VIEW_RES_ALOC_20"></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_30DAYS" Value="VIEW_RES_ALOC_30DAYS"></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_7DAYS" Value="VIEW_RES_ALOC_7DAYS"></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_8DAYS" Value="VIEW_RES_ALOC_8DAYS"></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_TO" Value="VIEW_RES_ALOC_TO"></asp:ListItem>
                          <asp:ListItem Text="VIEW_RES_ALOC_WARINC" Value="VIEW_RES_ALOC_WARINC"></asp:ListItem>
                      </asp:DropDownList>
                      <asp:TextBox ID="TextBoxTable" runat="server"></asp:TextBox>

    protected void ddSelect_table_SelectedIndexChanged  (object sender, EventArgs e)
    {
        TextBoxTable.Text = ddSelect_table.SelectedItem.Value;
    }

What is the syntax to use database table as textbox value? SelectCommand="select * from ',@TextBoxTable,'

using visualstudio 2013 oracle database 11g express edition

David · Accepted Answer · 2016-02-25 13:25:46Z

1

I don't think you can use a parameter for schema object names, only for values. So you'd have to concatenate the actual name. Which means be careful.

Don't use the actual user input in the SQL query string. Instead, have a pre-defined list of possible values which you control in server-side code. (The same list you use to populate the DropDownList would do nicely.) Check if the input matches an item in the list, and if it does then use the item from the list. If it doesn't show an error.

For example, you might have a simple list in your code-behind:

var viewNames = new List<string>
{
    "VIEW_RES_ALOC_20",
    "VIEW_RES_ALOC_30DAYS",
    "VIEW_RES_ALOC_7DAYS",
    "VIEW_RES_ALOC_8DAYS",
    "VIEW_RES_ALOC_TO",
    "VIEW_RES_ALOC_WARINC"
};

Instead of putting your ListItems directly in the markup, just bind the control to that list:

ddSelect_table.DataSource = viewNames;
ddSelect_table.DataBind();

Then when the user selects a value, validate it. Something as simple as:

string viewName;
if (viewNames.Contains(ddSelect_table.SelectedValue))
    viewName = viewNames.Single(n => n.Equals(ddSelect_table.SelectedValue));
else
    // error

Then you'd end up just using the matched value in the query:

var sql = string.Format("SELECT * FROM {0}", viewName);

There's a little bit of paranoia in the code, always using the server-side value instead of the user-supplied value even when the two have already been determined to match. But a little bit of paranoia is good in this case. Maintain the rule of never trusting user input when it comes to SQL injection. As long as the only thing which ever gets executed as SQL code always comes from you and not from the user, you're good.

whose value is then stored in "TextBoxTable.Text"

It's not really clear why you'd be doing that, since the user has already selected the value in one control so it's just duplicating the value in a second control. But the concept is the same. Validate whatever input you're getting from the user. If you're getting the input twice, validate it twice.

answered Feb 25, 2016 at 13:25

David

221k42 gold badges247 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Muhammad Usman Over a year ago

i need to store it at somewhere so that i can retrieve it's value to get table name from which i can show data.

David Over a year ago

@UsmanJaved: That doesn't really make sense, because you can already retrieve the value from the DropDownList. But that's not the point, you can design your system however you like. What matters is that you validate the input before executing it in a SQL query, and only execute strings/code which originate from your code and not from user input.

Muhammad Usman Over a year ago

Thank you David for your reply.Kindly guide me how can i use selected DropDownList value as database view name?

David Over a year ago

@UsmanJaved: That's what this answer describes. Did you try it? Where did you get stuck?

David Over a year ago

@UsmanJaved: Well, there are two things wrong with that. First, you can't use .NET code directly in SQL code like that, you have to build the SQL code as a string to send to the SQL server. Second, you're doing the exact thing that you shouldn't so with user input. Don't use the input directly in the SQL code, that's called a "SQL injection vulnerability" and it allows users to execute whatever code they want on your server. Validate the input against known values and use one of the known values. Try reading through this answer again and see how the code was written.

|

Cansu Arslan · Accepted Answer · 2016-09-01 14:53:50Z

protected void Page_Load(object sender, EventArgs e)
        { 
if(!IsPostBack)
          {
            ddl.Items.Add(new ListItem("---",""));
            ddl.AppendDataBoundItems = true;
            String conStr = ConfigurationManager.ConnectionStrings["ConnectionString3"].ConnectionString;
                string queryStr = "SELECT CAMPAIGN_ID, NAME FROM CAMPAIGN_INFO";
                OracleConnection conn = new OracleConnection(conStr);
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = queryStr;
                cmd.Connection = conn;

                try
                {
                conn.Open();
                    OracleDataReader rdr = cmd.ExecuteReader();


                    while (rdr.Read())
                    {
                        ListItem li = new ListItem();
                        li.Value = rdr["CAMPAIGN_ID"].ToString();
                        li.Text = rdr["CAMPAIGN_ID"].ToString() +" - "+ rdr["NAME"].ToString();

                        ddl.Items.Add(li);
                    }

                }
            catch (Exception)
            {
                conn.Close();
                conn.Dispose();

            }
}

Collectives™ on Stack Overflow

oracle syntax: select from textbox selected value as table name in asp.net

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related