CODEWITHSUNDEEP
c#stringstring-interpolation
user9402741
user9402741

Reputation:

String Interpolation inside an SQL Command

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();
}

Upvotes: 3

Views: 12274

Answers (3)

Rob
Rob

Reputation: 45779

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.

Upvotes: 6

Marc Gravell
Marc Gravell

Reputation: 1063559

Don't do it! Seriously, just don't. String interpolation is not suitable for building SQL. Just use parameters:

var query = @"
insert into Customer_info(Customer_Name,Customer_Phone)
values(@name,@phone);";
//...
cmd.Parameters.AddWithValue("name", name);
cmd.Parameters.AddWithValue("phone", phone);
cmd.ExecuteNonQuery();

Or use a library like dapper (which removes all the messy ADO.NET code for you, like commands, parameters, and readers):

conn.Execute(query, new { name, phone });

Upvotes: 5

Michał Turczyn
Michał Turczyn

Reputation: 37440

To use string interpolation you need to write:

var query = $"insert into Customer_info(Customer_Name,Customer_Phone) values('{name}','{phone}');";

But, of course you are prone to SQL Injection, which you should avoid!

Use SqlCommand.Parameters collection to add parameter and be safe from that thread.

Upvotes: 4

Related Questions