Srini, August 15, 2006 - 4:27 pm UTC
Thanks Tom.
using regular expressions
Tom, August 16, 2006 - 4:14 pm UTC
Doesn't this work also?
1 select val,
2 substr(val, 1,
3 regexp_instr(val, '\..*([[:digit:];]\.|\.[[:digit:];])')
4 )
5 from (select 'test to. strip.; a string9.; character' val
6 from dual
7* ) x
SQL> /
VAL SUBSTR(VAL,1,REGEXP_INSTR(VAL,'\..*([[
-------------------------------------- --------------------------------------
test to. strip.; a string9.; character test to.
August 16, 2006 - 5:30 pm UTC
version 9.2....
regular expressions ... not easy to do or maintain ...
Gabe, August 16, 2006 - 7:16 pm UTC
19:06:22 session_151> select val,
19:06:22 2 substr(val, 1,
19:06:22 3 regexp_instr(val, '\..*([[:digit:];]\.|\.[[:digit:];])')
19:06:22 4 ) ss
19:06:22 5 from (select :x val from dual
19:06:22 6 ) x
19:06:22 7 /
VAL SS
-------------------- --------------------
1..2 1.
August 16, 2006 - 7:19 pm UTC
(i am personally not a regex fan myself)
Pro regular expression ... they do simplyify things.
cd, August 17, 2006 - 6:54 am UTC
And always remember, there are several different regex_functions in 10g:
SELECT REGEXP_SUBSTR('test to. strip.; a string9.; character', '^.*\.[^;0-9]')
FROM dual;
C.
August 17, 2006 - 9:16 am UTC
Needs word though, doesn't do what it needs to do:
ops$tkyte%ORA10GR2> create table t ( x varchar2(50) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'test to. strip.; a string9.; character' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 'test to. strip;. a string9. character' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT REGEXP_SUBSTR(x, '^.*\.[^;0-9]')
2 FROM t;
REGEXP_SUBSTR(X,'^.*\.[^;0-9]')
--------------------------------------------------
test to.
test to. strip;. a string9.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x,
2 substr( substr(x,1,1700), 1,
3 instr( replace( replace( translate(substr(x,1,1700),'0123456789;','00000000000'), '0.', '00' ), '.0', '00' ), '.', -1 )-1)
4 ss
5 from t
6 /
X
--------------------------------------------------
SS
--------------------------------------------------
test to. strip.; a string9.; character
test to
test to. strip;. a string9. character
test to
And removing the last character of the result ...
cd, August 17, 2006 - 6:59 am UTC
SELECT REGEXP_REPLACE('test to. strip.; a string9.; character', '(^.*\.)([^;0-9].*$)', '\1')
FROM dual
C.
Fell into my own trap ... ;-)
cd, August 17, 2006 - 11:44 am UTC
Testing, testing, testing ... I know. ;-)
Rereading the OPs question again, I'd see this should do it.
SELECT REGEXP_SUBSTR(REGEXP_REPLACE('test to. strip. a string. character.', '\.*[;0-9].*$|', ''), '.*\.')
FROM dual
I have to admit that the OPs question was a bit tricky.
C.
Incomplete cut & paste *sigh* ...
cd, August 17, 2006 - 11:55 am UTC
Finally, this should do the trick. It's not the regex fault ...
SELECT REGEXP_SUBSTR(REGEXP_REPLACE('test to. strip. a string.; character.', '\.[;0-9].*$|[;0-9]\..*$', ''), '.*\.')
FROM dual
Last attempt ... sorry for spamming this thread.
C.
Kind of proves the point
Gabe, August 17, 2006 - 2:31 pm UTC
REGEXP tends to result in trial-and-error coding ... it just is too cumbersome most of the time.
flip@flop> SELECT :x src
2 , REGEXP_SUBSTR(REGEXP_REPLACE(:x, '\.[;0-9].*$|[;0-9]\..*$', ''), '.*\.') tgt
3 FROM dual;
SRC TGT
---------- ----------
1...2 <null>
flip@flop> SELECT :x src
2 , REGEXP_SUBSTR(REGEXP_REPLACE(:x, '\.[;0-9].*$|[;0-9]\..*$', ''), '.*\.') tgt
3 FROM dual;
SRC TGT
---------- ----------
1.. <null>
Here REGEXP_REPLACE may remove characters from the substring were suppose to return
so there is no way this will work even if the expression were to be refined.
Anyway, REGEXP_INSTR and REGEXP_SUBSTR are not quite suitable here because they do not search backwards
as INSTR and SUBSTR could. Finding that last . is just much easier with INSTR.
So that Im not just "criticizing"
Gabe, August 17, 2006 - 3:49 pm UTC
flip@flop> with g as
2 (select :x src
3 ,regexp_instr('a'||reverse(:x)||'a', '[^;0-9]\.[^;0-9]') pos
4 from dual)
5 select src, case when pos = 0 then null
6 else substr(src,1,length(src)-pos+1)
7 end tgt
8 from g
9 ;
SRC
------------------------------------------------------------
TGT
------------------------------------------------------------
.Not this;. nor this..9 but this..;1 hmm
.Not this;. nor this..9 but this.
The pattern is . with anything but ; 0-9 at both sides
search for the first occurrence of the pattern in the reversed string (padded with a at both ends to deal with the . existing at extremes)
then some math and substr to cut to size.
You have to think in regex ...
cd, August 18, 2006 - 2:16 am UTC
... it's the same as thinking in sets, as someone once stated regarding SQL. ;-)
@Gabe: In your example, I'd say that the regex work as defined. If you follow the OPs definition, they work to the point and have to return NULL as result. So your example actually proves my point.
C.
REGEXP_REPLACE in a function with a package
Aaron, November 08, 2006 - 10:38 am UTC
Tom -
Using 10gR2 and REGEXP_REPLACE, is there a way to strip out the "@" and the "." from an email_address, from within a function placed in a package?
For example:
CREATE OR REPLACE PACKAGE CREATE_SCHEMA
AS
PROCEDURE USER
( iOwner IN VARCHAR2,
iPassword IN VARCHAR2);
FUNCTION CLEANUP
( p_str IN VARCHAR2) RETURN VARCHAR2;
END CREATE_SCHEMA;
/
CREATE OR REPLACE PACKAGE BODY CREATE_SCHEMA
AS
FUNCTION CLEANUP
( p_str IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN
REGEXP_REPLACE(p_str, '@.',NULL);
END CLEANUP;
PROCEDURE USER
( iOwner IN VARCHAR2, iPassword IN VARCHAR2)
IS
BEGIN
CLEANUP(iOwner);
EXECUTE IMMEDIATE ' CREATE USER '||p_str||' IDENTIFIED BY '||iPassword||' DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP';
END USER;
END CREATE_SCHEMA;
/
Then when I execute
CREATE_SCHEMA.USER('aaron.smith@yahoo.com','password');
I would like to have a user called aaronsmithyahoocom created in the database.
I know I'm doing something wrong, because I can't get the package body to compile correctly - but I just can't seem to figure out what it is I'm doing wrong.
Any help is greatly appreciated.
Aaron
November 08, 2006 - 7:54 pm UTC
can you spell "sql injection"
cause you are seriously subject to it, can you see where and why?
1* select REGEXP_REPLACE('tkyte@x.y', '[@.]',null) from dual
ops$tkyte%ORA10GR2> /
REGEXP_
-------
tkytexy
seriously though, this code CANNOT GO PRODUCTION, can you see why? It has a grievous error it in security wise (please use google.com with sql injection)
About injection
Michel Cadot, November 09, 2006 - 4:56 am UTC
Hi Tom,
I don't see how one can inject something in the very specific case.
I'm not an expert of hijacking statements but it seems to me that to twist this statement we must break into 2 or more and so include a ";" which is not allowed in this case.
Note this is not an apology to do this but just a need to frame the danger.
At least a call to dbms_assert must be done.
Regards
Michel
November 09, 2006 - 8:37 am UTC
I'll add a "--"
ops$tkyte%ORA10GR2> create or replace procedure sql_inject
2 ( iOwner IN VARCHAR2, iPassword IN VARCHAR2)
3 IS
4 BEGIN
5 dbms_output.put_line
6 ( ' CREATE USER '||REGEXP_REPLACE( iOwner, '[@.]',null)||
7 ' IDENTIFIED BY '||iPassword||
8 ' DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP' );
9 execute immediate
10 ( ' CREATE USER '||REGEXP_REPLACE( iOwner, '[@.]',null)||
11 ' IDENTIFIED BY '||iPassword||
12 ' DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP' );
13 END;
14 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop user my_user cascade;
User dropped.
ops$tkyte%ORA10GR2> exec sql_inject( 'MY_USER identified by x default tablespace system quota unlimited on system --' , null);
CREATE USER MY_USER identified by x default tablespace system quota unlimited
on system -- IDENTIFIED BY DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select username, default_tablespace
2 from dba_users where username = 'MY_USER';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MY_USER SYSTEM
doh!
Aaron, November 09, 2006 - 6:03 am UTC
Tom -
Thanks for the insight. It never crossed my mind that this was vulnerable to SQL injection.
I found some good information with the Google search at:
</code>
http://www.securiteam.com/securityreviews/5DP0N1P76E.html <code>
and other links from within that URL.
Is this one of those "teach a man to fish" scenarios, where you're not going to come out and show the vulnerability? Can you at least give a hint :-)
I'll continue to research the SQL injection possibility and come back with anything interesting I can find.
November 09, 2006 - 8:40 am UTC
see above for a "quick one"
always think about what -- might do to you :)
but we don't even need --, you allow us to use any of the create user inputs - we'll just make our password:
password quota unlimited on ....
for example.
another good link
A reader, November 09, 2006 - 7:00 am UTC
SQL injection
Padders, November 09, 2006 - 8:21 am UTC
Certainly there appears to be scope for abuse of optional clauses following the IDENTIFIED BY clause, e.g.
CREATE_SCHEMA.USER ('aaron.smith@yahoo.com','password QUOTA UNLIMITED ON SYSTEM');
REGEXP_SUBSTR without word break
A reader, March 19, 2009 - 10:16 am UTC