Error "check the manual that corresponds to your MySQL server version"

Question

Hello I'm having a bit of a problem with my code.. I get this error and it depends on

myReader =  SelectCommand.ExecuteReader();

I really don't understand it.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''password' at line 1

MySqlConnection conn = new MySqlConnection("secretstring")
        MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM userspassword where'" + this.loginuser.Text + "'and password'" + this.passworduser.Text, conn);
        MySqlDataReader myReader;
        conn.Open();
        //myReader = SelectCommand.ExecuteReader();
        myReader =  SelectCommand.ExecuteReader();


        int count = 0;
        while (myReader.Read())
        {
            count = count + 1;
        }
        if (count == 1)
        {
            MessageBox.Show("Correct");
            Form2 pannel = new Form2();
             pannel.Show();
             Hide();

        }
        else if (count > 1)
        {
            MessageBox.Show("More then 1 user logged in");
        }
        else
            MessageBox.Show("Incorrect password or username");
        conn.Close();

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

Missing the field name for the user to compare

 MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM userspassword where " +  
        "username = '" + this.loginuser.Text + "' and password = '" + 
         this.passworduser.Text +"'" , conn);

and also some spaces between the fields and the values and the closing quotes.

Said that let me point you to the danger of string concatenation. If one of your textboxes contains a single quote the command will fail with a syntax error, or, if you have a malicious user, he/she could prepare a Sql Injection attack inserting sql text in the input boxes.

You should write a parameterized query like this

 MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM userspassword where " +  
        "username = @uname and password = @pwd", conn);
 SelectCommand.Parameters.AddWithValue("@uname",this.loginuser.Text); 
 SelectCommand.Parameters.AddWithValue("@pwd",this.passworduser.Text); 
 myReader =  SelectCommand.ExecuteReader();

There is another problem relative to the storage of the password in clear text on the database. Is another thing to avoid from the security point of view. You should store an hash of the password, apply the hash function to the user input text and pass this hash value as parameter to check against the database field

edited May 23 '17 at 10:30

Community

1
1

answered May 17 '13 at 22:34

Steve

213,761
22
232
286

Hey thanks for the quick response, i applied your solution, and i still having the SQL syntax error, this is wierd. Is something wrong with my database scheme? – Patrik Christoffersson May 17 '13 at 22:47
Have you corrected the string 'and password = @pwd' ? Please reload the page – Steve May 17 '13 at 22:52
if you meen adding the + sign, yes. – Patrik Christoffersson May 17 '13 at 22:57
No, I mean the = sign between password and the parameter. The error message is still the same? I mean pointing to the word 'password'? – Steve May 17 '13 at 23:09
I've got it to work, there was something wrong with my database. Thanks for the help! – Patrik Christoffersson May 17 '13 at 23:11

Daniel Flippance · Answer 2 · 2013-05-17T22:39:42.767

0

You have a spacing problem and are missing a column name:

MySqlCommand SelectCommand = 
    new MySqlCommand("SELECT * FROM userspassword where username='" 
                     + this.loginuser.Text + "' and password='" 
                     + this.passworduser.Text +"'", conn);

BTW - You have a potential SQL injection problem here

edited May 17 '13 at 22:39

answered May 17 '13 at 22:33

Daniel Flippance

7,734
5
42
55

score 0 · Answer 3 · edited May 23 '17 at 11:47

You are missing column name in your where clause on your query.

"SELECT * FROM userspassword where'" + this.loginuser.Text
                                   ^^^^^^^ Column name is missing..

Try like;

MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM userspassword where " + "YourColumnName = '" + this.loginuser.Text + "' and password '" + this.passworduser.Text +"'" , conn);

But more important part is, you should always use parameterized queries. This kind of string concatenations are causes SQL Injection attacks.

You can read How does the SQL injection from the "Bobby Tables" XKCD comic work?

Brackets · Answer 4 · 2016-12-28T20:02:47.973

0

This is apparently not the case for the one who asked the question, but for everyone else having this error: make sure you did not name your column with existent SQL keywords. KEY and index are most probable. List of SQL keywords.

edited Dec 28 '16 at 20:02

answered Dec 28 '16 at 19:57

Brackets

464
7
12

Error "check the manual that corresponds to your MySQL server version"

4 Answers4