Shell Script with sqlplus and special characters on password

3
votes

I have a question that mix Linux / Unix shell-scripting and sqlplus (Oracle) that is driving me crazy. :-)

sqlplus utilize a syntax like this:

./sqlplus johnF/[email protected]:1521/SID

And it works fine. However my password is not simple as "mypassword", it utilize "!" and "@" and sometimes even "\". For this example, let's suppose that my password is !p@ssword

If I use the following syntax in sqlplus it works:

./sqlplus johnF/'"!p@ssword"'@127.0.0.1:1521/SID

That's great. However I wanted to use it in a shell script that get call sqlplus and get many parameters from files (username, password, SID and SQL QUERY), just for example let me use a reduced code.

#!/bin/bash

while IFS=: read -r line
do

        echo "./sqlplus johnF/[email protected]:1521/SID" 
        echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/[email protected]:1521/SID

done < $1

I have attempted to fix it in many ways, including:

echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/'"$line"'@127.0.0.1:1521/SID
echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/'\"$line\"'@127.0.0.1:1521/SID
echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/\'\"$line\"\'@127.0.0.1:1521/SID

And many others and all fails, in a few cases the first echo print the output exactly as it should be passed to sqlplus, but it never works, returns login denied (wrong password) or connection issues (maybe the @ being intercepted as wrong target).

How to solve this puzzle?

Thanks.

3
linuxbashoracleshellsqlplus
Maybe this? Or perhaps using a heredoc? – Jeff Holt
Can you use sqlplus /nolog, and then a connect statement within the sqlplus script. That should remove some of the shell interactions which complicate the issue. – Gary Myers
could you share your alter user johnF identified by ??? statement? – Barbaros Özhan
There are a number of oracle MOS articles related to special characters associated with database passwords like, support.oracle.com/MMOS/faces/ui/km/…. There is guidance on escaping and use of quotation marks. – Patrick Bacon
Thanks guys, my password was changed correctly. And I know basic of shell escaping, but I'm not able to apply it to send the special password inside shellscript where the data on the middle of it is a variable. I believe that my script reproduce the issue. Any solution? thanks. – J. Fox

3 Answers

2
votes

Configure the config file sqlnet.ora for an easy connection.

NAMES.DIRECTORY_PATH= (TNSNAMES,ezconnect)

Change the password @T!ger to the user "Scott".

oracle@esmd:~>
oracle@esmd:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:05:04 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> alter user "Scott" identified by "@T!ger";

User altered.

Example 1 Script is test_echo.sh

    #!/bin/sh

    username=\"Scott\"
    password=\"@T!ger\"
    ezconnect=10.89.251.205:1521/esmd

    echo username:  $username
    echo password:  $password
    echo ezconnect  $ezconnect

 echo -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus  $username/$password@$ezconnect

oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
ezconnect 10.89.251.205:1521/esmd

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:02:52 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> USER is "Scott"
SQL>
         1
----------
         1

SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Example 2 Run script test_echo.sh in silent mode sqlplus 

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
ezconnect=10.89.251.205:1521/esmd

echo username:  $username
echo password:  $password
echo ezconnect  $ezconnect
echo -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus -s  $username/$password@$ezconnect

oracle@esmd:~> oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
ezconnect 10.89.251.205:1521/esmd
USER is "Scott"

         1
----------
         1

Example 3 A little bit Another syntax

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
ezconnect=10.89.251.205:1521/esmd


echo username:  $username
echo password:  $password
echo ezconnect: $ezconnect

testoutput=$(sqlplus -s $username/$password@$ezconnect  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual
exit;
EOF
)

echo $testoutput

oracle@esmd:~> ./test_Upper_case.sh
username: "Scott"
password: "@T!ger"
ezconnect: 10.89.251.205:1521/esmd
USER is "Scott" 29-01-2018 11:55 Test passed
1
votes

i assume you issued this to alter your user's password :

alter user johnF identified by "!p@ssword";

since

alter user johnF identified by !p@ssword;

doesn't conforms oracle password definition rules.

and then it's enough to write such a script in your file to connect your schema :

#!/bin/bash
# cnn.sh
line '"!p@ssword"'
echo line
sqlplus johnF/[email protected]:1521/yourSID

and call from prompt :

$ . cnn.sh
0
votes

I've encountered the same problem here as well (which really drives me crazy), and this is my answer.

All the special characters allowed in Oracle could be found on this page: https://docs.oracle.com/cd/E11223_01/doc.910/e11197/app_special_char.htm#MCMAD416

  1. If your Oracle password contains any of the special characters on the above page except for a single quotation mark. #o@%+!$(){}[]/^?:`~ for example.

You can use it like this:

sh test.sh oracle_user '"#o@%+!$(){}[]/\^?:`~"' ip port service_name
  1. If your Oracle password contains a single quotation mark. #o@%+!$(){}[]/^?:`~' for example.

You can use it like this:

sh test.sh oracle_user '"#o@%+!$(){}[]/\^?:`~'"'"'"' ip port service_name

Please be noted that the single quotation mark ' should be replaced with '""'.