CODEWITHSUNDEEP
sqloracle-database
user3255121
user3255121

Reputation: 41

Search Oracle database for a string with an escape

I am trying to search a Oracle database for any string that contains a %20 or a %2F in the value. The values that I am looking for came from a website that was not coded right and caused HTML URL encoding to be placed in the value string.

I have been using the following script to find data in the database, but found that I can not include an escape clause for the % symbol.

SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='OWNER';

-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='%\%2F%' ESCAPE '\';

--'-- Added to fix syntax highlighting on SO

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type AND table_name LIKE '%') LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' LIKE :1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;

Upvotes: 4

Views: 198

Answers (2)

Leo
Leo

Reputation: 1919

instead of 

EXECUTE IMMEDIATE 
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' LIKE :1'
INTO match_count
USING v_search_string;

try to use

EXECUTE IMMEDIATE 
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' LIKE ''' || v_search_string || ''''
INTO match_count;

Upvotes: 0

Christian13467
Christian13467

Reputation: 5584

You should add the escape to the query not the variable.

Look at Escaping special characters in SQL

SELECT * FROM table WHERE column like '%\%20%' ESCAPE '\'

The character in the escape is the character escaping the %. Have a look into the oracle documentation http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions016.htm

Upvotes: 1

Related Questions