pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Odd characters in inserted data...
3+ messages / 3 participants
[nested] [flat]
* Re: Odd characters in inserted data...
@ 1998-11-30 16:09 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 3+ 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] 3+ 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; 3+ 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] 3+ 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; 3+ 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
(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] 3+ messages in thread
end of thread, other threads:[~1998-11-30 17:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
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>
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