Incorrect syntax to use near ' postcode ='n' ' using java

Question

I was trying to use java to insert some data into mysql database, but it kept giving syntax error:

Exception in thread "main" 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 ' postcode ='n'' at line 1

here is my code:

`

public static void saveAddress(Address address) throws SQLException{
        SQLConnection sql = new 
        SQLConnection(SQLConnection.getConnection());
        if(SQLAddress.getAddressID(address) == -1){
            sql.update(String.format("INSERT INTO address(houseNumber, 
                street, district, city, postcode) VALUES (%s ,%s ,%s ,%s 
                ,%s)",address.getHouseNumber(),address.getStreet(),
                address.getDistrict(),address.getCity(),
                address.getPostCode()));
        }
        sql.close();
    }

`

I really don't know what to do, i used the same syntax in mysql shell, it worked perfect there.

Edit

public static void saveAddress(Address address) throws SQLException{
    Connection sql = SQLConnection.getConnection();
    if(SQLAddress.getAddressID(address) == -1){
        PreparedStatement ps = sql.prepareStatement("INSERT INTO address (houseNumber, street, district, city, postcode) VALUES (?, ?, ?, ?, ?);");
        ps.setString(1, address.getHouseNumber());
        ps.setString(2, address.getStreet());
        ps.setString(3, address.getDistrict());
        ps.setString(4, address.getCity());
        ps.setString(5, address.getPostCode());
        ps.executeUpdate();
        sql.commit();
    }
    sql.close();
}

I have tried using single quote on values inside the VALUES parenthesis, wont work as well. — Zilong Tian, Nov 18 '17 at 22:02
For this to work, you need `'` characters around each `%s`, so that SQL knows they're literals. But you shouldn't be doing it this way. You need to learn about `PreparedStatement`, so you can protect yourself from SQL injection attacks. — Dawood ibn Kareem, Nov 18 '17 at 22:06
@petey full error is more than 600 characters, so i cant post it in the comment, how do i do it then ? — Zilong Tian, Nov 18 '17 at 22:07
@DawoodibnKareem thanks i will have a look at the PrepareStatement. Can you please explain what SQL injection is to me briefly ? — Zilong Tian, Nov 18 '17 at 22:11
@ZilongTian There is _so much_ information about SQL injection available on the Internet, that it would be a complete waste of my time to add more here. — Dawood ibn Kareem, Nov 18 '17 at 22:12

Sergey Kalinichenko · Accepted Answer · 2017-11-18T22:17:49.737

You should be happy that you ran into this error, because if it didn't happen, you'd create a huge security problem. Inserting user values into SQL string enables SQL injection attacks, which is a very dangerous possibility.

Switch to using prepared statements, and bind parameters to them. This is similar to the way you did with formatting, but it's done on separate lines:

PreparedStatement upd = sql.prepareStatement("INSERT INTO address(houseNumber, street, district, city, postcode) VALUES (?, ?, ?, ?, ?)");
upd.setInt(1, address.getHouseNumber());
upd.setString(2, address.getStreet());
... // Set other fields
upd.executeUpdate();
sql.commit();

Incorrect syntax to use near ' postcode ='n' ' using java

Edit

1 Answers1