CODEWITHSUNDEEP
sqlsql-serveroracle-databaseoracle11gescaping
Mohd Fikrie
Mohd Fikrie

Reputation: 327

set escape character in oracle

I'm working on a Java program that try to reset a user's password in oracle and sql-server. This password is a random generated password that will have some character that is not acceptable as a normal string. Eg. ',"",;

The command that I'm using to reset user's password is :

oracle: ALTER USER <username> IDENTIFIED BY <password>

sql-server: ALTER LOGIN <username> WITH PASSWORD = '<passowrd>'

How can I do this reset so that it can accept all kind of special character?

I did google and found out about quoting method:. Also I did found out about using single code and double code. But what if the password generated have a " or same quote delimiter inside that password? Then it will be a problem. 

Eg. IDENTIFIED BY 'jks'k"fjh''d'
Eg. password = q[#kkksdj#jsksls#]
Eg. password = "nm.js""kh:kjhs"

Is there any way for me to do this inside the oracle and sql-server? Or do I need to escape each character one by one from java before sending to oracle/sql-server? My reset program for oracle and sql-server are different. So the method can be different.

Upvotes: 1

Views: 1070

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

But what if the password generated have a " or same quote delimiter inside that password? Then it will be a problem.

Yes, of course. Oracle has documented it in Object Names and Qualifiers which equally applies to the password as well.

From the documentation,

Passwords must follow the rules described in the section "Schema Object Naming Rules"

and the most important part:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

So, neither quoted nor nonquoted identifiers can contain double quotation marks.

To allow single-quotes, you could use a quoted identifier using double-quotation marks. For example,

SQL> create user test identified by "a'b'c";

User created.

Upvotes: 1

Related Questions