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(at)schell(dot)de
| for PGP public key