Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to handle comma in the text field?

 
Rekha Pai
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

While entering an insert query in pgsql database using JDBC connection,

I get SQL error when I use text for a database field text with commas included.

How can I resolve this issue? Please, help.

Regards,
Rekha

Suppose database table privilege_leave has some fields like (name, fromdate, todate, leavetotal, reason).

The fields are
name varchar(50)
fromdate date
todate date
leavetotal double
reason text

If I write the query taking the html form elements :

insertQuery = " insert into privilege_leave values(\'"+name+"\',\'"+fdate+"\',\'"+tdate+"\',"+total+",\'"+reason+"\')";

If the reason variable contains a comma within, it gives error.

Regards,
Rekha
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does your query look like? Have you enclosed the value in single quotes?
 
hem raj
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
put your text value between single quotes....


eg.. insert into table(fieid) values('some text,text');
 
Rekha Pai
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose database table privilege_leave has some fields like (name, fromdate, todate, leavetotal, reason).

The fields are
name varchar(50)
fromdate date
todate date
leavetotal double
reason text

If I write the query taking the html form elements :

insertQuery = " insert into privilege_leave values(\'"+name+"\',\'"+fdate+"\',\'"+tdate+"\',"+total+",\'"+reason+"\')";

If the reason variable contains a comma within, it gives error.

Regards,
Rekha
 
Sandeep Sanaboyina
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't you use a PreparedStatement. It will solve your problem and it's also a proper way than preparing a string as you did.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sandeep Sanaboyina wrote:Why don't you use a PreparedStatement. It will solve your problem and it's also a proper way than preparing a string as you did.
+1. That is the best advise one can give in this situation.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34371
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should absolutely use a PreparedStatement:



It solves three problems you have:
  • 1) allows commas
  • 2) lets you call stmt.setDate() to insert the dates rather than formatting them in a database specific way
  • 3) Prevents SQL Injection. Entering a comma is what a normal user will do. A hacker will enter ' sql here; ' and try to attack your database. PreparedStatements prevent that too

  •  
    Rekha Pai
    Ranch Hand
    Posts: 35
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi,

    Thanks a lot for your valuable suggestion!!

    Regards,
    Rekha
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic