On Wed, 2009-11-25 at 16:20 +0100, David FOXWELL wrote:
Say what?
I thought you would never ask!
First, the central part of the code, with line numbers for reference
1 chgvar &cmdstr +
2 ( '+
3 db2 "insert into v40lbtxe.filea values( +
4 $( q1="''" IFS='';'' ; +It is here that the
input is split into four words.
5 declare -i len=${#MyVar}-1 ; +
6 MyVarTruncated=${MyVar:0:$len} ; +
7 list=$q1 sep='''' ; +
8 for v in $MyVarTruncated ; do +
9 list="$list$sep$v" sep="$q1, $q1" ; +
10 done ; +
11 list="$list$q1" ; +
12 echo $list ; +
13 ) ) " +
14 ')
15 qsh cmd(&cmdstr)
Lines 1, 2, 14, 15 amount to "Listen up. Here's wot youse gotta do."
Line 3, and line 13 from second closing parenthesis to the end say "you
gotta put a row with some values into FILEA".
The leading "$( on line 4 and the leading ")" on line 13 say "here is
the program to tell you what those values are. Note that this structure
invokes a subshell, so that we need worry about embedded assignments,
notably the assignment to IFS, having any effect outside this bit of
code.
The first assignment in line 4 assigns the name q1 to a single quotation
mark. We need the single quote to delimit string constants in the SQL
statement. The variable reference helps to hide the quotation marks
from the shell interpreter and the command compiler, each of which is
wont to eat our carefully placed quotation marks for its own purposes.
The assignment to IFS on line 4 tells the shell interpreter that "words"
are separated by semicolons. The goal is to execute the loop body on
line 9 four times, with v taking on values Abcd, cdef, ghijk, and
12345678aa in succession.
Lines 5, 6 strip the trailing semicolon from the input variable, for the
convenience of later code. Now that I think about it, I think I could
have written this better as
ïMyVarTruncated=${MyVar##;\$}
Line 7 prepares for iteration through the values. list is the
comma-separated list of values to insert. We start it here with a
single quote which will start the first value; the rererence to $q on
line 11 provides the single quote which will end the last value. sep is
what goes between values in the list. The loop body puts $sep in front
of every value, and that is wrong for the first value; so here we set
sep to the null string.
Line 8 starts an iteration over the four values in turn. The bit `in
$MyVarTruncated` is where MyVarTruncated magically expends into four
values.
Line 9 first appends to list $sep, which is null the first time, and $v
which is the bare-naked next value. Then it assigns to sep a single
quote to end the previous value, a comma because that is how SQL likes
its list, and a single quote to start the next value.
Line 11 closes the final value, a necessary nicety that line 9 never
quite got around to.
Line 12 returns the built $list of values back into the enclosing SQL
statement.
The second ') on line 13 terminates the VALUES() clause.
Phew! And Fooey, too.
I decided deliberately not to make a separate routine to encapsulate the
code which builds the list of values. Such encapsulation might mislead
a dozing reader to think that the routine is good for something, and
that is very, very far from the the truth. There are just too many
implicit assumptions. Here are the ones that come to mind.
- The code assumes that each value is to be wrapped in single
quotes, like you would for a character or varchar constant.
- The code assumes that no value contains a single quote. More
importantly, it assumes the absence of any other sql injection. If
the input comes from a user, this is a bad thing. Have I mentioned
"Little Bobby Tables" <
http://xkcd.com/327/> here lately?
- The code assumes that there is a value. I think--I am making this
up right now--that it concocts a null string out of an undefined MyVar.
(If MyVar were defined as a character variable, that would arguably
be the correct behaviour, but only if the trailing semicolon is
optional; the specs are silent on that question <grin />.)
Cheers,
Terry.
midrange.com
