String Interpolation inside an SQL Command

Question

Is there an easier way to use the name and phone variables inside the below insert SQL Command?

String Interpolation is a way but I don't know how to implement this.

String name = textBox1.Text;
String phone = textBox2.Text;  
     
var query = "insert into Customer_info(Customer_Name,Customer_Phone) " +
            "values('" + name + "','" + phone + "');";
SqlCommand com = new SqlCommand(query,con);

try {
    con.Open();
    com.ExecuteNonQuery();
    con.Close();
}

catch (Exception Ex) {
    con.Close();
}

Answer 1

What you really should do is use a parameterised query, so your query would look like this:

var query = "insert into Customer_info(Customer_Name,Customer_Phone)" +
"values(@name, @phone);";

You'd then use a SQLCommand object to pass the parameters to the query:

using (var command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@name", name);
    command.Parameters.AddWithValue("@phone", phone);

    command.ExecuteNonQuery();
}

The reason for this is that it avoids the risk of SQL Injection (which is one of the OWASP Top 10). Consider for a moment your current query if the name passed in contained some SQL, for example if it contained:

'; DROP TABLE [Customer_info]; --

This would mean that your constructed SQL (if phone was blank) would look like this:

insert into Customer_info(Customer_Name,Customer_Phone) values ('';
DROP TABLE [Customer_Info];
-- ','');

This may well result in your Customer_Info table being dropped if the user that the code is connecting to SQL as has sufficient rights to do so.