0

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>] ]
Karl Henselin
  • 113
  • 1
  • 2
  • 8
  • There are several places you can run into trouble here, and each may require different solutions: when the script accepts the password (solution depends on how it gets the password), how it passes the password to `sqlplus` (if it's in a variable, double-quotes are the solution here), and how `sqlplus` parses it (AIUI there are some reserved characters here, like "@", that'll require special treatment -- but I don't know `sqlplus` well enough to know a solution). To get it to work right, you need to solve *all three* of these problems (and maybe more, depending...). – Gordon Davisson Feb 14 '19 at 01:34
  • BTW, the error messages you list suggest you're running into trouble at the first step, but without more info it's hard to tell exactly why, or how to solve it. – Gordon Davisson Feb 14 '19 at 01:36
  • @GordonDavisson Thanks for the thoughts. I have separated it into multiple parts now, first making the variable and setting it, and then passing it in. I have gotten past the BASH errors, so now I think that you are right, it is another challenge to face but separate. I have found this article now http://banhill.hu/banhill/orapass_en.html with a list of characters and their treatments, so I will see if I get any luck from it. – Karl Henselin Feb 14 '19 at 16:13

2 Answers2

0

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.

NoMad
  • 302
  • 1
  • 4
  • 14
  • 1
    `echo` isn't necessarily a reliable way to see how bash is interpreting a string, since `echo` itself may try to interpret some escape sequences in the string (or it might not, depending on version, compilation options, runtime settings, phase of moon, etc). I recommend using `printf '%s\n' whateverstring` instead. – Gordon Davisson Feb 14 '19 at 18:54
  • How `echo` or `printf` process their input does not matter here, the problem OP encountered is that `bash` itself parsed the literal value of the string argument. This happens even before any command is executed. In fact we don't even have to call anything, just entering the quoted string to test is enough to verify (by comparing input and output) if it contains tokens that bash substitutes or expands. See http://tldp.org/LDP/Bash-Beginners-Guide/html/sect_01_04.html#sect_01_04_01_01 and http://tldp.org/LDP/Bash-Beginners-Guide/html/sect_03_03.html – NoMad Feb 14 '19 at 21:40
  • 1
    My point is the output of `echo` is not reliable for test purposes. Compare the output of `echo 'foo\nbar'` with `echo $'foo\nbar'` -- the strings passed are different, but they *might* produce the same output (depending on the factors I mentioned). Basically, `echo` adds another possible source of confusion, and when you're trying to figure out what's going on, additional sources of confusion are bad. – Gordon Davisson Feb 14 '19 at 23:26
  • I get your point, `echo` may apply additional string manipulation. But we're only using echo to look for **bash syntax errors** due to unescaped tokens, since that was OP's original problem. How `echo` or `printf` produce their output is irrelevant, since OP wants to pass a string as an argument to another command that may interpret strings in yet another way. – NoMad Feb 17 '19 at 13:55
0

So, the Oracle rules are straightforward. However, oracle uses many special characters in its own way. For example, avoid @ because oracle expects a connect string after that character. Here are the rules: if the password is not enclosed in quotes then it can include any letter, any digit, "_", "#" or "$" characters. Only a letter can be used in the first character. If you enclose the password in quotes you can use any character in any position.

However, in my experience, avoid these at all times: _, %, @, \,/ because they are likely to confuse Oracle

OracleDBA
  • 27
  • 4