SQL Invalid Column name detection from Query

Question

I've tried running the code and I have no idea what's wrong with the query. Because it keeps saying invalid column name, when I'm trying to retrieve the data from that column instead. The column name matches the one in the DB. It's well connected because it's connected to a login form where it detects the other given password and name. I'm using based on a search textbox.

private void btnSearch_Click(object sender, EventArgs e)
{   
    SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDetailConnectionString"].ToString());

    try
    {
        cnn.Open();
        SqlCommand command = new SqlCommand();
        command.Connection = cnn;
        string query = "SELECT *FROM AffiliatedRegister WHERE Username=" + txtUser.Text + "";
        command.CommandText = query;
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
          ---[Converting String from db /Insert to textboxes]---
        }
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error" + ex);
    }
}

You should use parametrized queries. But in your current sql-injectable query, you need to use `'` around string values: `"SELECT * FROM AffiliatedRegister WHERE Username='" + txtUser.Text + "'"` — Reza Aghaei, Aug 29 '16 at 17:23
check your database schema and make sure you do not have any typo's could the column name be `UserName vs Username` — MethodMan, Aug 29 '16 at 17:25

score 4 · Answer 1 · edited Aug 29 '16 at 18:56

4

You need to wrap the username text in quotes.

Your emitted sql script is gonna look like:

SELECT *FROM AffiliatedRegister WHERE Username=InputUserName

So SQL is trying to compare the column Username to the column InputUsername.

Once you wrap the user name in quotes, it would be:

SELECT *FROM AffiliatedRegister WHERE Username='InputUserName'

edited Aug 29 '16 at 18:56

marc_s

732,580
175
1,330
1,459

answered Aug 29 '16 at 17:27

rolovargas

41
2

I would recommend also having the OP learn how to construct Parameterized Queries.. – MethodMan Aug 29 '16 at 17:28
Thanks for the advice. Will try to focus more on that area. – Minial Aug 29 '16 at 18:58

Arpit Parmar · Answer 2 · 2016-08-29T17:52:54.857

Well first of all your query is very dangerous so please don't use it for production purpose.

Now what you need to do :-
In your query you need single quotes around the txtUser.Text.
Like this :-

"SELECT *FROM AffiliatedRegister WHERE Username='" + txtUser.Text + "'";

Resulting query : SELECT *FROM AffiliatedRegister WHERE Username = 'txtUser.Text';

You can also put double quotes like :-

...Username=\"" + txtUser.Text + "\"";

its complicated ;-) Earlier one is better for the reading purpose.

Why it did not run?
Because all values except integers must be passed inside single or double quotes in a query. Like :-

SELECT * FROM TABLE_NAME WHERE TABLE_NAME.COLUMN_NAME = "VALUE";

Now one very important thing please don't use these kinds of queries for production purpose. I guess you are in development phase so answering this question is not gonna ruin your life ...!!!

score 1 · Accepted Answer · edited Sep 22 '17 at 02:30

Your statement erred because you did not wrap your string in quotes so Sql interpeted it as on object and not a string. That being said there you should use parameters and not string concatenation.

Use parameters
Wrap your SqlConnection in a using block
You should specify the column order in the SELECT statement, do not use *.
Do not swallow an Exception unless you know how to recover from it

Update code

private void btnSearch_Click(object sender, EventArgs e)
{
    // use ConnectionString property
    // wrap in using block
    using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDetailConnectionString"].ConnectionString))
    {
        try
        {
            SqlCommand command = new SqlCommand();
            command.Connection = cnn;
            // use parameters
            // avoid *, specify columns instead
            string query = "SELECT * FROM AffiliatedRegister WHERE Username= @userName";
            command.CommandText = query;
            // use parameters, I assumed the parameter type and length - it should be updated to the type and length specified in your table schema
            command.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar, 200) {Value = txtUser.Text });

            // open as late as possible
            cnn.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                // ---[Converting String from db / Insert to textboxes]-- -
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex); 

            // do not swallow the exception unless you know how to recover from it
            throw;
        }
    }
}

Thank you, I'll try to practice more on parameters. It solved my problem. — Minial, Aug 29 '16 at 18:58

score 0 · Answer 4 · answered Aug 29 '16 at 17:26

You can try to replace the your string: string query = "SELECT *FROM AffiliatedRegister WHERE Username=" + txtUser.Text + "";

to: string query = "SELECT <yourcolumn> FROM AffiliatedRegister WHERE Username=" + txtUser.Text + "";

I believe it is necessary to specify the column name.

Best regards

SQL Invalid Column name detection from Query

4 Answers4