MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax;

Question

Please help, im trying to build a classroom scheduling system, so the system is almost complete, i can debug the system but whenever i go to set some schedules, it shows this kind of message

MySql.Data.MySqlClient.MySqlException:

'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 ''SELECT subj.sub_code, subj.sub_desc, start_time, end_time, days, item_color, pr' at line 1'

What should I do? Please help. heres the part of the code that im guessing that it has a problem

Dim constring As String = "server=localhost;user id='root';password='!Password123';database = 'scheduling'"
Using con As New MySqlConnection(constring)
    sql = "'SELECT subj.sub_code, subj.sub_desc, start_time, end_time, days, item_color, pr_id, sched.sched_id, sched.course_id FROM tblschedule AS sched INNER JOIN open_subjects AS op_sub ON sched.sub_code = op_sub.sub_code INNER JOIN subject AS subj ON op_sub.sub_code = subj.sub_code WHERE sched.room_id ='" & room_id & "' AND op_sub.semester = '" & school_semester_text & "' AND op_sub.sy = '" & school_year_text & "' AND sched.void = 0 '"
    cmd = New MySqlCommand(sql, con)
    cmd.CommandType = CommandType.Text
    Using sda As New MySqlDataAdapter(cmd)
        Using ds As New DataSet()
            sda.Fill(ds, "tblschedule")
            Using dt As DataTable = ds.Tables("tblschedule")

You have a sql injection issue. Always use parameterized queries instead of string concatenation. Apart from that, why you use '' areound your select statement? — Tim Schmelter
– Tim Schmelter, Commented Jan 29, 2022 at 19:27

Joel Coehoorn · Accepted Answer · 2022-01-31 21:28:29Z

2

Dim constring As String = "server=localhost;user id=root;password=########;database=scheduling"
Dim sql As String = "
SELECT subj.sub_code, subj.sub_desc, start_time, end_time, days, item_color, pr_id, sched.sched_id, sched.course_id 
FROM tblschedule AS sched 
INNER JOIN open_subjects AS op_sub ON sched.sub_code = op_sub.sub_code
INNER JOIN subject AS subj ON op_sub.sub_code = subj.sub_code 
WHERE sched.room_id = @room_id AND op_sub.semester = @semester AND op_sub.sy = @year AND sched.void = 0
"

Dim dt As New DataTabe()
Using con As New MySqlConnection(constring), _
      cmd As New MySqlCommand(sql, con), _
      sda As New MySqlDataAdapter(cmd)

    cmd.Parameters.AddWithValue("@room_id", room_id)
    cmd.Parameters.AddWithValue("@semester", school_semester_text)
    cmd.Parameters.AddWithValue("@year", school_year_text)
    sda.Fill(dt)
End Using

Note how I used the parameters instead of string concatenation. This is how you must do it if you don't want your application to end up horribly hacked.

edited Jan 31, 2022 at 21:28

answered Jan 29, 2022 at 21:14

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andrew Morton Over a year ago

Not forgetting Can we stop using AddWithValue() already?, of course ;)

Joel Coehoorn Over a year ago

@AndrewMorton I don't really use MySql often myself, but I have it on good authority from people I trust that MySql handles parameter binding differently, and it's much less of a concern than with Sql Server.

Andrew Morton Over a year ago

@JoelCoehoorn Thanks, that's interesting information. (If it had been otherwise, I thought you might have taken the opportunity to put quotes around the parameter names.)

Joel Coehoorn Over a year ago

@AndrewMorton Well the quotes around the names was an oversite (now corrected).

Joel Coehoorn Over a year ago

@AndrewMorton I don't have anything handy, sorry.

|

Collectives™ on Stack Overflow

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax;

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related