I need some advice with mysql

Question

Hello I am trying to do an application in C# very simple it has a search bar(textbox) and a button, my purpose is for eg when I type E1 I want to display the item from MySql. At the moment, when I type E1 I get some kind of error that says unknown column "e1". I will post my code below:

public partial class MainWindow : Window
{
    MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=pass;");
    MySqlCommand cmd;
    MySqlDataReader mdr;

    private void button_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            connection.Open();
            string selectRaspuns = "SELECT * FROM testdb.element WHERE name="+ userInput.Text;
            cmd = new MySqlCommand(selectQuery, connection);
            mdr = cmd.ExecuteReader();

            if (mdr.Read())
            {
                r1.GetDenumire(mdr.GetString("name"));
                r1.GetInformatii(mdr.GetString("info"));

                r1.Show();
            }
            else
            {
                MessageBox.Show("Error");
            }

        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
        }
        finally
        {
            connection.Close();
        }
    }
}

I want for eg when I type E1 to display from my mySql db information's about E1 that is stored in table element column name and info can you point me what am i doing wrong ?Thanks

Always use SQL Parameters – Ňɏssa Pøngjǣrdenlarp Mar 04 '17 at 19:22 — Ňɏssa Pøngjǣrdenlarp, Mar 04 '17 at 19:22

score 0 · Accepted Answer · edited May 23 '17 at 12:00

The problem is in this line of code:

string selectRaspuns = "SELECT * FROM testdb.element WHERE name="+ userInput.Text;

When you concatenate your input of "E1", the result is:

SELECT * FROM testdb.element WHERE name=E1

Since your string is not quoted, MySql interprets it as a column name, thus you get the "invalid column name" error.

At the very minimum, you must add quotes, like so:

string selectRaspuns = "SELECT * FROM testdb.element WHERE name='" +  userInput.Text + "'";

By doing this, your resulting SQL is:

SELECT * FROM testdb.element WHERE name='E1'

and MySql will interpret "E1" as a string, which is what you intend.

That said, creating SQL using string concatenation is a bad practice and can lead to SQL injection vulnerabilities. Once you have it working as desired, I strongly urge you to circle back and replace the string concatenation with parameterized queries.

I need some advice with mysql

1 Answers1