Re: Ignoring or filtering ' char

[Tiago Serafim]

Hi, 

In your code you are creating the PreparedStatement, but you still
querying the raw sql string

> query = "select * from emaildata where UserEmail =?
> order by EmailDate desc limit 1;";
> ps=con.prepareStatement(query);
> ps.setString(1,UserLogin);
> 
> rs = ps.executeQuery(query); // <<<<------- Here is your error

instead of this line you should use:
rs = ps.executeQuery(); 

Hope I helped, 


On Wed, 4 Aug 2004 03:52:40 -0700 (PDT), Cadbury <codename13th@stripped> wrote:
> gee,thank god,thanx tiago for helping...i'm sorry for
> the late reply..
> 
> it does solve my problem..however one problem occured
> when i try to use the preparedstatement twice.. here's
> what i mean..before that here's a part of my source
> code.
> 
> ----------------------------------------------------
> query = "select status from userinfo where UserEmail=?
> and password =?;";
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1,UserLogin);
> ps.setString(2,UserPassword);
> rs = ps.executeQuery();
> 
> while (rs.next()){
>    st = rs.getString("status");
> }
> 
> query = "select * from emaildata where UserEmail =?
> order by EmailDate desc limit 1;";
> ps=con.prepareStatement(query);
> ps.setString(1,UserLogin);
> 
> rs = ps.executeQuery(query);
> 
> ----------------------------------------------------
> the error msg below will appear:
> ----------------------------------------------------
> Some exception: 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
> '? order by EmailDate desc limit 1' at line 1"
> ----------------------------------------------------
> it seems that my second part of the
> ps=con.prepareStatement(query);
> can't be executed, although the syntax is correct..
> did i miss anything?
> 
> thanks a lot for helping.
> 
> 
> 
> 
> --- Tiago Serafim <tserafim@stripped> wrote:
> 
> > 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
> > <codename13th@stripped> wrote:
> > > hi
> > >
> > > 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.
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > New and Improved Yahoo! Mail - 100MB free storage!
> > > http://promotions.yahoo.com/new_mail
> > >
> > > --
> > > MySQL Windows Mailing List
> > > For list archives: http://lists.mysql.com/win32
> > > To unsubscribe:
> >
> http://lists.mysql.com/win32?unsub=1
> > >
> > >
> >
> >
> > --
> > Tiago Serafim
> > tserafim@stripped
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:
> >
> http://lists.mysql.com/win32?unsub=1
> >
> >
> 
> 
> __________________________________
> Do you Yahoo!?
> Read only the mail you want - Yahoo! Mail SpamGuard.
> 
> 
> http://promotions.yahoo.com/new_mail
> 


-- 
Tiago Serafim
tserafim@stripped