Bash – Escape all characters that shell or sqlplus need escaped in any password given

bashoracleshellsqlplus

You may try for yourself by creating this user:

CREATE USER "karl" IDENTIFIED BY "/?'!@#$%^&*()_+=-~`"

(Maybe there are other symbols I should have included for my testing, but that seemed like a good start)

These characters seem to all be allowed, and the user was created.

The actual purpose (the reason that I made the password) is to verify that sqlplus when run with the right escaping can handle all passwords that might be passed in and could be valid.

so, I am running this from bash to test it
sqlplus karl/"/?'!@#$%^&*()_+=-~`"
and then I tried to escape different characters trying to figure it out.

I have gotten errors in my testing such as:

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-C <v>] [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_identifier>] | / | /NOLOG
      <start>  ::= @<URL>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-C" sets SQL*Plus compatibility version <v>
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode

or

-bash: *()_+=-~`": command not found

or 

-bash: syntax error near unexpected token `_+=-~\`\"'

or

-bash: !@#$%^: event not found

etc.
The goal is for the code to just work:
sqlplus ${USER/bashfunction?}/${PW/bashfunction?} and a connection occurs.

There are many guides about double quoting the password, and I have done so, but it isn't enough. I saw one about single and double quoting, but that didn't help either.

If it isn't possible, that's ok too I guess, but I would like to know what the restrictions are so that I can pass then on to my users.

Oracle version I am using is as follows, but this answer should work across sqlplus releases, perhaps being performed in shell?
SQL*Plus: Release 10.1.0.5.0 – Production on Wed Feb 13 16:26:41 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

So, I will try with a bit different idea now, put into an environment variable first:

user=karl
password="/?'!@#$%^&*()_+=-~`"
#failed. so tried
password="/?'\!@#$%^&*()_+=-~\`"
echo $password
/?'\!@#$%^&*()_+=-~` #seemed to work

sqlplus ${user}/"${password}"
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]

Best Answer

Bash is throwing these errors since bash needs escaping, too.

More information about bash escaping: https://stackoverflow.com/questions/15783701/which-characters-need-to-be-escaped-when-using-bash

To test that bash isn't encountering any errors with unescaped strings, you can echo the string in question. If the string is returned as expected, the string is correctly escaped. If however there are any errors, the string is not escaped correctly:

echo "/?'!@#$%^&*()_+=-~`"
bash: !@#: event not found

In this case, bash evaluated the string because it's in double-quotes and tried to execute a history command: http://tldp.org/LDP/abs/html/abs-guide.html#HISTCOMMANDS

This is expected behavior, bash also substitutes variables in double-quoted strings:

test=123
echo "Value of \$test: $test"
Value of $test: 123

So when calling the sqlplus command, try using single-quoted strings. Bash won't evaluate the content of single-quoted strings.

Another option would be to escape the string for bash when using double-quoted strings.

Reading the content of a variable from stdin and then using the variable as an argument to the command is another workaround, but won't always work since the content of variables may be evaluated, too.

Related Topic