Re: Ignoring or filtering ' char - Tiago Serafim - com.mysql.lists.win32

20 messages in com.mysql.lists.win32Re: Ignoring or filtering ' char

From	Sent On	Attachments
Nick Randell	15 Jul 2004 22:44
Reggie Burnett	19 Jul 2004 14:04
Nick Randell	19 Jul 2004 22:00
East Bay Technologies	20 Jul 2004 01:59
Reggie Burnett	20 Jul 2004 13:59
East Bay Technologies	21 Jul 2004 02:38
East Bay Technologies	23 Jul 2004 10:53
East Bay Technologies	23 Jul 2004 10:53
PF: MySQL	23 Jul 2004 11:07
Danny Willis	23 Jul 2004 14:36
Randy Clamons	23 Jul 2004 15:09
Tata Respecia	23 Jul 2004 20:04
Cadbury	29 Jul 2004 19:46
Danny Willis	29 Jul 2004 20:02
Tiago Serafim	29 Jul 2004 20:05
Petr Vileta	30 Jul 2004 06:09
Tiago Serafim	30 Jul 2004 07:40
Cadbury	04 Aug 2004 03:52
Tiago Serafim	04 Aug 2004 05:28
Cadbury	04 Aug 2004 19:24

Subject:	Re: Ignoring or filtering ' char
From:	Tiago Serafim (tser...@gmail.com)
Date:	07/29/2004 08:05:45 PM
List:	com.mysql.lists.win32

Hi,

Here is a tip for you, try to make all your inserts statements declaring all the fields, like this: insert into tablename (field1, field2) values (value1, value2)

This will avoid lots of troubles when your table structure changes...

In Java, you should avoid to create raw querys, insted use a PreparedStatement, a PreparedStatement use yours mysql connector implemetation to put data in right format....

Here is a example how your code might look:

String sql = "INSERT userinfo (yourFieldNameHere) VALUES (?)";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString(1, emailSubject);

ps.execute();

Look the javadoc for all methods: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

Hope it helps you....

ps:sorry my bad english

Regards,

On Thu, 29 Jul 2004 19:47:10 -0700 (PDT), Cadbury <code...@yahoo.com> wrote:

i'm using Java to write a program that access mysql database. here's a part of the java program source code:

--------------------------------------------------------- query = "insert into userinfo values ('" + emailSubject + "');"; executeUpdate(query);

---------------------------------------------------------

basically what the program does is it will insert an incoming email's subject into a mysql table.

the problem occurs when the subject has the character ' in it. For example if the subject is something like :"You've got a mail",mysql will give an exception which looks something like this:

------------------------------------------------------ java.sql.SQLException: Syntax error or access violation, message from server: " 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 've got

','Fri Jul 30 10:11:04 GMT+08:00 2004')' at line 1"

------------------------------------------------------ from what I can see MySql treats the ' char as part of mysql syntax. how can i overcome this problem? any help are greatly appreciated.

Thanx.