How to select an item from MySql without id in C# ASP.NET Core

Question

I'm trying to get a user from mysql table with a username instead of id in ASP.Net Core, the default column in GET method is id. I tried to change it to username, but the column is unknown.

In my table, username, password and email are in TEXT type, id in INT type.

My changed code is:

// GET: api/Person/XXX
[HttpGet("{uname}", Name = "Get")]
public Person Get(string uname)
{
    ConnectMysql();

    Person p = new Person();
    string queryString = "SELECT * FROM users WHERE uname = " + uname;

    MySqlCommand cmd = new MySqlCommand(queryString, conn);

    MySqlDataReader myReader = cmd.ExecuteReader();

    if (myReader.Read())
    {
        p.id = (int)myReader["id"];
        p.name = myReader["uname"].ToString();
        p.password = myReader["pword"].ToString();
        p.email = myReader["email"].ToString();

        return p;
    }
    else
    {
        return null;
    }
}

And then I try to get an item in table with calling http://localhost:54203/api/Person/q ('q' is an item exists in the table).

The error is MySql.Data.MySqlException:Unknown column 'q' in 'where clause'

How can I fix it?

Beware your code is prone to SQL injection.

user6572277
– user6572277

2018-03-05 23:04:42 +00:00
Commented Mar 5, 2018 at 23:04 — user6572277
– user6572277, Commented Mar 5, 2018 at 23:04
Obligatory Bobby Tables link

Nyerguds
– Nyerguds

2018-03-05 23:24:15 +00:00
Commented Mar 5, 2018 at 23:24 — Nyerguds
– Nyerguds, Commented Mar 5, 2018 at 23:24

Community · Accepted Answer · 2020-06-20 09:12:55Z

You need single quotes around values, like this:

string queryString = "SELECT * FROM users WHERE uname = '" + uname + "'";

Without them, MySql tries to interpret your uname value as a column name.

BUT DON'T DO THAT!

That code is crazy-vulnerable to sql injection attacks. Do that and your site will hacked before you sign up your first user.

Structure the code more like this:

// GET: api/Person/XXX
[HttpGet("{uname}", Name = "Get")]
public Person Get(string uname)
{
   // Don't manage the conneciton like this.
   // .Net uses connection pooling, where in most cases you really do want
   //   a new connection object for each call to the db
   //  ConnectMysql();

    string queryString = "SELECT * FROM users WHERE uname = @uname";

    using (var conn = new MySqlConnection("connection string here"))
    using (var cmd  = new MySqlCommand(queryString, conn))
    {
        cmd.Parameters.Add("@uname", MySqlDbType.VarChar, 20).Value = uname;
        conn.Open();

        using (var myReader As MySqlDataReader = cmd.ExecuteReader())
        {
            if (myReader.Read())
            {
                return new Person() {
                    id = (int)myReader["id"],
                    name = myReader["uname"].ToString(),
                    password = myReader["pword"].ToString(),
                    email = myReader["email"].ToString()
                };
            }
        }
    }
    return null;
}

And while I'm here, that sure looks like you're storing the password in plain text. In any list of security sins, SQL injection is at the top, and plain-text passwords come soon after.

Mithgroth · Accepted Answer · 2018-03-05 23:12:42Z

0

Try this in your query:

string queryString = "SELECT * FROM users WHERE uname = '" + uname + "'";

Your problem is with MySql query not ASP.NET.

Edit: I strongly encourage you to use Entity Framework and LINQ for your projects, check them out.

If the project isn't that serious or you want to use the old ways instead, you should be parameterize your queries: http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

edited Mar 5, 2018 at 23:12

answered Mar 5, 2018 at 23:05

Mithgroth

1,2122 gold badges15 silver badges25 bronze badges

2 Comments

Camilo Terevinto Over a year ago

I would certainly not say "for any database activity in a .NET environment.". While EF is a beast, it is too much for many projects. You also should be recommending to use parameters instead

Mithgroth Over a year ago

@CamiloTerevinto Aye, exactly. But considering the OP's code is prone to SQL injection, I'm assuming he is a starter. I'm just trying to guide him to the right direction. Not using EF on purpose is somewhat more of an advanced decision. Edited my answer anyway, thank you.

Collectives™ on Stack Overflow

How to select an item from MySql without id in C# ASP.NET Core

2 Answers 2

BUT DON'T DO THAT!

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

BUT DON'T DO THAT!

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related