MySQL INSERT syntax error near '' at line 1 visual c#

Question

Im having a hard time solving this error about MySQL Syntax.

Here is the MySQL syntax

try
        {
            SQL = "INSERT INTO payment_history_tbl (id, payment_type, date, time, amount, student_no) VALUES (NULL, '" + cmbbxPaymentType.Text + "', CURRENT_DATE(), CURRENT_TIME(), '" + txtbxPaymentAmt.Text + "', '" + msktxbxStudNo.Text + "'";
            // INSERT INTO `studentpaymentqueuing`.`payment_history_tbl` (`id`, `payment_type`, `date`, `time`, `amount`, `student_no`) VALUES (NULL, 'Certificate of Enrollment', '2017-02-24', '10:19:28', '60.00', '13-0695');
            cmd = new MySqlCommand(SQL, conn);
            conn.Open();
            cmd.ExecuteNonQuery();

            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Error message:

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 '' at line 1

Any idea?

Your code will certainly fail if someone put " or just '. Use SQL parameter to fix it. — Anirudha Gupta, Feb 23 '17 at 04:17
Here's an idea... the code in this question follows a pattern we frequently see in code that vulnerable to SQL Injection. — spencer7593, Feb 23 '17 at 04:26

score 0 · Accepted Answer · answered Feb 23 '17 at 03:56

0

Did you miss )?

SQL = "INSERT INTO payment_history_tbl (id, payment_type, date, time, amount, student_no) VALUES (NULL, '" + cmbbxPaymentType.Text + "', CURRENT_DATE(), CURRENT_TIME(), '" + msktxbxStudNo.Text + "')";

answered Feb 23 '17 at 03:56

Blank

12,308
1
14
32

Oh! my bad -___- I guess I was so used to `SELECT` statement that has no ")" in the end. Thanks anyway :) – HelpMePlease Feb 23 '17 at 04:01
2

I thought MySql is not completely lost and supports parametrized queries (like http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp), are you sure you are recommending the best practices in the answer? – Alexei Levenkov Feb 23 '17 at 04:07
@AlexeiLevenkov You are absolutly right, here is just an answer, maybe not recommendable, but answer is answer, if answer is wrong, you can tell me then I'll fix it. If you have some other better answers, then you answer here. Thanks for your attention. – Blank Feb 23 '17 at 04:34

score 0 · Answer 2 · answered Feb 23 '17 at 05:24

Using String.Format may help you to get pretty code and finding bug easily.
Example:


String query = String.Empty;
query += String.Format("INSERT INTO payment_history_tbl (id, payment_type, date, time, amount, student_no)");
query += String.Format(" VALUES (NULL, '{0}', '{1}', {2}, '{3}', '{4}');", cmbbxPaymentType.Text, CURRENT_DATE(), CURRENT_TIME(), txtbxPaymentAmt.Text, msktxbxStudNo.Text);

MySQL INSERT syntax error near '' at line 1 visual c#

2 Answers2