pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Odd characters in inserted data...
7+ messages / 7 participants
[nested] [flat]

* Odd characters in inserted data...
@ 1998-11-29 15:29  PETER PAULY <ppauly@usa.net>
  0 siblings, 1 reply; 7+ messages in thread

From: PETER PAULY @ 1998-11-29 15:29 UTC (permalink / raw)
  To: pgsql-sql

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
there a better way to pass data to these commands, than to build a command
string like you see above?   My preference would be to pass a pointer to the
data, or something like that.  (same issue with insert). 

____________________________________________________________________
Get free e-mail and a permanent address at http://www.netaddress.com/?N=1



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Odd characters in inserted data...
@ 1998-11-30 16:09  Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 1998-11-30 16:09 UTC (permalink / raw)
  To: PETER PAULY <ppauly@usa.net>; +Cc: pgsql-sql@hub.org

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Re: Odd characters in inserted data...
@ 1998-11-30 17:08  Jan Wieck <jwieck@debis.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: jwieck@debis.com @ 1998-11-30 17:08 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: ppauly@usa.net; pgsql-sql@hub.org

Tom Lane wrote:

> 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

    To  get  out of this, the function SQLstring(char *str) could
    use malloc'ed memory to return it's result and  remember  all
    the chunks that it allocated in some static held array. After
    all, another function SQLstring_free() might be  called  that
    has  to  tidy up.  The array itself is also malloc'ed and can
    grow if many strings are used.

    Still not that pretty, but it's half way of  automation.  And
    if  the  SQLstring_free() is forgotten once, never mind - the
    next call will do it anyway.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Odd characters in inserted data...
@ 1998-11-30 17:09  Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 7+ messages in thread

From: Herouth Maoz @ 1998-11-30 17:09 UTC (permalink / raw)
  To: PETER PAULY <ppauly@usa.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces@postgresql.org

(Redirected to the INTERFACES list, which is more appropriate for this than
the SQL list)

At 18:09 +0200 on 30/11/98, Tom Lane wrote:


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

If it were me, I'd malloc the pointer inside the function, and explicitly
free it after sprintf'ing. If you want to be more elegant (in C???), you
can malloc it in advance, pass the malloced pointer to the function and
free it afterwards, to make it consistent with the way str functions work.

I should think this was a good function to add to libpq. At least, the part
that does the quoting, maybe without the other frills. It's a function
*everybody* who uses C will use, and it makes more sense to have it
centralized, so if characters other than backslash and quote need to be
escaped, it will be done within the central source, and not break
everybody's programs.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Odd characters in inserted data...
@ 1998-12-01 09:16  S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>
  parent: PETER PAULY <ppauly@usa.net>
  0 siblings, 0 replies; 7+ messages in thread

From: S.Ramaswamy @ 1998-12-01 09:16 UTC (permalink / raw)
  To: PETER PAULY <ppauly@usa.net>; +Cc: pgsql-sql

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


> there a better way to pass data to these commands, than to build a command
> string like you see above?   My preference would be to pass a pointer to the
> data, or something like that.  (same issue with insert).
>
> ____________________________________________________________________
> Get free e-mail and a permanent address at http://www.netaddress.com/?N=1



--
___________________________________________________________________________
S.Ramaswamy
Matrix Infotech Syndicate
D-7, Poorti, Vikaspuri, New Delhi, 110018, India
PHONE: +91-11-5610050,   FAX: +91-11-5535103
WEB  :   http://MatrixInfotech.HyperMart.Net






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Odd characters in inserted data...
@ 1998-12-02 16:55  Gregory W Burnham <gburnham@sfu.ca>
  0 siblings, 1 reply; 7+ messages in thread

From: Gregory W Burnham @ 1998-12-02 16:55 UTC (permalink / raw)
  To: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>; PETER PAULY <ppauly@usa.net>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Odd characters in inserted data...
@ 1998-12-07 18:19  Sascha Schumann <sas@schell.de>
  parent: Gregory W Burnham <gburnham@sfu.ca>
  0 siblings, 0 replies; 7+ messages in thread

From: Sascha Schumann @ 1998-12-07 18:19 UTC (permalink / raw)
  To: Gregory W Burnham <gburnham@sfu.ca>; +Cc: S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>; PETER PAULY <ppauly@usa.net>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1998-12-07 18:19 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-29 15:29 Odd characters in inserted data... PETER PAULY <ppauly@usa.net>
1998-12-01 09:16 ` S.Ramaswamy <srswamy@giasdl01.vsnl.net.in>
1998-11-30 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
1998-11-30 17:08   ` Jan Wieck <jwieck@debis.com>
1998-11-30 17:09 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-12-02 16:55 Re: [SQL] Odd characters in inserted data... Gregory W Burnham <gburnham@sfu.ca>
1998-12-07 18:19 ` Sascha Schumann <sas@schell.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox