-1
query="select * from books where BookName LIKE \"%" +txt1.getText()+"%\"";

this is for mysql server database code. what will be change for oracle?

Nikunjo Nil Shuvo
  • 9
  • 2
  • Possible duplicate of [Why do I have ORA-00904 even when the column is present?](https://stackoverflow.com/questions/5722851/why-do-i-have-ora-00904-even-when-the-column-is-present) – AxelH Apr 25 '18 at 07:53
  • There is multiple answers that cover multiple causes. Also, `pass` is not in the query, are you sure this is the one ? – AxelH Apr 25 '18 at 07:54
  • 2
    Please learn how to use a [`PreparedStatement`](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html) and read the chapter [Basic Elements of Oracle SQL](https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00217) on how to specify string literals in SQL –  Apr 25 '18 at 08:13
  • please use preparedstatement. your sql has risk about sql injection... – yaoweijq Apr 25 '18 at 08:18
  • 1
    String literals in normal SQL dialects are enclosed in single quotes, not double quotes. And your code is vulnerable to SQL injection. Please learn how to use prepared statements. – Mark Rotteveel Apr 25 '18 at 08:19

2 Answers2

4

DO NOT build SQL queries using string concatenation - you should be using bind parameters.

Your query string should be:

query="select * from books where BookName LIKE ?";

and then you can do something like:

Class.forName( "oracle.jdbc.OracleDriver" ); // If you are using the Oracle driver.

Connection con = DriverManager.getConnection(
  "jdbc:oracle:thin:@localhost:1521:XE",
  "username",
  "password"
);

final String query="select * from books where BookName LIKE ?";
PreparedStatement ps = conn.prepareStatement(query);            
ps.setString( 1, "%" + txt1.getText() + "%" );
ResultSet rs = ps.executeQuery();
// Loop through the result set.
// Close statement/connections

(you will need to handle exceptions, etc.)

and:

  • You should not need to change the query to swap between MySQL and Oracle (just change the driver and connection string).
  • You do not need to escape any single or double quotation marks in the input string.
  • You are protected from SQL injection attacks.
  • Oracle can cache the query with the bind parameter and does not have to re-parse / re-compile it when the bind parameter changes.

If you are going to write the query as a string then string literals are surrounded by single quotes (not double quotes) in SQL:

query="select * from books where BookName LIKE '%your_string%'";

and you need to make sure that any single quotes in your string are properly escaped (but just use a bind parameter instead).

MT0
  • 143,790
  • 11
  • 59
  • 117
  • It would be better to handle JDBC resources with `try-with-resources`. – Mick Mnemonic Apr 25 '18 at 08:31
  • @MickMnemonic Updated to just be a snippet relevant to the OP's question - this is just an answer on how to pass parameters to a query and increasing the scope to what is the current "proper" exception handling technique is a distraction from the answer to the question. Yes, the modern way is to use `try-with-resources` but this is not a Java tutorial so I have removed all the extraneous bits of code to just focus the answer on the question asked (and included a note in the text to remember exception handling). – MT0 Apr 25 '18 at 08:46
  • I agree with the solution but the answer is to use `'` instead of `"`. This is only mentioned in the bullet point. This is hiding the problem. OPs could still write a query with a constant `String` like `"select * from persons where sex = \"M\""`. No parameters so no real reason to use a `PreparedStatement`. I feel this should be a bit more visible ;) – AxelH Apr 25 '18 at 08:49
  • 1
    @AxelH Updated. But you should still use a bind parameter as it allows Oracle to cache the query and does not need to re-parse it when `sex = 'M'` changes to `sex = 'F'` if you just use `sex = ?`. – MT0 Apr 25 '18 at 09:03
0

problem solved with this..

query="select * from books where BookName LIKE '%" +txt1.getText()+"%'";

thanks everyone :)

Nikunjo Nil Shuvo
  • 9
  • 2