Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2015
    Posts
    9

    Unanswered: Issue writing dbms assert statements for alter profile

    Hi

    Tried to write a plsql statement as follows using dbms assert statements for alter profile, but getting errors.We want to prevent sql injections, so trying this approach.

    DECLARE
    BEGIN
    EXECUTE IMMEDIATE 'ALTER profile sys.dbms_assert.enquote_name(sys.dbms_assert.simpl e_sql_name(DEFAULT)) limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)';
    END;

    Above statement gives the error as :

    Error report:
    ORA-02000: missing LIMIT keyword
    ORA-06512: at line 3

    It will be great if somebody can help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2015
    Posts
    9
    Quote Originally Posted by anacedent View Post
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.
    ok , thanks for the reply. if you can mention what might be the issue with that statement it will be helpful.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So, you didn't bother to follow Anacedent's suggestion, did you? How about doing it now? Copy/paste SQL*Plus session over here.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    valid statements do not throw error.
    Error is Oracle's way to inform you that something is wrong.
    So you need to correct the invalid syntax.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2015
    Posts
    9
    Quote Originally Posted by anacedent View Post
    valid statements do not throw error.
    Error is Oracle's way to inform you that something is wrong.
    So you need to correct the invalid syntax.
    Hi
    Thanks Anacedent and Littlefoot for your replies.

    I tried your advice as follows:

    Executed the below statement in sqldeveloper.

    DECLARE
    sql_stmt VARCHAR2(500);
    BEGIN
    sql_stmt := 'ALTER profile sys.dbms_assert.enquote_name(sys.dbms_assert.simpl e_sql_name(DEFAULT)) limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)';
    dbms_output.Put_line(sql_stmt);
    END;

    It just says 'anonymous block completed'.It is not printing the value of sql_stmt variable.Tried using 'print' , that gives some syntax error.

    I am not an expert in PL/SQL, so if you can help me here it will be great.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You have to enable SERVEROUTPUT. I don't use SQL Developer, but I presume that it has a tab in its SQL Editor window or something like that which enables you to see the result of DBMS_OUTPUT.PUT_LINE calls.

  8. #8
    Join Date
    Mar 2015
    Posts
    9
    Quote Originally Posted by Littlefoot View Post
    You have to enable SERVEROUTPUT. I don't use SQL Developer, but I presume that it has a tab in its SQL Editor window or something like that which enables you to see the result of DBMS_OUTPUT.PUT_LINE calls.
    Hi

    Tried setting SERVEROUTPUT on as follows:

    SET SERVEROUTPUT ON size 2000
    DECLARE
    sql_stmt VARCHAR2(500);
    BEGIN
    sql_stmt := 'ALTER profile sys.dbms_assert.enquote_name(sys.dbms_assert.simpl e_sql_name(DEFAULT)) limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)';
    dbms_output.Put_line(sql_stmt);
    END;


    Now i get the below output in the Script output window:

    anonymous block completed
    ALTER profile sys.dbms_assert.enquote_name(sys.dbms_assert.simpl e_sql_name(DEFAULT)) limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)

    Now how should i proceed with this output?

  9. #9
    Join Date
    Mar 2015
    Posts
    9
    Tried to run the sql got inthe output directly and it fails with this error:

    Error starting at line : 1 in command -
    ALTER profile sys.dbms_assert.enquote_name(sys.dbms_assert.simpl e_sql_name(DEFAULT)) limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)
    Error report -
    SQL Error: ORA-02000: missing LIMIT keyword
    02000. 00000 - "missing %s keyword"

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ALTER statement you got as a result is invalid. It looks as this:
    Code:
    ALTER profile  sys.dbms_assert.enquote_name(sys.dbms_assert.simpl  e_sql_name(DEFAULT))  limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)
    What looks suspicious is a concatenation (double pipe) operator. Also, there's a space between "simpl" and "e". Other errors are possible as well.

    Verify ALTER PROFILE syntax (in Oracle documentation) and modify the way you use to create a valid ALTER PROFILE statement.

  11. #11
    Join Date
    Mar 2015
    Posts
    9
    Quote Originally Posted by Littlefoot View Post
    ALTER statement you got as a result is invalid. It looks as this:
    Code:
    ALTER profile  sys.dbms_assert.enquote_name(sys.dbms_assert.simpl  e_sql_name(DEFAULT))  limit failed_login_attempts || sys.dbms_assert.simple_sql_name(20)
    What looks suspicious is a concatenation (double pipe) operator. Also, there's a space between "simpl" and "e". Other errors are possible as well.

    Verify ALTER PROFILE syntax (in Oracle documentation) and modify the way you use to create a valid ALTER PROFILE statement.
    After correcting the space between "simpl" and "e" also getting the same error. Checked the ALTER PROFILE syntax in Oracle doc and it looks fine. Only thing we are adding dbms_asserts functions to prevent sqlinjection.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I think you want to validate the SQL statement BEFORE the statement is actually executed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by sabkan View Post
    Only thing we are adding dbms_asserts functions to prevent sqlinjection.
    You are adding it in a wrong manner. You can't
    Code:
    alter profile blabla || sys.dbms_assert 
                         ^
                         |
                       this!
    A simple example is altering user's password - you can't use concatenation here:
    Code:
    SQL> alter user scott || identified by tiger;
    alter user scott || identified by tiger
                    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While the goal to prevent injected SQL is admirable, the current "plan" is woefully lacking.
    Any "injected" SQL code must be valid in order to be successfully executed.
    Simply checking for a valid statement does NOTHING to prevent injected SQL from being present & then executed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Mar 2015
    Posts
    9
    Quote Originally Posted by anacedent View Post
    While the goal to prevent injected SQL is admirable, the current "plan" is woefully lacking.
    Any "injected" SQL code must be valid in order to be successfully executed.
    Simply checking for a valid statement does NOTHING to prevent injected SQL from being present & then executed.
    Hi

    Basically we are calling this sql statement from within java. So thought of validating the sql statement first explicitly, then call the same from java.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •