CODEWITHSUNDEEP
javamysqljdbcprepared-statementsql-injection
Arpit Tomar
Arpit Tomar

Reputation: 175

MySQLSyntaxErrorException: Unknown column 'arpit' in 'field list'

What's the mistake in this JDBC program:

package jdbc;

import java.sql.*;
import java.util.Scanner;

public class Table {

    public static void main(String[] args) {
        String name;
        int age;
        Scanner sc = new Scanner(System.in);
        System.out.println("Enter name and age: ");
        name = sc.nextLine();
        age = sc.nextInt();
        try{
            Connection con = DriverManager.getConnection("jdbc:mysql:" +
                                 "//localhost/demo","root","");
            Statement st = con.createStatement();
            //SQL Query to insert user input
            String sql = "insert into tab values(3,"+name+","+age+");";  
            //Execute INSERT
            st.executeUpdate(sql);
            //Dislplay table "tab"
            ResultSet rs = st.executeQuery("select * from tab");
            while(rs.next()){
                System.out.println(rs.getInt(1) + " " + rs.getString(2)"+
                                   " " " + rs.getInt(3));
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }
}

OUTPUT:

Enter name and age: 
arpit          //user input
18             //user input
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'arpit' in 'field list'
    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:400)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1604)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1535)
    at jdbc.Table.main(Table.java:20)

Upvotes: 0

Views: 136

Answers (1)

Roman C
Roman C

Reputation: 1

Invalid value entered. Use PreparedStatement to set the values in the query. Use column names in the sql which values should be inserted. The primary key is auto generated by default in MySQL, so you don't need to use it. At the end you also don't need to specify the ;. When statement is executed it automatically adds the closing character.

//SQL Query to insert user input
String sql = "insert into tab(name, age) values(?,?)";  
Statement st = con.prepareStatement(sql);

st.setString(1, name);
st.setInt(2, age);

//Execute INSERT
st.executeUpdate();

Upvotes: 4

Related Questions