SQL injection in an ALTER statement

Question

Apologies if this is a duplicate.

Let's say you have a Java app which has a query which can unlock/reset passwords for users like:

"ALTER USER " + iD_Of_User.toUpperCase() + " IDENTIFIED BY " + password_Of_User + " ACCOUNT UNLOCK"

Where iD_Of_User and password_Of_User definitely come in directly from an HTTP request.

I get an error when I try to use a PreparedStatement object to parameterize the dynamic values... I guess PreparedStatement's parameters can only be used for data values? And the ALTER query here is not using the values that way.

I don't think input validation is even possible here-- maybe on the 'iD_Of_User' value, but almost certainly not on the 'password_Of_User' value (which is sometimes used as a password reset-- so it's only restrictions are the Oracle 12c password standards).

Any help were would be greatly appreciated. I reviewed this post, but it didn't give me a good solution. I'm hoping someone knows of a good way to do password resets/account unlocks in Oracle which doesn't open the app up to SQL injection.

You can read the documentation about java PreparedStatement at this page https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html — curiouscupcake, Nov 18 '19 at 17:15
Thanks @LongNguyen. I noticed that the doc mentions there is execute() and executeQuery() option-- and that my app currently uses executeUpdate(), which states it should only be used with insert, update, or delete... of which ALTER is not one. However, my team is telling me that using execute() and executeQuery() are giving them the same error... so I guess either we're doing something wrong or those methods also don't allow for the use of an ALTER SQL query. Thanks for the response. — Garret, Nov 18 '19 at 21:40
String sql = "alter table foo add colum bar varchar(256)"; Connection con = new Connection('...'); Statement stmt = con.createStatement(sql); stmt.execute(); — curiouscupcake, Nov 18 '19 at 22:22
This statement should only be used for an immutable query since it can be used for SQL injection. For any other purpose, you should use PreparedStatement — curiouscupcake, Nov 18 '19 at 22:23

score 2 · Answer 1 · answered Nov 21 '19 at 01:11

Use dynamic PL/SQL to allow for bind variables, and to use DBMS_ASSERT to prevent SQL injection. I believe the question you linked to was pretty close to working.

For testing, I put the PL/SQL block inside another PL/SQL block. But in your Java program you'll only need the inner block.

begin
    execute immediate
    q'[
        declare
            v_username varchar2(128);
            v_password varchar2(4000);
            v_sql      varchar2(32767);
        begin
            --You may want to catch and handle the exceptions to be more user-friendly.
            v_username := dbms_assert.schema_name(upper(trim(:username)));
            v_password := dbms_assert.enquote_name(:password);

            v_sql := 'alter user '||v_username||' identified by '||v_password;
            --Only include this line in debug version, for testing.
            dbms_output.put_line(v_sql);
            execute immediate v_sql;
        end;
    ]'
    --Use bind variables for these values:
    using 'test_user', 'passw0rd###1234''';
end;
/

The Oracle passwords can't contains double quotation character as " ? BUT password value as {"password"} will be interpreted as {password} which may be the issue for user who don't know this feature, right? See: https://docs.oracle.com/database/121/ARPLS/d_assert.htm#ARPLS65381 — Marek-A-, Oct 20 '20 at 13:04
@Marek-A- I'm not sure if I understand. If the value entered is `{"password"}` the double quotes will not be silently removed, the PL/SQL block will fail with the error "ORA-44003: invalid SQL name". — Jon Heller, Oct 21 '20 at 00:36

SQL injection in an ALTER statement

1 Answers1