MySQL Query Glitch

Question

I have this MySQL Query glitch in my VB.NET program. Here is the query in VB.NET:

"SELECT Points FROM user WHERE Username = '" & UsernameText.Text & "';"

It will select the points column from the user table from the Username they entered. When I deploy this it will set the value for points to 0 on their account. What's going wrong? I'm not even inserting anything in.

Answer 1

In MySQl, the fields and tables are denoted by a apostrophe/backtick in this case...

"SELECT `Points` FROM `user` WHERE `Username`= '" & UsernameText.Text & "';"

Check if that works. Hope it helps.

Answer 2

2 possibilities that I can see:

1. Some other code is updating the points value
2. SQL Injection:

If the value in UsernameText.Text is

blah'; update user set points = 0;--

Then the resultant SQL will be:

SELECT Points FROM user where Username = 'blah'; update user set points = 0;--';

Answer 3

I'm not sure exactly why your table is updated by a select query. But I do know you're going about building your query string all wrong. You should do it like this instead:

Using cn As New MySqlConnection("your connection string here"), _
      cmd As New MySqlCommand("SELECT points FROM user WHERE username= @UserName")

    cmd.Parameters.AddWithValue("@UserName", UsernameText.Text)
    cn.Open()

    ''# ExecuteReader or Fill command go here, connection will be automatically closed
    ''# when control falls out of the using block, even if an exception is thrown

End Using

Answer 4

There's no way a SELECT query can change values stored in the database. It's a read-only command. Either the values were already zero, or else the query is only returning zeroes (in spite of what's stored in the database), or else it's returning no rows at all.

I'd guess that your query isn't formed properly, and it's returning no rows.

You probably have a variable in your VB.NET code and its initial value is zero, to be overridden as it reads the result of the SQL query. When the SQL query returns no rows, this variable retains its initial zero value.

So try an experiment: initialize your points variable to -99 or something and try it again. I bet your app will display -99.

Furthermore, I'd assume the data actually in the database has not changed to zero. Only what you display defaults to zero because your SQL query isn't functioning.

When you're debugging dynamic SQL, you need to look at the complete SQL query -- after you interpolate any dynamic variables into it. It's hard to debug when you're staring at a VB.NET string expression that builds an SQL query. It's like troubleshooting your car problems by visiting an automobile factory.

Answer 5

I think this might help for those who are answering this question. How would the MYsql Column be set up for Points in MySQL admin?