Getting exception on select statement

Question

Getting an exception saying: {"Fatal error encountered during command execution."} on the following code:

internal string GetUserRole(string userEmail)
    {
        _query = "SELECT Role FROM RegisteredUsers WHERE Email = @userEmail";
        string role = null;

        using (_msqlCon = new MySqlConnection(_connectionString))
        {
            _msqlCon.Open();

            using (_command = new MySqlCommand(_query, _msqlCon))
            {
                MySqlDataReader reader = _command.ExecuteReader();

                while (reader.Read())
                {
                    if (reader["Email"].Equals(userEmail))
                    {
                        role = reader["Role"].ToString();
                        break;
                    }
                }
            }
        }

        return role;
    }

The innerException says: Parameter @userEmail must be defined. This is the way I usually make a select statement when I have to select from the given parameter, so I guess it's just a minor error. But I have starred myself blind on this code for almost 20 min. What is wrong with my query: _query = "SELECT Role FROM RegisteredUsers WHERE Email = @userEmail";?

Where is @userEmail defined? Why not do this: _query = string.Format("SELECT Role FROM RegisteredUsers WHERE Email = '{0}'", @userEmail); — CoderForHire, Mar 23 '15 at 14:49
possible duplicate of [Using Prepared Statement in C# with Mysql](http://stackoverflow.com/questions/11070434/using-prepared-statement-in-c-sharp-with-mysql) — npinti, Mar 23 '15 at 14:50
@CoderForHire Your solution would be prone to [sql injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. — nvoigt, Mar 23 '15 at 14:53

Lucas Trzesniewski · Answer 1 · 2015-03-23T14:51:45.060

5

Like the error says... The query uses the @userEmail parameter, but you didn't supply a value for this parameter.

Add the following line before ExecuteReader:

_command.Parameters.AddWithValue("@userEmail", userEmail);

edited Mar 23 '15 at 14:51

answered Mar 23 '15 at 14:49

Lucas Trzesniewski

50,214
11
107
158

Agreed, although I think you would just use `userEmail` as the value for the parameter so that it will match the passed-in value. – Danny Mar 23 '15 at 14:51
or better `_command.Parameters.Add("@userEmail", SqlDbType.VarChar).Value = "some@email.com";` since it's better to provide the correct type instead of letting the database guess. ` – Tim Schmelter Mar 23 '15 at 14:51
@Danny indeed, I didn't see the email was in the argument list, thanks. – Lucas Trzesniewski Mar 23 '15 at 14:52

Getting exception on select statement

1 Answers1