“set -o noglob” in bash shell script

Question

I would usually write SQL statements inline in a Bash shell script to be executed in SQLPlus as-

#! /bin/sh

sqlplus user/pwd@dbname<<EOF
insert into dummy1 
select * from dummy2;

commit;
exit;
EOF

This would work just fine and will insert rows into dummy1 when executed. A colleague of mine came to me the other day with a script like below (simplified)

#! /bin/sh    
sqlvar="insert into dummy1 select * from dummy2;commit;"    
echo $sqlvar|sqlplus user/pwd@dbname

The issue with this is when executed the variable sqlvar expands the * to be all the files in the current directory and would eventually error out like-

SQL> insert into dummy1 select <--all the file names in the current directory--> 
from dummy2;commit
                                                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Our first stance on this one was the shell was interpreting * in a wildcard context and listing all the file names while the shell variable expansion (not quite sure why....???). So, in order to understand this we did something like below-

$ var="hello *"
$ echo $var
hello <--all the file names in the current directory-->

But

$*
ksh: somefile.sh: 0403-006 Execute permission denied. #since it had no execute permission

There are a number of other files in the directory and I am not sure why * chose to execute somefile.sh or is pointed to somefile.sh.

After, a bit of digging we realized, using set -o noglob would resolve this issue entirely, like-

#! /bin/sh
set -o noglob
sqlvar="insert into dummy1 select * from dummy2;\n commit;"    
echo $sqlvar|sqlplus user/pwd@dbname

There are some conflicting or rather contradictory description of setting noglob, over the internet. So I am looking if someone could explain the knick knacks of this bit.

Answer 1

Before I begin: @John Kugelman's answer (appropriate quoting) is the right way to solve this problem. Setting noglob only solves some variants of the problem, and creates other potential problems in the process.

But since you asked what set -o noglob does, here are the relevant excerpts from the ksh man page (BTW, your tags say bash, but the error message says ksh. I presume you're actually using ksh).

noglob  Same as -f.

-f      Disables file name generation.

File Name Generation.
   Following splitting, each field is scanned for the characters *, ?,  (,
   and  [  unless  the -f option has been set.  If one of these characters
   appears, then the word is regarded as a pattern.  Each file name compo-
   nent  that  contains  any  pattern character is replaced with a lexico-
   graphically sorted set of names that  matches  the  pattern  from  that
   directory.

So what does that mean? Here's a quick example that should show the effect:

$ echo *
file1 file2 file3 file4
$ ls *
file1 file2 file3 file4
$ *    # Note that this is equivalent to typing "file1 file2 file3 file4" as a command -- file1 is treated as the command (which doesn't exist), the rest as arguments to it
ksh: file1: not found

Now watch what changes with noglob set:

$ set -o noglob
$ echo *
*
$ ls *
ls: *: No such file or directory
$ *
ksh: *: not found

Answer 2

After, a bit of digging we realized, using set -o noglob would resolve this issue entirely

It doesn't resolve the issue so much as it hides it. The issue at hand is the lack of quoting. Quoting variables is usually a good practice as it prevents the shell from doing unexpected things when the variable contains special characters, whitespace, etc.

Disabling globbing does prevent the * from being expanded, but that's generally not something you want to do. It'll let you use * and ?, but things could break if you used other special characters.

There are a number of other files in the directory and I am not sure why * chose to execute somefile.sh or is pointed to somefile.sh.

Here * expands to all of the file names in the current directory, and then this file list becomes the command line. The shell ends up trying to execute whichever file name is first alphabetically.

---

So, the right way to fix this is to quote the variable:

echo "$sqlvar" | sqlplus user/pwd@dbname

That will solve the wildcard problem. The other issue is that you need the \n escape sequence to be interpreted as a newline. The shell doesn't do this automatically. To get \n to work either use echo -e:

echo -e "$sqlvar" | sqlplus user/pwd@dbname

Or use the string literal syntax $'...'. That's single quotes with a dollar sign in front.

sqlvar=$'insert into dummy1 select * from dummy2;\n commit;'
echo "$sqlvar" | sqlplus user/pwd@dbname

(Or delete the newline.)