Insert user defined variables in to the sql statements [duplicate]

Question

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 the city numbers '007' and '017' with variables

     string destcity;
     string tempcityholder1;

What I tried is this

          SqlCommand mybtncmd2 = new SqlCommand("SELECT StraightDist FROM StraightLineDistances WHERE (FirstCity='" + tempcityholder1 + "' AND SecondCity='" + destcity + "');", mybtnconn2);

it didn't give me the expected output.

But when i tried with the original sql as given below it worked.

    SqlCommand mybtncmd2 = new SqlCommand("SELECT StraightDist FROM StraightLineDistances WHERE (FirstCity='007' AND SecondCity='017');", mybtnconn2);

Can anyone point me the error here? or a better solution. This is for a personal application, security is not a must, so no need of parametrized queries. And I don't know how to implement parametrized queries with multiple parameters. If anyone can explain how to use a parametrized query it's great and I would really appreciate that. But just for the time being I need to correct this.

Any help would be great..

OK if with parametrized query

MY Work looks like this

           SqlConnection mybtnconn2 = null;
                SqlDataReader mybtnreader2 = null;
                mybtnconn2 = new SqlConnection("");
                mybtnconn2.Open();

                SqlCommand mybtncmd2 = new SqlCommand("SELECT StraightDist FROM    StraightLineDistances WHERE (FirstCity='007' AND SecondCity='017');", mybtnconn2);



                mybtnreader2 = mybtncmd2.ExecuteReader();
                while (mybtnreader2.Read())
                {
                    MessageBox.Show(mybtnreader2.GetValue(0) + "My btn readre 2 value");

                }

Can anyone give me a solution which doesn't complicate this structure. If I use a parametrized query how can I edit

 mybtnreader2 = mybtncmd2.ExecuteReader();

This statement?

Try this SELECT StraightDist FROM StraightLineDistances WHERE (FirstCity='''' + tempcityholder1 + ''''+ AND +''''SecondCity='''' + destcity + "') — Azar
– Azar, Commented Jul 3, 2014 at 7:39
@rene Please read completely before answering. I don't need a parametrized query I just need to correct this — Sahil
– Sahil, Commented Jul 3, 2014 at 7:49
@Sahil if you have such code, you are wide open to something called SQL Injection attack. Bottom line it means that hackers can easily take control over your database and possibly your whole server. You sure you want this? What rene linked to offers a better way with same results, but protecting against this kind of attack. — user447356
– user447356, Commented Jul 3, 2014 at 7:54
Have it your way, I find it sad that a programmer is not willing to learn. Bye! — user447356
– user447356, Commented Jul 3, 2014 at 8:04
@Sahil the duplicate provides the perfect solution as does the 5 current answers. Don't go down the route you obviously want to follow. — rene
– rene, Commented Jul 3, 2014 at 8:09

st mnmn · Accepted Answer · 2014-07-03 07:51:11Z

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

answered Jul 3, 2014 at 7:51

st mnmn

3,6753 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sahil Over a year ago

I have created my current code like this \ I'm bit confused with your answer.

mooncat69 · Accepted Answer · 2014-07-03 07:53:21Z

0

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");

answered Jul 3, 2014 at 7:53

mooncat69

1559 bronze badges

Comments

ionutioio · Accepted Answer · 2014-07-03 07:51:29Z

0

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

answered Jul 3, 2014 at 7:51

ionutioio

2281 silver badge6 bronze badges

Comments

Complexity · Accepted Answer · 2014-07-03 07:59:16Z

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'; DELETE FROM 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:

Collectives™ on Stack Overflow

Insert user defined variables in to the sql statements [duplicate]

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Related