1

I want to run a command such as this in a bash script:

freebcp <authentication and other parameters> -t "," -r "\r\n"

When run directly on the command line, the command works as expected. But when placed in a variable in a bash script it returns an error such as this:

Msg 20104, Level 3
Unexpected EOF encountered in bcp datafile

Msg 20074, Level 11
Attempt to bulk copy an oversized row to the server

bcp copy in failed

When command is placed in a variable and double quotes are escaped:

cmd="freebcp ${db_name}.dbo.${table_name} in ${p_file} -S ${server_name} -U ${username} -P ${password} -t \",\" -r \"\r\n\" -c"
`$cmd`

Note: Putting the following in the script works as expected:

`freebcp ${db_name}.dbo.${table_name} in ${p_file} -S ${server_name} -U ${username} -P ${password} -t "," -r "\r\n" -c`

So I know there's some quoting/escaping/expansion problems but I can't figure out how to fix it.

Note 2: Single quoting -t -r parameters doesn't work either

migrated from serverfault.com Jan 20 '15 at 11:46

This question came from our site for system and network administrators.

6

Short answer: see BashFAQ #50: I'm trying to put a command in a variable, but the complex cases always fail!.

Long answer: the shell does variable expansion partway through the process of parsing a command line -- notably, after it processes quotes and escapes. As a result, putting quotes and escapes in a variable doesn't do the same thing as having them directly on the command line.

The solution in your answer (doubling the escape characters) will work (in most cases), but not for the reason you think it's working, and that makes me rather nervous. The command:

cmd="freebcp ... -t "," -r "\\r\\n" -c"

Gets parsed into the double-quotesd string freebcp ... -t, followed by the unquoted string , followed by the double-quoted string -r, followed by the unquoted string '\\r\\n' (the fact that it's unquoted is why you needed to double the escapes), followed by the double-quoted string ' -c'. The double-quotes you meant to be part of the string aren't treated as part of the string, they're treated as delimiters that change how different parts of the string are parsed (and actually have pretty much the reverse of the intended effect). The reason this works is that the double-quotes actually weren't having much effect in the original command, so reversing their effect didn't do much. It would actually be better to remove them (just the internal ones, though), because it'd be less misleading about what's really going on. That'd work, but it'd be fragile -- the only reason it works is that you didn't really need the double-quotes to begin with, and if you had a situation (say, a password or filename with a space in it) where you actually needed quotes, you'd be in trouble.

There are several better options:

  • Don't store the command in a variable at all, just execute it directly. Storing commands is tricky (as you're finding), and if you don't really need to, just don't.

  • Use a function. If you're doing something like executing the same command over & over, define it as a function and use that:

    loaddb() {
        freebcp "${db_name}.dbo.${table_name}" in "${p_file}" -S "${server_name}" -U "${username}" -P "${password}" -t \",\" -r \"\r\n\" -c"
    }
    loaddb
    

    Note that I used double-quotes around all of the variable references -- this is generally good scripting hygiene, in case any of them contain whitespace, wildcards, or anything else that the shell does parse in variable values.

  • Use an array instead of a plain variable. If you do this properly, each command argument gets stored as a separate element of the array, and you can then expand it with the idiom "${arrayname[@]}" to get it out intact:

    cmdarray=(freebcp "${db_name}.dbo.${table_name}" in "${p_file}" -S "${server_name}" -U "${username}" -P "${password}" -t \",\" -r \"\r\n\" -c")
    "${cmdarray[@]}"
    

    Again, note the prolific use of double-quotes; here they're being used to make sure the array elements are defined properly, not as part of the values stored in the array. Also, note that arrays aren't available in all shells; make sure you're using bash or zsh or something similar.

A couple of final notes: when you use something like:

`somecommand`

the backquotes aren't doing what you seem to think they are, and in fact they're potentially dangerous. What they do is execute the command, capture its output, and try to execute that output as another command. If the command doesn't print anything, this doesn't matter; but if it does print something, it's unlikely the output will be a valid command. Just lose the backquotes.

Lastly, giving a password as a command argument is insecure -- command arguments published in the process table (for example, the ps command can see them), and publishing passwords in public locations is a really bad idea. freebcp doesn't seem to have any alternative way to do this, but I found a patch that'll let it read the password from stdin (echo "$password" | freebcp -P - ... -- note that echo is a shell builtin, so its arguments don't show up in the process table). I make no claims about the correctness, safety, etc of the patch (especially since it's rather old), but I'd check it out if I were you.

1

Escaping the backslash in the \n\r seems to have done the trick

cmd="freebcp ${db_name}.dbo.${table_name} in ${p_file} -S ${server_name} -U ${username} -P ${password} -t "," -r "\\r\\n" -c"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.