Re: [SQL] Odd characters in inserted data...

On Wed, 2 Dec 1998, Gregory W Burnham wrote:

> >PETER PAULY wrote:
> >
> >> I'm using the "C" interface to write CGI code for a web application.  I
> allow
> >> the user to type data into a particular field, and am storing that data
> into a
> >> field in a postgres database.
> >>
> >> 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
> >
> >you should substitute single quote with two single quotes
>
> You can also (keeping with 'C' tradition) substitute \' for the single
> quote.

Here is a small C function which escapes a null terminated array of chars
properly. It should be used as

char *dest = alloca(strlen(user_input * 2) + 1);

...

printf("...'%s'...", escape_string(dest, user_input));


char *
escape_string(char *to, char *from)
{
    char *start = to;
    char c;

    for( ; (c = *from); from++) {
        switch(c) {
            case '\\':
            case '\'':
            case '\"':
                *to++ = '\\';
            default:
                *to++ = c;
        }
    }
    *to = '\0';
    return start;
}



          Regards,

                            Sascha Schumann |
                                 Consultant | finger sas@schell.de
                                            | for PGP public key