Re: UPDATE syntax problem

MT <mt@open2web.com> writes:

> Hi,
>
> I'm developing a C++ script to update postgresql database records. The
> user interacts with the script via an html form. That is, the user is
> presented with the data from a particular record in an html form and
> asked to update any number of fields in that record.
>
> To perform a multiple column update in postgres one does:
>
> UPDATE tablename
>           SET column1 = 'blahblah',
>               column2 = 'moreblahblah',
>               column3 = 1234
>           WHERE id = 555;
>
> Here's an excerpt from the script:

[snip]

A couple points:

1) You're wide open to an SQL injection attack.  You'll need to
   preprocess the parameter values to make sure single quotes are
   properly escaped before building the SQL statement.

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).

Try to think at a higher level of abstraction.

> Now I could probably solve this problem by building separate UPDATE
> statements as such:
>
>     if (param["new_catid"] != param["old_catid"])
>     {
>         sql = "UPDATE product SET ";
>         sql += "catid = " + param["new_catid"];
>         sql += "WHERE prodid = '" + param["prodid"] + "'";
>         int res = conn.Exec (sql.c_str()); //sql exec
>     }
>     else if (param["new_catname"] != param["old_catname"])
>     {
>         sql = "UPDATE product SET ";
>         sql += "prodname = '" + param["new_catname"] + "'";
>         sql += "WHERE prodid = '" + param["prodid"] + "'";
>         int res = conn.Exec (sql.c_str()); //sql exec
>     }
>
> This necessitates calling the database each time the if statement is
> true. Is there perhaps a more efficient way of doing this? I'm assuming
> that the fewer times you call the database the faster the program will run.

Surely.  Whether it's an issue for you depends on how busy the site is
likely to be.  You should also use transactions if you do it this way
to make sure other users don't see a half-updated record.

-Doug