1

How can i prevent these code of getting SQL injected? It's a login system that i'm learning. Here's the code!

            if (!(string.IsNullOrWhiteSpace(textBox1.Text)) && !(string.IsNullOrWhiteSpace(textBox2.Text)))
        {

            MySqlConnection mcon = new MySqlConnection("datasource = 127.0.0.1; port = 3306; username = root; password = ; database = rpgmaster;");

            mcon.Open();

            DataTable table = new DataTable();

            MySqlDataAdapter adapter = new MySqlDataAdapter("Select * From users where Username = '" + textBox2.Text + "' and password = '" + textBox1.Text + "'", mcon);
           
            adapter.Fill(table);

            if (table.Rows.Count <= 0)
            {
                MessageBox.Show("Você não está registrado!");
            }
            else
            {
                MessageBox.Show("Logado com sucesso! ");
            }

            mcon.Close();
        }

Thanks for the help! Really appreciate it!

1
  • I cannot resolve the class MySqlDataAdapter but maybe there is parameterized overloads if the library support common SQL features. Or there are some APIs to add and pass SQL parameters when SQL executed. Commented Jul 4, 2020 at 5:12

4 Answers 4

5

If you're learning, you could perhaps move on from this old low level way of doing data access and use something a bit more modern and easy. Dapper is an example of a library that isn't a huge leap above what you already know but makes your life a lot nicer:

using(var conn = new MySqlConnection("conn str here"){

  var sql = "SELECT count(*) FROM tblUsers WHERE username = @u AND password = @p";
  var prm = new { 
    u = txtUsername.Text,               //give your textboxes better names than textbox2,textbox1!
    p = txtPassword.Text.GetHashCode() //do NOT store plain text passwords!
  };
  bool valid = await conn.QuerySingleAsync<int>(sql, prm) > 0;

  if(valid)
    ... valid login code
  else
    ... invalid login
}

Some notes on this:

  • dapper is a device that you simply give your sql and parameter values to
  • the sql holds @parameters names like @u
  • an anonymous typed object has properties called the same name as the parameter name, with a value, like u = "my username"
  • use async/await when running queries; dapper makes this easy. Avoid jamming your UI up on queries that take 10 seconds to run
  • in this case you only need to ask the db to count the matching records, you don't need to download them all to find out if there are any, so we use QuerySingleAsync<int> which queries a single value of type it, and if it's more than 0, the login was valid
  • never store password in a database in plaintext. Use a one way hashing function like MD5, SHA256 etc, even the lowly string.GetHashCode is better than storing plaintext, particularly because people use the same passwords all the time so anyone breaking into your db (very easy; the password is in the code) will reveal passwords treat people probably use in their banking etc. We can't really be asking, on the one hand, how to prevent a huge security hole like SQL injection, and then on the other hand leave a huge security hole like plaintext passwords ;)
  • always name your textboxes a better name than the default textboxX - it takes seconds and makes your code understandable. If Microsoft called all their class property names like that, then the entire framework would be full of things like myString.Int1 rather than myString.Length and it would be completely unusable
  • life is too short to spend it writing AddWithValue statements; use Dapper, Entity Framework, strongly typed datasets.. Some db management technology that eases the burden of writing that code

Where Dapper makes things really nice for you is its ability to turn objects into queries and vice versa; this above is just a basic count example, but suppose you had a User class:

class User
{
  string Name { get; set; }
  string HashedPassword { get; set; }
  int age {get; set; }
}

And you had a table tblUsers that was similar (column names the same as the property names), then you could query like:

User u = new User() { Name = "someuser" };
User t = await conn.QuerySingleAsync<User>("SELECT Name, HashedPassword, Age FROM tblUsers WHERE Name = @Name", u);

We want to look up all the info of the someuser user, so we make a new User with that Name set (we could also use anonymous type, like the previous example) and nothing else, and we pass that as the parameters argument. Dapper will see the query contains @Name, pull the contents of the Name from the u user that we passed in, and run the query. When the results return it will create a User instance for us, fully populated with all the data from the query

To do this old way we'd have to:

  • have a command,
  • have a connection,
  • add parameters and values,
  • open the connection,
  • run the sql,
  • get a reader,
  • check if the reader had rows,
  • loop over the reader pulling the first row,
  • make a new User,
  • use reader.GetInt/GetString etc to pull the column values out one by one and
  • finally return the new user
  • oh and dispose of all the db stuff, close the connection etc

Writing that code is repetitive, and it is really boring. In computing, when we have something repetitive and boring, that we need to do thousands of times through out life (like serializing to json, calling a webservice, designing a windows UI) we find some way to make the computer do the repetitive boring bit; they do it faster and more accurately than we can. This is exactly what Dapper does; it does away with that boring repetitive and reduces it to a single line where you say what you want back, using what query, with what parameters. And it keeps your UI working:

await x.QueryAsync<type>(query, parameters)

Win. Seek out some Dapper tutorials! (I have no affiliation)

Sign up to request clarification or add additional context in comments.

2 Comments

@marcgravell thanks for correcting my brain delay. Made me realize I'd used the wrong namespace too
Note that if you want to use async/await with MySQL, I'd advise switching from MySql.Data to nuget.org/packages/MySqlConnector (an alternative MySQL ADO.NET library that I wrote). It's a longstanding bug in MySql.Data that all async methods are implemented synchronously: bugs.mysql.com/bug.php?id=70111
2

Try using parameters please see updated sample of your code below:

 if (!(string.IsNullOrWhiteSpace(textBox1.Text)) && !(string.IsNullOrWhiteSpace(textBox2.Text)))
        {

            using (MySqlConnection mcon = new MySqlConnection("datasource = 127.0.0.1; port = 3306; username = root; password = ; database = rpgmaster;"))
            {
                mcon.Open();

                MySqlCommand cmd = new MySqlCommand("Select * from users where username=?username and password=?password", mcon);
                cmd.Parameters.Add(new MySqlParameter("username", textBox2.Text));
                cmd.Parameters.Add(new MySqlParameter("password", textBox1.Text));

                MySqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows == true)
                {
                    MessageBox.Show("Você não está registrado!");
                }
                else
                {
                    MessageBox.Show("Logado com sucesso! ");
                }

            }

        }

1 Comment

Typical parameter format in microsoftland is @name, rather than ?name. The driver might support it but if it supports @ parameters you should probably prefer that form as its use is more widespread / one less thing to remember
0

Use parameters to pass and check their length, Use stored procedure instead of a query in the code. Use columns instead of * in Select. And please make sure you don't store the plain password in the DB

1 Comment

I think we've covered all those already, in some greater depth?!
-1

Use Parameters

using (MySqlConnection mcon = new MySqlConnection(connectionString))
{
        string commandText = "SELECT * FROM users WHERE Username = '@tbxText'"
        SqlCommand command = new SqlCommand(commandText, mcon);
        
        command.Parameters.AddWithValue("@tbxText", textBox2.Text);

}

1 Comment

Parameter names don't go inside strings; all this code will do is search for a user called @tbxText, which is a terrible name for a variable by the way

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.