Re: UPDATE syntax problem

MT <mt@open2web.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