1

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>] ]
Improve this question
3
  • 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...). Commented Feb 14, 2019 at 1: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. Commented Feb 14, 2019 at 1: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 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. Commented Feb 14, 2019 at 16:13

2 Answers 2

Reset to default
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.

Improve this answer
4
  • 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. Commented Feb 14, 2019 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 tldp.org/LDP/Bash-Beginners-Guide/html/… and tldp.org/LDP/Bash-Beginners-Guide/html/sect_03_03.html Commented Feb 14, 2019 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. Commented Feb 14, 2019 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. Commented Feb 17, 2019 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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.