Insert User Defined Variables In To The Sql Statements

Hi this is my query SELECT StraightDist FROM StraightLineDistances WHERE (FirstCity='007' AND SecondCity='017'); How can I pass this in to sql statement? I want to replace

Solution 1:

This is the way to use parametrized queries:

string sqlQuery="SELECT StraightDist FROM StraightLineDistances WHERE (FirstCity= @tempcityholder1 AND SecondCity=@destcity);"

     SqlCommand mybtncmd2 = new SqlCommand(sqlQuery, mybtnconn2);

    mybtncmd2.Parameters.AddWithValue("tempcityholder1", tempcityholder1 );
    mybtncmd2.Parameters.AddWithValue("destcity", destcity);

Solution 2:

It's always good practice to use parameters, for both speed and security. A slight change to the code is all you need:

var mybtncmd2 = new SqlCommand("SELECT StraightDist FROM StraightLineDistances WHERE FirstCity=@City1 AND SecondCity=@City2;", mybtnconn2);
mybtncmd2.Parameters.AddWithValue("@City1", "007");
mybtncmd2.Parameters.AddWithValue("@City2", "017");

Solution 3:

Use prepared statements: it's both easy and secure.

command.CommandText = "INSERT INTO Region (RegionID, RegionDescription) " + "VALUES (@id, @desc)"; SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0); SqlParameter descParam = new SqlParameter("@desc", SqlDbType.Text, 100);

Solution 4:

You really won't do this, because this is an open door to SQL injection. Instead you should use Stored Procedures for that approach.

In case your not familiar with SQL injection, let's make it clear:

Assume that you have a database with a table called 'T_USER' with 10 records in it. A user object has an Id, a Name and a Firstname.

Now, let's write a query that select a user based on it's name.

SELECT*FROM T_USER WHERE Name='Name 1'

If we take that value from C#, this can really take unexpected behaviour.

So, in C# code we will have a query:

string queryVal;
var command = "SELECT * FROM T_USER WHERE Name = '" + queryVal + "'";

As long as the user is nice to your application, there's not a problem. But there's an easy way to retrieve all records in this table.

If our user passes the following string in QueryVal:

demo' OR 'a' = 'a

Then our query would become:

SELECT*FROM T_USER WHERE Name ='demo'OR'a'='a'

Since the second condition is always true, all the records are retrieved from this table. But we can even go further:

If the same user uses the following value in queryVal:

demo'; DELETE FROM T_USER--

The full query becomes:

SELECT*FROM T_USER WHERE Name ='demo'; DELETEFROM T_USER--'

And all our records our gone.

And we can even go further by dropping the table:

queryVal needs to be:

demo'; DROP TABLE T_USER--

I think you get it. For more information google on Sql Injection: