SQL syntax error when inserting new row

Question

Below is my code for when the user clicks Apply Balance. This works for the first part and the user's balance updates just fine but when I try and execute the second statement a SQL syntax errors appears. What may be causing the problem?

public void mouseClicked(MouseEvent e) {
            if (cal == true) {
                try {
                int balchange = updatebal;
                String username = (String) userPicker.getSelectedItem();
                Connection conn = DriverManager.getConnection( Host, Name, Pass );  
                PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal='"+balchange+"' WHERE user_name='"+username+"'");
                pst.execute();


                String sign = "£";
                String PayName = textField_1.getText();
                PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'");
                pst2.execute();
                cal = false;
                } 
                catch (Exception e3) {
                e3.printStackTrace();
                }
            }
            else {
                JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
            }
        }

Below is the stack trace I get when I run this.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at AdminPanelMain$7.mouseClicked(AdminPanelMain.java:444)
at java.awt.Component.processMouseEvent(Component.java:6538)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6300)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4891)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2750)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

May be you can print out the SQL statements before you execute them -- that way any syntax problems will be more visible. Consider using PreparedStatement to supply parameters -- as it is now your code is vulnerable to SQL injection. — mustaccio
– mustaccio, Commented May 10, 2016 at 14:20
You should look at what query it actually generated. This is most likely due to your string-concatenation of a SQL query. You should use prepared statements for all queries that accept parameters. Not only do they prevent situations like this, but they also safeguard your code from SQL Injection. Imagine what would happen if my balance change was 0' -- Or even better, 10000000' -- ;-) — Siyual
– Siyual, Commented May 10, 2016 at 14:21

11thdimension · Accepted Answer · 2016-05-10 14:24:55Z

3

Your second query is missing closing bracket in Values clause.

Instead of directly appending parameters in the query use parameterized queries.

public void mouseClicked(MouseEvent e) {
    if (cal == true) {
        try {
        int balchange = updatebal;
        String username = (String) userPicker.getSelectedItem();
        Connection conn = DriverManager.getConnection( Host, Name, Pass );  
        PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal=? WHERE user_name=?");

        pst.setInt(1, balchange);
        pst.setString(2, username);

        pst.execute();

        String sign = "£";
        String PayName = textField_1.getText();
        PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"
        + " VALUES (?, ?, ?, ?)");

        pst2.setString(1, PayName);
        pst2.setString(2, sign + balchange);
        pst2.setString(3, "Date");//if it's date column use ps2.setDate(3, new Date());
        pst2.setString(4, username);

        pst2.execute();
        cal = false;
        } 
        catch (Exception e3) {
        e3.printStackTrace();
        }
    }
    else {
        JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
    }
}

That will look much cleaner and will be easier to write. Most of all it will save you from SQL Injection attacks.

Here's Oracle Docs for parameterized queries https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

answered May 10, 2016 at 14:24

11thdimension

10.7k4 gold badges40 silver badges74 bronze badges

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

1 Comment

Michael Quinn Over a year ago

Thanks this got it working perfectly, Love the way you lay things out will start using this kind of layout more.

Horsing · Accepted Answer · 2016-05-10 14:27:51Z

2

Look at here:

PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'")

It seems like you lack of right bracket in your sql statement which should be VALUES() not VALUES(.

By the way, there are several ORM systems, such as mybatis or hibernate, why not choose one? They are not only can help you reduce work also makes it easy to access your database.

edited May 10, 2016 at 14:27

answered May 10, 2016 at 14:23

Horsing

1,1107 silver badges26 bronze badges

3 Comments

Michael Quinn Over a year ago

This still causes the SQL Syntax error to appear. Could the problem be linked to me not creating a ID for the Unique ID or should it not matter?

Horsing Over a year ago

Please debug it and post the real sql statement, it shoud be easy to know what is going wrong.

Michael Quinn Over a year ago

The Post above managed to fix my problem but thanks for the help. Only just started using stackoverflow so its all new to me.

Collectives™ on Stack Overflow

SQL syntax error when inserting new row

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related