CODEWITHSUNDEEP
c#sql-server
troy0h
troy0h

Reputation: 13

"System.Data.SqlClient.SqlException: Invalid column name" when checking for username

I'm trying to check a database for a line in a username creation system, to see if the username is in use. Whenever I try to run the program, it tells me there is an invalid column name.

bool tooLong = true;
        conn.Open();
        while (tooLong == true)
        {
            Console.WriteLine("Enter a username:");
            username = Console.ReadLine();
            if (username.Length > 64)
            {
                Console.WriteLine("Username too long");
                tooLong = true;
            }
            else if (username.Length == 0)
            {
                Console.WriteLine("Username cannot be blank");
                tooLong = true;
            }
            else
            {
                SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = {username}", conn);
                if (testUser.ExecuteNonQuery() == -1)
                {
                    tooLong = false;
                    conn.Close();
                    Console.WriteLine("Username accepted");
                }
                else
                {
                    Console.WriteLine("Username already in use");
                }
            }

I have also tried changing

SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = {username}", conn);

to

SqlCommand testUser = new SqlCommand($"SELECT Username FROM Users WHERE Username = '{username}'", conn);

but this just lets everything through instead of throwing the error

Upvotes: 0

Views: 8898

Answers (3)

Nelson
Nelson

Reputation: 179

I would make sure you are not missing the schema in the table name. They are not always a dbo.Users. Try copying the SQL as you have it, paste it in SSMS, and simply replace the {username} with an actual 'userName'.

Also, instead of passing in your variable directly, you should add a parameter to prevent SQL injection. Store your SQL query in a variable (here: sqlQuery). Then, add your UserName as a parameter.

Note: by adding the parameter as below, you will not need to pass in the single quotes.

const string sqlQuery = @"SELECT Username FROM Users WHERE Username = @userName";

var cmd = new SqlCommand(sqlQuery, conn);

cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
cmd.Parameters["@UserName"].Value = userName

Upvotes: 4

derpirscher
derpirscher

Reputation: 17382

You have no quotes ' around your {username} thus, the query results in something like

select username from mytable where username = john

And I don't suppose you have a column named john thus, the Invalid column error. You should use parameterized queries instead.

Futhermore ExecuteNonQuery() for a SELECT will always return -1 because it's defined that way. So you could for instance return the count of matching rows. And if the count is 0, the username was not found.

var command = new SqlCommand("select count(username) from mytable where username = @username", theconnection);
command.Parameters.Add("@username", SqlDbType.NVarchar).Value = username;
var usercount = Convert.ToInt32(command.ExecuteScalar());

if (usercount == 0) {
   //not found
} else {
   //found
}

Upvotes: 1

troy0h
troy0h

Reputation: 13

I've fixed the code, thanks for the help. Changed to

                SqlCommand testUser = new SqlCommand($"SELECT * FROM Users WHERE Username = @userName;", conn);
                testUser.Parameters.Add("@UserName", SqlDbType.VarChar);
                testUser.Parameters["@UserName"].Value = username;
                if (testUser.ExecuteScalar() != null)
                {
                    Console.WriteLine("Username already in use");
                }
                else
                {
                    tooLong = false;
                    conn.Close();
                    Console.WriteLine("Username accepted");
                }
            }

I'm also changing my other queries to use parameters instead.

Upvotes: 1

Related Questions