0

As I try to put data into my Oracle database using Java, I'm facing the problem that I need to avoid special characters or to escape them. I'm sure, that there is a smart way to workaround this using functionalities I'm not aware of.

Example of my problem:

movie_name: Pirates of the Caribbean: Dead Man's Chest

The title got an " ' " which means, it will be processed by Oracle database. When putting this into an INSERT statement using a Java variable, it can't be escaped.

The data I want to get into my database is from The Movie Database (TMDB), I'm using a wrapper called themoviedbapi. Using that wrapper I wrote myself a little program to get the data and put it into my database.

[...] for (int i = 0; i < tmdb_max_id; i++) { int movie_count = 0; try { MovieDb movie_name = movies.getMovie(i, "en"); String imdb_id = movie_name.getImdbID(); db.processInsert("INSERT INTO TMDB_TEST_MOVIES (TMDB_ID, ORIGINAL_TITLE, IMDB_ID) "+ "VALUES ('"+i+"', '"+movie_name+"', '"+imdb_id+"')"); i++; } catch (RuntimeException e) { System.err.println("Error getting movie with ID " + i); continue; } catch (SQLException sqle) { System.err.println(sqle+" SQL ERROR at movie with ID"+i); throw sqle; }[...]

What I'm getting when reaching the movie (see above) is following exception:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00917: Missing comma at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878) at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318) at database.DatabaseConnect.processInsert(DatabaseConnect.java:24) at tmdb_api_to_db.main.main(main.java:65)

Which is propably because I'm having that specific problem with non-escaped special characters. A colleague told me there is a way in binding variables in order to avoid SQL Injections, which would also solve my problem, but - to be honest - I searched for such a functionality and wasn't successful in tranfering it to my specific problem.

The ideal solution would be, if someone could tell me how to build my INSERT statement to avoid the exception above...

I'm totally open to criticism and advices in any way!

As you may noticed: My knowledge regarding SQL as well as Java are rusty as hell, so please be indulgent :)

Improve this question
4
  • 7
    You might want to use PreparedStatement instead of hand-crafting queries with values (that's most likely what your colleague was talking about). This should take care of your escaping problem as well as tremendously increase security (google "SQL injection"). Commented Jan 30, 2017 at 17:04
  • ... and increase performance due to limitation of parsing as well! Commented Jan 30, 2017 at 17:15
  • @Thomas thanks so far, I'll look it up! Commented Jan 30, 2017 at 17:19
  • @Thomas that solved the problem! PreparedStatement is candy ;) Thank you! Commented Jan 30, 2017 at 18:11

2 Answers 2

Reset to default
1

Here is how I solved it - credits to @Thomas and @Marmite Bomber :)

I added PreparedStatement to my DatabaseClass

package database; import java.sql.*; public class DatabaseConnect { private Connection con; public void connect(String user, String password, String dbUrl) throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection(dbUrl, user, password); } public void disconnect() throws SQLException { con.close(); } public void processInsert(String sql) throws SQLException { Statement stmt = con.createStatement(); stmt.execute(sql); stmt.close(); } public ResultSet processQuery(String sql) throws SQLException { Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); return rs; } public PreparedStatement prepareStatement(String sql) throws SQLException{ PreparedStatement ps = con.prepareStatement(sql); return ps; } }

And used it in my Main like this:

[...]PreparedStatement ps = db.prepareStatement("INSERT INTO TMDB_TEST_MOVIES (TMDB_ID, ORIGINAL_TITLE, IMDB_ID) VALUES (?,?,?)");[...] [...]for (int i = 0; i < tmdb_max_id; i++) { try { MovieDb movie_name = movies.getMovie(i, "en"); String original_title = movie_name.getOriginalTitle(); String imdb_id = movie_name.getImdbID(); ps.setInt(1,i); ps.setString(2,original_title); ps.setString(3,imdb_id); ps.executeUpdate(); i++; } catch (RuntimeException e) { continue; } catch (SQLException sqle) { throw sqle; } }[...]

Hope this helps someone out when looking for that problem... Cheers and thanks again!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Just add an apostrophe to your string to escape. Example:

 String movie_name = "Pirates of the Caribbean: Dead Man's Chest"; if (movie_name.contains("'")){ movie_name = movie_name.replaceAll("'", "''"); }
Improve this answer

2 Comments

Thanks for your answer! Solving it via PreparedStatement seems to be more elegant
Don't ever prefer concatenating values into a query over prepared statements. Your application security will thank you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.