Java -> MySQL query issue

Question

I am trying to create a java query to insert to MySQl but i keep getting errors. please see the code below. PS the connection to the DB is fine.

here is the query that is being called

public  String newEmpInsert() {
    return newEmpInsert;
}

private String newEmpInsert = "INSERT INTO empInfo"
    + "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
    + "VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+",  "+pin+","+empLevel+", "+contactInfo+")";

here is the handler that is being called from the main

    public void newEmpInsert() {

    // SQL Connection
    Connection conn = null;
    try {
        conn = MySQL_connection_test.getConnection();
        // Create a statement
        Statement statement = conn.createStatement();
        statement.executeQuery(queries.newEmpInsert());

    } 
    catch (SQLException e) {
        // TODO Auto-generated catch block
        //e.printStackTrace();
        System.out.println("--------->>Invalid query!!!!<<--------------");
        System.out.println("Your query has an error, please try again!!");
    }

    // Close the connection
    finally {
        try {
            conn.close();
            System.out.println("Database closed");
        } 
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("Database closed");
    }
}

every time i run a query i am getting the invalid query catch. the variables are being set properly within the class and everything.

Look at the exception. Try adding System.out.printnl(e.getMesasge()). The exceptions almost always include very relevant information. — Jonathon Reinhart
– Jonathon Reinhart, Commented Apr 24, 2014 at 2:09

Pham Thai Thinh · Accepted Answer · 2014-04-24 02:12:24Z

1

Your issue here cause you build wrong sql statement. As i look on your code you missing single quote on text field. Also your approach build Statement not good, it is easy get failure with special character like ' or " and expose for sql inject attach. Try using prepare statement and bind parameters.

answered Apr 24, 2014 at 2:12

Pham Thai Thinh

984 bronze badges

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

Comments

Anura Adhikari · Accepted Answer · 2014-04-24 02:16:28Z

1

Please change third row as VALUES and try to wrap your string values with single quotes.

private String newEmpInsert = "INSERT INTO empInfo"
+ "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
+ " VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", "+pin+", 
+ "+empLevel+", "+contactInfo+")";

edited Apr 24, 2014 at 2:16

answered Apr 24, 2014 at 2:10

Anura Adhikari

3151 silver badge10 bronze badges

4 Comments

John Woo Over a year ago

No, that's not the case. The string literals were not wrapped with single quotes. But that's not the preferred way as it is vulnerable with sql injection. Parameterizing the values would be the best idea.

Ainnera Over a year ago

i had the string literals wrapped in single quotes as well at first just to try different things but that didnt have any effect on the result

Anura Adhikari Over a year ago

Did you try by changing as VALUES

Warren Dew Over a year ago

For MySQL, VALUE and VALUES are interchangeable.

Warren Dew · Accepted Answer · 2014-04-24 02:18:19Z

0

You need to remove the + from the quoted string at the end of the third line and the beginning of the fourth line:

private String newEmpInsert = "INSERT INTO empInfo"
+ "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
+ "VALUE ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+", "+pin+", "
    // added close quote at the end of the above line
+empLevel+", "+contactInfo+")";
    // plus sign and quote deleted at beginning of above line

answered Apr 24, 2014 at 2:18

Warren Dew

8,9383 gold badges34 silver badges44 bronze badges

Comments

ambreen irfan · Accepted Answer · 2021-07-06 07:46:08Z

0

private String newEmpInsert = "INSERT INTO empInfo"
    + "(firstName, lastName, SSN, address, salary, pin, empLevel, contactInfo) "
    + "VALUES ("+firstName+", "+lastName+", "+SSN+", "+address+", "+salary+",  
"+pin+","+empLevel+", "+contactInfo")";

answered Jul 6, 2021 at 7:46

ambreen irfan

1

1 Comment

padaleiana Over a year ago

Your answer is the same as this one by Anura Adhikari, but with less explanation.

Collectives™ on Stack Overflow

Java -> MySQL query issue

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related