Search code examples
javaoracle-databasejdbcojdbc

JDBC PrepareStatement parameters do not work for CREATE, DROP, ALTER

I need to use JDBC to create a new Oracle user. The user name and password of the new user are provided by the user via a GUI. The following codes works fine (I can also use Statement instead of PreparedStatement).

PreparedStatement preparedStatement = connection.prepareStatement("create user " + username + " identified by " + password);
preparedStatement.execute();

However, since the user name and password are provided by the user, they may contain special characters like space, semi-column, quotes, etc, which may make the above statement invalid. I don't want my codes to be doing the validations of the user name and password, and to leave it to Oracle for the validation. Therefore, I thought of using parameters in the prepared statement instead:

PreparedStatement preparedStatement = connection.prepareStatement("create user ? identified by ?");
preparedStatement.setString(1, userName);
preparedStatement.setString(2, password);
preparedStatement.execute();

But it doesn't work at all. When I supply a valid user name and password, I will get "ORA-01935: missing user or role name". It seems that the parameters do not work for CREATE, DROP, ALTER statements. How to resolve my problem? Thanks in advance.

Solution

  • User name and password are not text values, but identifiers.

    The PreparedStatement parameter markers (?) are used to supply dynamic values of various data types. They cannot be used to supply identifiers, e.g. you cannot do things like SELECT * FROM ? to dynamically specify the table name.

    String concatenation is the only way, and you need to quote (") the identifier if it contains special characters, just like the documentation says:

    If a password starts with a non-alphabetic character, or contains a character other than an alphanumeric character, the underscore (_), dollar sign ($), or pound sign (#), then it must be enclosed in double quotation marks. Otherwise, enclosing a password in double quotation marks is optional.