Creating Registration form in Visual Studio, getting error in SQL Syntax

Question

Whenever I run the application I keep getting the error:

You have an error in your SQL syntaxt; check the manual that corresponds to your MySql server version for the right sytanx to use near 'User_Info.users where user_name= and password=

My code is:

try
{
    string myConnection = "datasource=127.0.0.1;port=3306;username=root;password=welcome";
    MySqlConnection myConn = new MySqlConnection(myConnection);
    MySqlCommand SelectCommand = new MySqlCommand("select * User_Info.users where user_name=" + this.username_txt.Text + " and password=" + this.password_txt + " ;", myConn);

    MySqlDataReader myReader;
    myConn.Open();
    myReader = SelectCommand.ExecuteReader();
    int count = 0;
    while (myReader.Read())
    {
        count = count + 1;
    }
    if (count == 1)
    {
        MessageBox.Show("Username and password is correct");
    }
    else if (count > 1)
    {
        MessageBox.Show("Duplciate Username and password, access is denied");
    }
    else
        MessageBox.Show("Username and password is incorrect, please try again");
    myConn.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

What causes this error?

Did you look at your SQL syntax? – jamesSampica Apr 29 '14 at 19:58 — jamesSampica, Apr 29 '14 at 19:58

score 1 · Answer 1 · edited May 23 '17 at 12:05

This is possibly due to not having single quotes around your username and password strings.

As such, and provided your c# syntax is correct, your string concatenation could possibly be:

"select * User_Info.users where user_name='" + this.username_txt.Text + "' and password='" + this.password_txt + "';"

When embedding SQL code into your app, it often helps to run an example query directly in the database first, and then translate that working query to your app code. You should then be able to debug to highlight any differences between the two.

Also, without using parameters but simply concatenating strings and user inputs (sanitized or not), you are opening yourself up to SQL Injection attacks. It cannot be stressed enough to refactor your code to use parameters. Doing so will also eliminate the need to provide these string delimiters in your query.

score 0 · Answer 2 · answered Apr 29 '14 at 20:03

Maybe your Strings are empty, or you need to escape the word "name" and "user" in your select, because these are reserved SQL keywords. For mysql the default escape character is

. The SQL standard escape character is

So please change the following line

"select * User_Info.users where user_name="

to

"select * `User_Info`.`users` where `user_name`="

and your query should work fine.

Creating Registration form in Visual Studio, getting error in SQL Syntax

2 Answers2