Parameterized Query in C#

Question

How will I make this not prone to SQL injection? These codes are to insert data to database, and alot says that in this format it is prone to SQL injection.

private void DBConn()
{
    string ConString = "datasource = localhost; Username = root; Password=; database = logbook";
    MySqlConnection DBConnnect = new MySqlConnection(ConString);
    try
    {
        DBConnnect.Open();
        MySqlCommand cmd = DBConnnect.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into tbl_data (Student_Number, Name, Strand, Section) values('"+textBox1.Text+"', '"+textBox2.Text+"', '"+textBox3.Text+"', '"+textBox4.Text+"')";
        cmd.ExecuteNonQuery();
        DBConnnect.Close();
        MessageBox.Show("Student registered successfully");
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }
}

please note, this is an *sql injection attack* waiting to happen, never pass user input into a query like this — TheGeneral, Oct 24 '19 at 02:35
You may wish to consider using Dapper or the like since they make it relatively easy to avoid SQL Injection issues. — mjwills, Oct 24 '19 at 03:03

score 3 · Answer 1 · answered Oct 24 '19 at 02:41

Like this...

private void DBConn()
{
    string ConString = "datasource = localhost; Username = root; Password=; database = logbook";
    MySqlConnection DBConnnect = new MySqlConnection(ConString);
    try
    {
        DBConnnect.Open();
        MySqlCommand cmd = DBConnnect.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into tbl_data (Student_Number, Name, Strand, Section) values(@student_number, @name, @strand, @section)";
        cmd.Parameters.AddWithValue("student_number", textBox1.Text);
        cmd.Parameters.AddWithValue("name", textBox2.Text);
        cmd.Parameters.AddWithValue("strand", textBox3.Text);
        cmd.Parameters.AddWithValue("section", textBox4.Text);
        cmd.ExecuteNonQuery();
        DBConnnect.Close();
        MessageBox.Show("Student registered successfully");
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }
}

But how will I do it with login form? If its not add? "select * FROM tbl_enduser where Username = '" + lgUsername.Text + "' and Password = '" + lgPassword.Text + "' " can that be converted to parameterized one? — Larry, Oct 24 '19 at 03:50
@Larry I'd do it the same way. But one suggestion if you are working with passwords I'd read up on the following.... https://learn.microsoft.com/en-us/sql/t-sql/functions/pwdcompare-transact-sql?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/t-sql/functions/pwdencrypt-transact-sql?view=sql-server-ver15. Do not store unencrypted passwords in a database. — Mick, Oct 25 '19 at 00:06

tymtam · Answer 2 · 2019-10-24T03:13:41.510

The official SqlCommand.Parameters Property has everything you need.

cmd.CommandText = "INSERT INTO tablename(column1,column2,column3) VALUES(@column1,@column2,@column3)

There is some discussion about risks of AddWithValue so you may want to opt for the Add(...).Value approach.

command.Parameters.Add("@column1", SqlDbType.Int).Value = x;
command.Parameters.Add("@column2", SqlDbType.Int).Value = y;
command.Parameters.Add("@column3", SqlDbType.Int).Value = z;

You should be using the using statement.

var commandText = "INSERT INTO tablename(column1,column2,column3) VALUES(@column1,@column2,@column3)

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);

    command.Parameters.Add("@column1", SqlDbType.Int).Value = x;
    command.Parameters.Add("@column2", SqlDbType.Int).Value = y;
    command.Parameters.Add("@column3", SqlDbType.Int).Value = z;
    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Note this question is MySQL, not SQL. – mjwills Oct 24 '19 at 03:03 — mjwills, Oct 24 '19 at 03:03

Parameterized Query in C#

2 Answers2