"You have an error in your SQL syntax" when trying to insert data into MySQL

Question

I'm currently having an issue I really can't fix, I've researched on the internet, but the suggestions I find just don't work for me.

I'm trying to make a registration system, just for fun not anything serious but I would gladly want to know what I'm doing wrong, since I could probably use this one day.

EDIT: I got the solution from @Gordon and many other suggestions which will probably be much usefull and i will look into trying all of the suggestions.

Thank you so much =)

And my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    mysqlconn = New MySqlConnection
    mysqlconn.ConnectionString = "server=localhost;userid=root;password=password;database=Mysql"
    Dim Reader As MySqlDataReader

    If TxT_User.Text = "" Or TxT_Pass1.Text = "" Or TxT_Pass2.Text = "" Or TxT_Email.Text = "" Or TxT_SC.Text = "" Then
        MsgBox("You need to fill out the required informations above", MsgBoxStyle.Critical)
    Else
        If TxT_Email.Text.Contains("@") Then
            If TxT_Pass2.Text = TxT_Pass1.Text Then
                Try
                    mysqlconn.Open()
                    Dim Query As String
                    Query = "INSERT INTO syntax.members (Username, Password, Email, Secret Answer) VALUES ('" & TxT_User.Text & "', '" & TxT_Pass1.Text & "', '" & TxT_Email.Text & "', '" & TxT_SC.Text & "')"
                    cmd = New MySqlCommand(Query, mysqlconn)
                    Reader = cmd.ExecuteReader

                    MsgBox("Account created!", MsgBoxStyle.Information)

                    mysqlconn.Close()
                Catch ex As MySqlException
                    MsgBox(ex.Message, MsgBoxStyle.Information)
                Finally
                    mysqlconn.Dispose()
                End Try
            Else
                MsgBox("Your Password doesn't match the retyped version", MsgBoxStyle.Critical)
            End If

        Else
            MsgBox("Your email is not valid, please type a valid email address", MsgBoxStyle.Critical)
        End If
    End If
End Sub

Can you print the contents of `Query` before attempting to execute it and share it here please? — Mureinik, Oct 04 '15 at 14:47
You want the values inside the MySQL ? How do i exactly print the Query information to you? — GuestAnon, Oct 04 '15 at 14:50
While not directly answering your question, I would suggest that before going any further, you immediately get out of the habit of writing VB/SQL code that uses string concatenation. It is a huge security hole and extremely bad practice. Add parameters to your SQL command instead. Not only is this more secure, it will result in more robust code. — Lunster, Oct 04 '15 at 14:51
Can you provide me any link / guide to how i exactly add parameters to my SQL command ? — GuestAnon, Oct 04 '15 at 14:52
@Mureinik https://gyazo.com/0eef165ebbd0df05843dca3af3f39835 — GuestAnon, Oct 04 '15 at 14:54
I quickly googled an answer which should give you the code you need (not for this question, but for avoiding SQL injection attacks): http://stackoverflow.com/a/9234835/4120837 — Lunster, Oct 04 '15 at 14:56
@GuestAnon yup, so Gordon's answer below should do the trick — Mureinik, Oct 04 '15 at 14:57
@GuestAnon Another bad habit to get out of is asking people "can you provide me with links and examples for XYZ?" before having done *any* research on the matter. Search engines and documentation will provide you with links and examples. And while we are at it, please don't post screenshots of errors. That's completely useless to anybody. Please copy and paste errors you get as plain text. — Tomalak, Oct 04 '15 at 14:59
@Tomalak okay mate, i'll keep that in mind. I was just asking them for a guide since i have no idea of what parameters is and what they do, so i'm not sure where to look, i'll probably find some c++ shit on the internet which'll confuse me even more.. And since you guys here are more experienced than me, i thougt i just could ask you, since this is a forum where people help eachother. — GuestAnon, Oct 04 '15 at 15:03
I understand that. But what people will do is: They will go to Google, pick a few links and post them. That's *exactly* what you could do as well. You can search through the documentation for the keywords you are given and you will get an abundance of reading material to familiarize yourself with new topics. This forum *is* here so people help each other. But asking others for links is effectively wasting their time, and that's rude. I hope you can understand the difference. (Also, please use moderate language here.) — Tomalak, Oct 04 '15 at 15:10

score 0 · Answer 1 · answered Oct 04 '15 at 14:51

0

One obvious problem is "Secret Answer" here:

INSERT INTO syntax.members (Username, Password, Email, Secret Answer) . . .

Unfortunately, I don't know which you want:

INSERT INTO syntax.members (Username, Password, Email, `Secret Answer`) . . .
INSERT INTO syntax.members (Username, Password, Email, SecretAnswer) . . .
INSERT INTO syntax.members (Username, Password, Email, Secret, Answer) . . .

Judging from the list of values, it is one of the first two.

answered Oct 04 '15 at 14:51

Gordon Linoff

1,242,037
58
646
786

Well, in my SQL, the columns name = Secret Answer, could it be the "space" that makes the issue? – GuestAnon Oct 04 '15 at 14:55
@GuestAnon . . . The space is the problem. The first option is the right solution. However, it would be better to have column names that don't need to be escaped (generally, only alphanumeric characters and underscore, and not matching MySQL reserved words). – Gordon Linoff Oct 04 '15 at 14:57
Where can i find MySQL reserved words? So i'm sure that i wont do anything stupid again? – GuestAnon Oct 04 '15 at 15:00
@GuestAnon . . . These are words that have special meaning in MySQL syntax. Many are obvious, but the list is at https://dev.mysql.com/doc/refman/5.6/en/keywords.html. Note that the ones with "(R)" are actually reserved. The others are keywords and could be used for column or table names, although I discourage that. – Gordon Linoff Oct 04 '15 at 15:02

"You have an error in your SQL syntax" when trying to insert data into MySQL

1 Answers1