CODEWITHSUNDEEP
c#sqlvisual-studio-2012
SunAwtCanvas
SunAwtCanvas

Reputation: 1460

SQL String issue

I have this INSERT statement, 

"INSERT INTO [CustomerInfo] (CustomerName, CustomerEmail, CustomerAddress) " +
$"VALUES ('{name}','{email}','{address}')";

So as you can see on the 2nd line, i have added '$'. This version of the code works, but it's not supported in VS2012.

I'm trying to convert this into something like this but I'm having a lot of issues since it's so very complicated .

"INSERT INTO [CustomerInfo] (CustomerName, CustomerEmail, CustomerAddress) " +
"VALUES (''" + name + "', ''" + email + "', ''" + address + "')";

This version above doesn't work. Basically i'm trying to make a query without using the '$' Any ideas?

Upvotes: 0

Views: 194

Answers (2)

SᴇM
SᴇM

Reputation: 7213

You should use parameterized queries, for example if you are using ADO.NET, use this:

. . .
using(SqlConnection connection = new SqlConnection("yourConnectionString"))
{
    SqlCommand cmd = connection.CreateCommand();
    cmd.CommandText = @"INSERT INTO [CustomerInfo] (CustomerName, CustomerEmail, CustomerAddress)
                        VALUES (@Name, @Email, @Address)";
    cmd.Parameters.AddRange(new SqlParameter[]
    {
        new SqlParameter("@Name", name),
        new SqlParameter("@Email", email),
        new SqlParameter("@Address", address)
    });
    connection.Open();
    cmd.ExecuteNonQuery();
}
. . .

other sql provider type examples you can find here.

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131237

Don't do that at all, it exposes you to SQL injection attacks and converions issues. What would a date look like if you tried to pass it using string concatenation? A decimal?

It's actually easier to use parameterized queries :

//Create a SqlCommand that can be reused
SqlCommand _cmdInsert;

var sql="INSERT INTO [CustomerInfo] (CustomerName, CustomerEmail, CustomerAddress) " + 
        "VALUES (@name,@email,@address)";
var cmd=new SqlCommand(cmd);
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 30);
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@address", SqlDbType.NVarChar, 50);
_cmdInsert=cmd;

Later, you can use the command directly by setting a connection and parameter values :

using(var connection=new SqlConnection(theConnectionString)
{
    _cmdInsert.Connection=connection;
    _cmdInsert.Parameters["@name"].Value=someName;
    ...
    connection.Open();
    _cmdInsert.ExecuteNonQuery();
}

Parameterized queries pass the strongly-typed values alongside the query in the RPC call. A DateTime is passed as a DateTime (or the binary equivalent) to the server, not as a string. This way, there are no conversion errors. No matter what the value contains, it's never mixed with the query itself so it isn't executed. Even if address contained '); drop table Users;-- it wouldn't be executed.

Another option is to use a microORM like Dapper. Dapper uses reflection to map parameter names and data properties to create and execute parameterized queries:

var insertStmt="INSERT INTO [CustomerInfo] (CustomerName, CustomerEmail, CustomerAddress) " + 
        "VALUES (@name,@email,@address)";

connection.Execute(insertStmt, new { name=someName,email=someEmail, address=someAddress});

As the project's page shows, you can execute the same query multiple times if you pass an array of parameters :

var myItems=new[] 
            {
                new {name=someName,email=someEmail, address=someAddress}
            };
connection.Execute(insertStmt, myItems);

Upvotes: 3

Related Questions