MySQL Data NOT Deleting from table VB.NET

Question

Why won't this delete the data in my MySQL database!?

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

 Dim dbCon As MySqlConnection      
 Dim strQuery As String = ""           
 Dim SQLCmd As MySqlCommand            
 Dim DR As MySqlDataReader            
 Try               
 dbCon = New MySqlConnection("Server=Localhost;Database=myusers;Uid=root;Pwd=Mypassword")

        strQuery = "DELETE settings FROM settings WHERE user=" & Me.loginuser.Text
        '* FROM settings WHERE user = "Testuser"'
        SQLCmd = New MySqlCommand(strQuery, dbCon)

        ' OPEN THE DB AND KICKOFF THE QUERY 
        dbCon.Open()

        DR = SQLCmd.ExecuteReader

        While DR.Read
            req1.Text = "" And exlink.Text = ""
        End While

        ' DONE! Close DB
        DR.Close()
        dbCon.Close()
    Catch ex As Exception
        TextBox8.Text = ("Fail" & vbCrLf & vbCrLf & ex.Message)
    End Try

Here is a picture of my database:

enter image description here

Alternatively I could somehow make it replace what is already in the database, in which case please help me with that.

It works when you enter “xyz OR TRUE” in the textbox. In other words: your code suffers from [SQL injection vulnerability](http://en.wikipedia.org/wiki/SQL_injection). — Konrad Rudolph, Mar 23 '13 at 13:57

score 1 · Answer 1 · edited May 23 '17 at 11:43

I will try to change your code in this way

Using con = New MySqlConnection("Server=.....")
     con.Open()
     Dim sqlText = "DELETE * FROM settings WHERE user = @ulogin"
     Using cmd = new MySqlCommand(sqlText, con)
        cmd.Parameters.AddWithValue("@ulogin",  Me.loginuser.Text)
        cmd.ExecuteNonQuery()
     End Using
End Using

First and foremost, do not use string concatenation to create command texts to pass to the database engine. In that way you risk Sql Injections, also, if the user name contains a single quote (i.e. O'Hara) your code will fail with a syntax error (Same problems arise for date formatting, parsing numeric decimals and other globalization issues). Instead a parametrized query like the one in code above will avoid all of these problems.

In a parametrized query, the text of the query doesn't contains the actual value for the search condition or the update or insert data. Instead it contains placeholders ( in our case it is called @ulogin). The correct value to insert at the placeholders position is specified using one or more MySqlParameter added to the Parameters collection of the MySqlCommand. In this case I have used the AddWithValue method that derives the correct datatype directly from the datatype of the value. Because Me.loginuser.Text is a string value then the parameter will be treated as a string value replacing incorrect single quotes and removing extraneus characters usually used to Mount Sql Injections Attacks. The engine will do the rest inserting the correct value at the placeholder at execution time

EDIT: Seeing your comment about the MySql connector used, I will try to update my answer to show a semi-equivalent version for NET 1.

Try
     Dim con As MySqlConnection = New MySqlConnection("Server=.....")
     con.Open()
     Dim sqlText as String = "DELETE * FROM settings WHERE user = @ulogin"
     Dim cmd As MySqlCommand = new MySqlCommand(sqlText, con)
     Dim par As MySqlParameter = New MySqlParameter("@ulogin", MySqlDbType.VarChar)
     par.Value = Me.loginuser.Text
     cmd.Parameters.Add(par)
     cmd.ExecuteNonQuery()
Finally
     con.Close()
End Try

I am not sure if the connector 1.0.10 supports the parameter name with the @ prefix or just the : prefix

I don't get this part: cmd.Parameters.AddWithValue("@ulogin", Me.loginuser.Text) — Tim Rowley, Mar 23 '13 at 13:24
by the way, do not use a MySqlReader to execute INSERT,DELETE and UPDATE statement to the database. The correct way is the ExecuteNonQuery method of the MySqlCommand class — Steve, Mar 23 '13 at 13:37
Error 1 'AddWithValue' is not a member of 'MySql.Data.MySqlClient.MySqlParameterCollection' — Tim Rowley, Mar 23 '13 at 14:33
What version of the MySql.Data.Dll are you using? And what version of the framework? — Steve, Mar 23 '13 at 14:41
Well, that's really old. See docs on MySqlConnector here http://dev.mysql.com/doc/relnotes/connector-net/en/ If I am not wrong that connector has been built for the Framework 1.1 (now we have 4.5) and the latest connector is 5.1.7. I think that you need to upgrade your system (Framwework and connector). This explains why you don't have the AddWithValue method. I will try to update my answer — Steve, Mar 24 '13 at 08:23

score 1 · Answer 2 · answered Mar 23 '13 at 14:15

Try

strQuery = "DELETE FROM settings " _
         & " WHERE user = '" & Me.loginuser.Text & "'"

but as was stated earlier, you should be using parameterized queries. If you had a user named O'Brien then your query (as composed above) would fail because of the embedded single quote. When you use DELETE, you are deleting entire records and you already specify the table name in the FROM clause.

score 0 · Answer 3 · answered Mar 23 '13 at 12:52

0

i dont think you can use double quotes in mysql, i think its single quotes only. try

Query = "DELETE * FROM settings WHERE user = '" & Me.loginuser.Text & "'"

answered Mar 23 '13 at 12:52

bizzehdee

20,289
11
46
76

this may sound like a stupid question, but are you actually executing the query, it doesnt look like it from the code provided – bizzehdee Mar 23 '13 at 13:18
Yes I am, sorry I didn't post the full code. But I have semi solved it. I now get Unknown column 'Testuser' in 'where clause' – Tim Rowley Mar 23 '13 at 13:20
make sure to escape Me.loginuser.Text, that is what is causing unknown column – bizzehdee Mar 23 '13 at 13:28

MySQL Data NOT Deleting from table VB.NET

3 Answers3