CODEWITHSUNDEEP
phporacle-databasedoctrinedbal
Ryan Slama
Ryan Slama

Reputation: 43

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

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.

Upvotes: 4

Views: 291

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 2

Related Questions