Re: Odd characters in inserted data...

PETER PAULY <ppauly@usa.net> writes:
> The problem is, I have to filter the data that the user entered to
> remove any single quotes and other odd characters so that my SQL
> command doesn't get messed up.  I'm building the command with printf
> and passing the filtered data from the user as so:

> update tablename set comment = '%s' where .....

> And %s is substituted in the printf with the user data. If the user
> typed in a single quote, it would cause havoc with the sql statement.
> My question is, is there a better way to pass data to these commands,

You do not have to prevent the user from entering special characters,
you just have to quote them with backslashes.  For example, if the
user types
    Joe's Diner
you want to emit SQL like
    update tablename set comment = 'Joe\'s Diner' where .....
As far as I know, only ' (single quote) and \ (backslash) need to be
escaped with a backslash, but I haven't dug into the backend to check.

Now, as to mechanism, what I currently do is to define a function
    const char * SQLstring (const char * str)
which returns "NULL" if str is NULL, otherwise the string surrounded
with single quotes and having internal special characters escaped.
Then the SQL statement is constructed with
    sprintf(query, "update tablename set comment = %s where ...",
        SQLstring(str), ...);
Notice that by having the quotes stuck on by SQLstring, not by the printf
format, I can deal easily with null field values.

This is a lot easier to get right in C++, where the function can return
a temporary string object that will get deallocated automatically after
use.  In C the most reasonable way to proceed is to have SQLstring
return a pointer to a static area, which means you can't invoke
SQLstring more than once per sprintf :-(.  You end up with klugery like
    sprintf(query, "update tablename set field1 = %s,",
        SQLstring(field1));
    sprintf(query + strlen(query), " field2 = %s where ...",
        SQLstring(field2), ...);
Ugh.  It works (I've done it a lot), but it's not pretty, and it's
easy to get wrong.

Even in C++, constructing an insert or update statement for a table with
many fields is pretty painful, especially if a lot of the fields are
optional or may not need to be written out at a particular time.
As I'm writing this I'm thinking about a C++ class that would automate
the construction of such a query, say
    UpdateQuery query("tablename");
    query.AddField("field1", field1value);
    if (field2 needs updated)
        query.AddField("field2", field2value);
    query.Where("condition text");
    SQLconnection->exec(query.AsText());
You could overload AddField to handle (at least) int, float, and char*
second arguments in the natural way; and AddField for a char* second
argument would know about escaping ' and \ ...

(If anyone gets around to writing this, send me a copy, willya?)

            regards, tom lane