correcting concatenation query. C# and MySQL

Question

0.00/5 (No votes)

See more:

I have a table in my data base with 4 columns Specie | Price | Stock | Country. and two drop down list, the first is Country once the country is selected the second drop down Shows the Specie allocated to the country. This all works well.

Problem

I have select index change that shows 2 label's with Price and Specie selected. problem being is that the label is not showing the correct print out of the country selected, I know its the query but I have tried several variations and can not seem to get it to work correct, advise or help will be much appreciated.

Code with query below

C#

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selection_price = DdPetPist.SelectedValue;
        string selection_stock = DdPetPist.SelectedValue;
        string petPrice = string.Empty;
        string available = string.Empty;

        MySqlCommand cd_price = new MySqlCommand(String.Format("SELECT Price FROM Animals WHERE Specie ='{1}'", ddlcountry.Text, selection_price), cs);
        MySqlCommand cd_available = new MySqlCommand(String.Format("SELECT Stock FROM Animals WHERE Specie ='{0}'", ddlcountry.Text, selection_stock), cs);

        cs.Open();
        petPrice = Convert.ToString(cd_price.ExecuteScalar());
        available = Convert.ToString(cd_available.ExecuteScalar());
        cs.Close();

        PetPrice.Text = String.Format("Minimum Donation For A {0}  Is £{1}.", selection_price, petPrice);
        Availble.Text = String.Format("{0}'s Avalible {1} In Your Country.", selection_stock, available);
    }

Posted 25-Nov-13 10:46am

Ben Oats

Updated 25-Nov-13 11:36am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2013-11-25T12:32:00

A critically important addition to Solution 1:

The term "concatenation" immediately makes your approach suspicious. And yes, it is wrong. Actually, you don't use sting concatenation to form your query using some user-supplied data. And this is good, string.Format has little to do with concatenation (with is inefficient when repeated, because strings are totally immutable). But the other thing is totally bad: you should not creates queries from the user input at all. It opens wide doors to the well-known exploit called SQL injection. This is how it works:
http://xkcd.com/327[^] :-).

What to do instead? Well, you need to use parametrized statements: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

Please see my past answers for further explanations:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

Richard C Bishop · Answer 2 · 2013-11-25T11:01:00

Your query does not make sense. The string.Format is not being used correctly. It is a zero index function. In your WHERE clause you are checking if "Specie" is equal to "selection_price". Your SELECT statements should read like this:

MySqlCommand cd_price = new MySqlCommand(String.Format("SELECT Price FROM Animals WHERE Specie ='{0}'", value for specie), cs);
     MySqlCommand cd_available = new MySqlCommand(String.Format("SELECT Stock FROM Animals WHERE Specie ='{0}'", value for specie), cs);

You will need a valid value to check the "Specie" against or change your query to actually check for price in the WHERE clause.

Of course, you should not be concatenation strings into an SQL statement. You should be using parameterized queries.