Thread: Odd characters in inserted data...
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
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
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) #
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