What is the way with PHP to sanitize user input for SQL DDL statement?

Question

I need to update a user's password from PHP, this is accomplished through an ALTER USER statement. I can't use prepared statements, because they do not support DDL commands. The username field can be easily sanitized my making sure it matches an Alphanumeric regex, but I can't figure out a safe way to sanitize the password.

How should I do this?

I'm aware the situation isn't ideal, but I do not call the shots here. I'm just trying to keep it as secure as I can.

EDIT: Here's an example of what I'm trying to run

ALTER USER myusername IDENTIFIED BY mynewpassword1

This runs in SQL Developer successfully, and I could make it work through concatenation, but I'm hoping to avoid that.

Is this a insert/update row question or is it a alter table question? If the first, I don't see how one can't accomplish this with a prepared statement — Ruben Vincenten, Jun 09 '17 at 20:43
If it's neither please show us some code. In generic SQL or DQL with dbal alter is used to modify table structure, not modify rows. So it's either `alter table ...` or `update user set...`. — Ruben Vincenten, Jun 09 '17 at 21:56

score 2 · Accepted Answer · answered Jun 12 '17 at 01:54

Pass the input to a PL/SQL block bind variable and do the concatenation inside PL/SQL after using DBMS_ASSERT.ENQUOTE_NAME to validate the input.

(The original poster understands the danger of this approach but it's worth restating: This is generally not a good idea. Always avoid SQL statement building with concatenation when possible. 99.9% of the time this can be done with plain old bind variables. There are many sneaky ways to make SQL injection happen whenever strings are concatenated.)

declare
    v_quoted_string varchar2(100);
begin
    v_quoted_string := dbms_assert.enquote_name(:v_new_password, capitalize => false);
    execute immediate 'alter user myusername identified by '||v_quoted_string;
end;
/

If the user tries to break out of the name by using a double quotation mark this exception will be raised: ORA-44003: invalid SQL name.

What is the way with PHP to sanitize user input for SQL DDL statement?

1 Answers1