How to pass textbox paremeters into sql adapter statements?

Question

I've a TextBox where a user will enter a table's name and it'll be passed into the query. The query will pull data from database and display it on the data table.

But I can't seem to do it with SqlAdapter, I always get this error like executescalarwhen I can easily add the paremeters and pass it in.

Incorrect syntax near '@a'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@a'.

Source Error:

Line 58: adapt.SelectCommand.Parameters.AddWithValue("@a", "%" + Selectdb.Text + "%"); Line 59: DataTable dt = new DataTable(); Line 60: adapt.Fill(dt); Line 61:
if (dt.Rows.Count > 0) Line 62: {

protected void btn_selectdb_Click(object sender, EventArgs e)
{
    Label1.Text = "ready";

    con = new SqlConnection(constr);
    con.Open();
    string a = Convert.ToString(Selectdb.Text);
    string cmdstr = "Select userid,username,email,eventname from @a";
    SqlCommand cmd = new SqlCommand(cmdstr, con);

    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    adapt.SelectCommand.Parameters.AddWithValue("@a", "%" + Selectdb.Text + "%");
    DataTable dt = new DataTable();
    adapt.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    con.Close();
}

Not only you are trying to [pass variable as a table name](https://stackoverflow.com/q/2838490/11683), you are also trying to wrap it into `%`s? What is that supposed to do, select from all tables whose name matches the pattern? — GSerg, Apr 25 '19 at 07:56
I initially used this, but I don't think this will work with SQLDataAdapter. I removed the `%` , still getting syntax error `md.Parameters.Add("@a", SqlDbType.NVarChar).Value = Selectdb.Text;` — Visual Dot, Apr 25 '19 at 07:59
The table name and column name in your query has to be static. Please check [this](https://stackoverflow.com/questions/2838490/table-name-as-variable) SO question. — Priyank Panchal, Apr 25 '19 at 08:00
Warning: If untrusted users use your application, very bad things can happen. Check [Bobby tables](http://bobby-tables.com/) — Cleptus, Apr 25 '19 at 08:10
Thank you for this, will be looking more into parameterized queries/sql injection. — Visual Dot, Apr 25 '19 at 08:11

Dmitry Bychenko · Answer 1 · 2019-04-25T08:09:34.617

You can't pass table name as a query parameter (diffrent table names mean different query, not same query with different parameter). However, you can try formatting (or string interpolation):

    //TODO: validate Selectdb.Text: we don't want SQL injection, e.g.
    // Selectdb.Text = "MyTable; delete from OtherTable"
    if (!Regex.IsMatch(Selectdb.Text, "^[A-Za-z0-9_]+$")) {
      //Something wrong with Selectdb.Text - is it a real table?
    }

    using (con = new SqlConnection(constr)) {
      con.Open();

      string cmdstr = 
        $@"select userid,
                  username,
                  email,
                  eventname 
             from [{Selectdb.Text}]"; 

      using (SqlCommand cmd = new SqlCommand(cmdstr, con)) {
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);

        DataTable dt = new DataTable();
        adapt.Fill(dt);

        if (dt.Rows.Count > 0) {
          GridView1.DataSource = dt;
          GridView1.DataBind();
        }
      } 
    }

score -1 · Answer 2 · edited Apr 25 '19 at 09:19

-1

You should not wrap table name in %%. Then it has a chance of working.

In any case, I would simply do

string cmdstr = "Select userid,username,email,eventname from [" + Selectdb.Text + "]";

edited Apr 25 '19 at 09:19

Cleptus

2,738
4
26
28

answered Apr 25 '19 at 08:01

Fabo.sk

116
1
9

4

A warning about SQL Injection would be wise – Cleptus Apr 25 '19 at 08:04
Thank you for this! Solved! – Visual Dot Apr 25 '19 at 08:06

How to pass textbox paremeters into sql adapter statements?

2 Answers2