Re: UPDATE syntax problem

MT <mt(at)open2web(dot)com> writes:

> Hello,
>
> I would just like to follow up on what you suggested since it went a
> little over my head.
>
> > A couple points:
> >
> > 1) You're wide open to an SQL injection attack.
>
> What's that?

STFW.

http://www.google.com/search?hl=en&ie=ISO-8859-1&q=SQL+injection+attack

>
> > You'll need to
> > preprocess the parameter values to make sure single quotes are
> > properly escaped before building the SQL statement.
>
> Do you mean:
>
> string category = \'param["new_prodname"]\'
>
> Does this prevent an sql injection attack?

Not quite--you need to look inside the string for single quote
characters and escape them. It's a bit tedious but not hard.

> > 2) The code structure you're using is awkward--you have to add a new
> > clause if you add a parameter. I'd be more likely to make a list
> > of parameters, and loop through it checking for changed values and
> > adding clauses to the SQL statement (this would be a good time to
> > do the quote escaping). If no values have changed, just don't
> > execute the SQL at all (your code doesn't handle this case).
>
> I'm not sure how this is done. I would appreciate it if you could
> elaborate on this by perhaps providing a quick example.

Sure. I don't actually know C++ that well--I use Perl and Java
generally, and I've been writing Perl tonight, so the below is mostly
Perlish--but you should be able to follow along:

$any_changed = 0; # false
$sql = "UPDATE mytable SET ";
@param_list = ('catid', 'prodname', 'unitcnt', 'price'); # create a list
foreach $p (@param_list) { # iterate through it
if ($param{"old_$p"} ne $param{"new_$p"}) {
$any_changed = 1;
if ($p ne $param_list[0]) { # if we're not on the first element
$sql .= ", "; # put in a comma
}
$fixed_param = escape_param($param{"new_$p"}); # escape single quotes
$sql .= "$p = '" . $fixed_param . "'";
}
if ($any_changed) {
$sql .= " WHERE prod_code = '4455GGF'";
exec_sql($sql);
}
}

Here, '.' is the Perl string concatenation operator (instead of '+')
and variable values are interpolated into double-quoted strings for
you (so "new_$p" ends up being "new_catid", say). '#' denotes a
comment just as '//' does in C++.

You get the idea? This way, if you add a parameter, you just add it
to the array, rather than copy/pasting a bunch of code and hacking it
around.

> This works, but I'm always interested in finding better ways to do
> things. Your way looks better. I realize this is more a programming
> question than a postgres question. By the way, should I be using
> transactions if I do it this way, or the way you have suggested?

Basically, you should use transactions any time you want to execute
two or more SQL statements that should be seen as a unit by other
database users.

-Doug