How to handle comma in the text field?

[Rekha Pai]

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]

What does your query look like? Have you enclosed the value in single quotes?

[hem raj]

put your text value between single quotes....


eg.. insert into table(fieid) values('some text,text');

[Rekha Pai]

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]

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]

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]

You should absolutely use a PreparedStatement:

insert into privilege_leave values(?, ?, ?, ?, ?)

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]

Hi,

Thanks a lot for your valuable suggestion!!

Regards,
Rekha