inserting into access database apostrophe issue

Question

I am trying to get some information of a website into an access database. I am creating a bean out of the information I get of the website and then send that bean into the database. the problem is that I've been blocked getting certain things into the database by an exception. the exception -

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: (token) required:

so ive checked whats in common with the values that are generating this exception. its an apostrophe. Every value that is calling an exception has an apostrophe in it and I can't really change it for now. so I've been wondering how do I make ucanaccess get that apostrophe into access without any exceptions?

this is the query statement

stmnt = conn.prepareStatement("INSERT INTO Table1(doctorName , description , specialty1 , specialty2 , personalSite , clinic1 , phone1 , clinic2 , phone2 , clinic3, phone3 ,worksWith) VALUES (?,?,?,?,?,?,?,?,?,?,?,?");  

            stmnt.setString(1,tempBean.getDoctorName());
            stmnt.setString(2,tempBean.getDescription());
            stmnt.setString(3,tempBean.getSpeciality1());
            stmnt.setString(4,tempBean.getSpeciality2());
            stmnt.setString(5,tempBean.getPersonalSite());
            stmnt.setString(6,tempBean.getClinic1());
            stmnt.setString(7,tempBean.getPhone1());
            stmnt.setString(8,tempBean.getClinic2());
            stmnt.setString(9,tempBean.getPhone2());
            stmnt.setString(10,tempBean.getClinic3());
            stmnt.setString(11,tempBean.getPhone3());
            stmnt.setString(12,tempBean.getWorksWith());

            stmnt.executeUpdate();

are you inserting this using a PreparedStatement? – Nathan Hughes Mar 17 '15 at 21:08 — Nathan Hughes, Mar 17 '15 at 21:08
no. stmnt.ExecuteUpdate("INSERT INTO.........."); – Bar Hoshen Mar 17 '15 at 21:36 — Bar Hoshen, Mar 17 '15 at 21:36

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

You are experiencing a common problem known as sql injection.

With Java the way to ensure this does not happen is to use PreparedStatements. Take a look at this answer from another question for a good example.

edited May 23 '17 at 11:56

Community

1
1

answered Mar 17 '15 at 21:37

Appak

442
2
9

Can you update your question with your INSERT statement? – Appak Mar 17 '15 at 22:13
Your Insert statement seems to be missing a closing ) was that a typo or is it in the code? – Appak Mar 17 '15 at 22:22
should i delete this post out of shame? well ive ran over the previous code , the one without the prepared statement and it didnt had any typos. so it was an injection all along. thank you very much! – Bar Hoshen Mar 17 '15 at 22:31
still dosent work haha but atleast its another exception! net.ucanaccess.jdbc.UcanaccessSQLException: data exception: string data, right truncation – Bar Hoshen Mar 17 '15 at 22:49
I am guessing but that might mean that the string with the apostrophe is now longer than the max length allowed by the column. – Appak Mar 17 '15 at 22:58

jamadei · Answer 2 · 2015-03-18T09:29:15.947

For future readers:

You should use PreparedStatement to avoid SQLInjection and to avoid the need TO ESCAPE textual values. In SQL(with all dbms' and all drivers), in this specific case, you should escape the apostrophe with another apostrophe:

the word xxx'xxx should be ESCAPED into xxx''xxx.

Because UCanAccess supports Access syntax, the same thing is valid when the double quote " delimeters are used instead of the apostrophe (in this case a double quote in the middle of the word must be escaped with another double quote ").
If not, the sql engine wouldn't able to know if an apostrophe is a text delimeter or it's part of the word. But if you use PreparedStatement, you don't have to worry about it.

inserting into access database apostrophe issue

2 Answers2