4 Replies Latest reply: Feb 22, 2013 6:33 AM by 984246 RSS

    How to handle invalid use of query operators in CONTAINS?

    984246
      I am using the 11gR2, XE version of Oracle's DB. I have set up the text index with WORLD_LEXER.

      I'm having troubles with CONTAINS' input, when it contains (invalid) query operators.
      If I make a mistake in the query, e.g. "hello & | | hi", the server will crash because Oracle won't be able to parse the query.
      What is the recommended way (SQL-injection-proof and handles unparsable input) to generate the query for the CONTAINS procedure?

      I was using curly brackets until now, but those will escape the query operators. Note that I don't have problem escaping the whole query, if it's invalid, but how would I go about detecting that?
        • 1. Re: How to handle invalid use of query operators in CONTAINS?
          Roger Ford-Oracle
          Have a look at this blog entry:
          https://blogs.oracle.com/searchtech/entry/oracle_text_query_parser
          • 2. Re: How to handle invalid use of query operators in CONTAINS?
            984246
            Your parser might be just the thing I need!

            I have a question though. In one of my queries I'm selecting from two different tables that have two different indices set up. Then I UNION ALL the results.
            If I call "exec parser.setIndexName('index');" twice, once with each index name, will your parser work on both indices? Or, is there a way I can call this function before each select?

            Example of the unioned query:
                    (select COUNT(*) OVER () "Count", NestNest.*, ROWNUM rn FROM (
                      (select score(1) RANK, rowid rid
                       from "Table1"
                       where contains("dummy1", input, 1) > 0 
                       UNION ALL
                       select score(2) RANK, rowid rid
                       from "Table2"
                       where contains("dummy2", input, 2) > 0)
                       order by RANK desc
                      ) NestNest
                    ) Nest
            • 3. Re: How to handle invalid use of query operators in CONTAINS?
              Roger Ford-Oracle
              Ah, good question. Hadn't considered that scenario.

              The index name is only used to fetch join characters (PRINTJOINS) and stopwords. If you're using the same join characters (or none) and stopword list for each index, then just call it once, the string returned will work fine for both indexes.

              If not, then you'll need to call the parser function outside of the query, something like:
              search1 varchar2(4000);
              search2 varchar2(4000);
                
              parser.setIndex( 'myindex1' );
              search1 := parser.simpleSearch( input );
              parser.setIndex( 'myindex2' );
              search2 := parser.simpleSearch( input );
              
              select score(1), ...
                where contains( dummy1, search1, 1 ) > 0
              UNION ALL
              select score(2), ...
                where contains( dummy2, search2, 2 ) > 0
              Alternatively, you could modify my code so that the index name is passed in as a parameter to each call. That's how I had it originally, but then I moved it to a separate call to make it neater.

              Edited by: Roger Ford on Feb 22, 2013 4:25 AM
              • 4. Re: How to handle invalid use of query operators in CONTAINS?
                984246
                As luck would have it, I use a global stoplist :)