MySQLSyntaxErrorException while using jdbc

Question

MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Name,Enrol no,Department,Mobile No,Email) values(Effie,1051,CE,123456789,121...' at line 1

The above exception is displaying. I don't know what I'm doing wrong, but here is the code:

String Create()
{
    String sql;
    Scanner sc = new Scanner(System.in);
    System.out.println("Enter name:");
    String name = sc.nextLine();
    
    System.out.println("Enter enrl:");
    long enrl = sc.nextInt();
    
    sc.nextLine();
     
    System.out.println("Enter department:");
    String dept = sc.nextLine();
    
    System.out.println("Enter mobile no:");
    String mono = sc.nextLine();
    
    System.out.println("Enter mail:");
    String mail = sc.nextLine();
    
    sql="Insert into stud_details(First Name,Enrol no,Department,Mobile No,Email) values("+name+","+enrl+","+dept+","+mono+","+mail+");";
    return sql; 
}

The above code is written in class "demo" and I've called it in the main class containing PSVM. I cannot find the mistake here.

Probably the cols names are wrong (with spaces) like "First Name" or you must escape the col name. Try to run the sql first from a db client (eg dbeaver). — PeterMmm, Aug 02 '23 at 15:51
My guess is that you need to put quotes around the column name with spaces. https://mariadb.com/kb/en/identifier-names/ — SedJ601, Aug 02 '23 at 15:51
Regular identifiers cannot contain spaces, so `First Name`, `Enrol no`, `Mobile No` are invalid. You need to change them to quoted identifiers (for MySQL and MariaDB, enclose in backticks, for standard SQL as supported by most other databases, enclose in double quotes). Also, **never ever** concatenate user sourced values into a query string like that. It makes you vulnerable to SQL injection. Always use parameterized queries. In fact, you're forgetting to actual quote string values in single quotes, which is another reason for errors. — Mark Rotteveel, Aug 02 '23 at 15:54
@MarkRotteveel I removed the spaces in the column names, but it still gives this error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@gmail.com)' at line 1 — Effie, Aug 02 '23 at 16:02
@MarkRotteveel okay I changed it to backtick, but now I get another exception: MySQLSyntaxErrorException: Unknown column 'Effie' in 'field list' Here "Effie" is the value i wanna enter in the column. — Effie, Aug 02 '23 at 16:07
That is because you're concatenating values into the query string, which you shouldn't, but if you really insist on it, you need to make sure string values, like a name or an email address, are enclosed in single quotes (i.e. `" ... values ('" + name + "', ..."`), or better yet use [`Statement.enquoteLiteral`](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Statement.html#enquoteLiteral(java.lang.String)) to also make sure the value is properly escaped. — Mark Rotteveel, Aug 02 '23 at 16:08
@MarkRotteveel I did that but now there's a new error: MysqlDataTruncation: Data truncation: Out of range value for column 'MobileNo' at row 1 The max length for MobileNo is 11, and I've only entered 10 numbers as a string. Thanks for still helping me !! — Effie, Aug 02 '23 at 16:16
All the errors are solved!! I can finally make my submission file thanks to you!! Thank you so much!!! — Effie, Aug 02 '23 at 16:33

MySQLSyntaxErrorException while using jdbc

0 Answers0