Mysql Date Column Error during insertion from Java

Question

I want to insert a data using JDBC, whenever I execute the program then it shows me some Mysql error:

Insert statement:

String sql = "INSERT into books(name, isbn, author, category, desc, published) VALUES('"+name+"','"+isbn+"','"+author+"','"+category+"', '"+desc+"','"+book_published+"')";

I am trying to convert the string to date here using :

String yr = year.getSelectedItem().toString();
String mn = month.getSelectedItem().toString();
String dy = day.getSelectedItem().toString();
String book_date = yr+"-"+mn+"-"+dy;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd",
                                            Locale.ENGLISH);
try{
Date book_published = df.parse(book_date);
}catch(...){...}

and it shows me error like :

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 'desc, published) VALUES('skd flakj','klsdjf askj','kl jasdklfj kl','kls djfklj f' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Please help me figure out what is the problem here.

Does one of your values have a stray single quote? You would avoid a whole lot of trouble by using parameter binding instead of concatenating the values directly in your SQL string. — sstan
– sstan, Commented Dec 14, 2015 at 3:16

Naruto · Accepted Answer · 2015-12-14 03:26:58Z

3

Your code is prone to SQL injection attacks due to use of normal query statements. To secure your query use preparedstatement.

As per your query issue, DESC is a reserved word. So you can't use it as column name.View this for complete list of reserved words.

answered Dec 14, 2015 at 3:26

Naruto

4,3591 gold badge24 silver badges32 bronze badges

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

Comments

herokingsley · Accepted Answer · 2015-12-14 04:05:28Z

1

desc is a reserved word for MySQL, which means u can just use it plainly.

To use it without getting an error from MySQL, u should use ` surround the reserved word.

Ps: u SQL statement may suffer from SQL injection if using user-inputed parameters, attackers can use it to get control of ur system.Maybe u should try to hv a look on this one.

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

answered Dec 14, 2015 at 4:05

herokingsley

4033 silver badges10 bronze badges

Comments

Josh Rumbut · Accepted Answer · 2015-12-14 04:47:48Z

0

desc is not a particularly good column name because it's a reserved word in MySQL. I'm not sure if this is the only issue here but you may want to try surrounding desc with ticks, like so:

String sql = "INSERT into books(`name`, `isbn`, `author`, `category`, `desc`, `published`) VALUES('"+name+"','"+isbn+"','"+author+"','"+category+"', '"+desc+"','"+book_published+"')";

It's good practice anyway.

Edit: and as others have mentioned, prepared statements are safer when saving untrusted input to a database.

edited Dec 14, 2015 at 4:47

answered Dec 14, 2015 at 3:21

Josh Rumbut

2,7102 gold badges34 silver badges43 bronze badges

1 Comment

Uraz Pokharel Over a year ago

thanx @josh i got it :)

Collectives™ on Stack Overflow

Mysql Date Column Error during insertion from Java

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related