Thread: Problem with character ' (single quote) in text fields
Hi,
I have an old problem using the single quote (') character in text field
because Postgresql thinks it is the "end of text" character. The '
characther is used a lot in my language. As a workaround I'm parsing the
fields duplicating the ' characters inside the text but this is a bad
solution because:
Assuming field1 and field2 are text field, as I usually use in my
programs something like :
...
sprintf (auxstr, "SELECT field WHERE field=%s AND field2=%s" , vartext1,
vartext2);
..
As vartext1 and vartext2 can contain the ' so I need to create char[size]
variables to save the output of my "duplicate ' characters" function.
Something like:
char[SIZE] vartext1;
char[SIZE] vartext2;
DuplicateSingleQuote (OriginalText1, vartext1); //vartext1 is the new
valid text to insert
DuplicateSingleQuote(OriginalText2, vartext2);
sprintf(...)
So the code is full of temporary chart[] variables.
Is there any way to change the start/end of Text field character or do you
know a better workaround to avoid parsing/changing all the text fields just
in case they contain the single quote ( ') character??.
Best regards,
Jordi
On 21.01.02 11:42 +0100(+0000), Jordi wrote: > Is there any way to change the start/end of Text field character or do you > know a better workaround to avoid parsing/changing all the text fields just > in case they contain the single quote ( ') character??. > In case the data comes from outside you *must* escape it. Libpq defines a function for it called PQescapeString. Consider what would happen if you had code like this: char buf[BUF_SIZE]; snprintf(buf,BUF_SIZE,"SELECT * FROM mytable WHERE field='%s';",string_from_user); PQexec(con,buf); now lets say the user would enter a value like 0';DELETE FROM mytable;SELECT '1 and the db would see SELECT * FROM mytable WHERE field='0';DELETE FROM mytable;SELECT '1'; - Einar Karttunen
Einar Karttunen wrote: > > On 21.01.02 11:42 +0100(+0000), Jordi wrote: > > Is there any way to change the start/end of Text field character or do you > > know a better workaround to avoid parsing/changing all the text fields just > > in case they contain the single quote ( ') character??. > > > > In case the data comes from outside you *must* escape it. Libpq defines a > function for it called PQescapeString. Consider what would happen if you > had code like this: > > char buf[BUF_SIZE]; > snprintf(buf,BUF_SIZE,"SELECT * FROM mytable WHERE field='%s';",string_from_user); > PQexec(con,buf); > > now lets say the user would enter a value like > 0';DELETE FROM mytable;SELECT '1 > and the db would see > SELECT * FROM mytable WHERE field='0';DELETE FROM mytable;SELECT '1'; > > - Einar Karttunen If I recall correctly, this was a security bug in older versions of the contributed Apache module, mod_auth_pgsql. It would be nice to have a configuration option (GUC) to disallow the submission of multiple statements in a single Query cycle in the FE/BE protocol, just as an extra security option. I'm not sure if any of the standard tools (i.e. pg_dump, psql) use multiple statements per query, though. And, of course, its no substitute for fixing the actual problem. But imagine if there was standard protection against buffer overflows... Just a thought, Mike Mascari mascarm@mascari.com
In my case the data is load from a file (for example a very long list of
Names/FirstNames) so this security concert should be not applicable here
(anyway, thanks by the remark because I have never though about it...).
The best solution for me should be if I could change the "start/end of a
Postgresql string" from the single quote to the double quote (") or a
similar character because now I have to do my own version of PQescapeString
for every text field assuming the ' character could be there. Anyone knows
if is possible to change it without modifying the Postgresql source code?
Thanks,
Jordi
"Einar Karttunen" <ekarttun@cs.helsinki.fi> wrote in message
news:20020121113549.GC25853@shellak.helsinki.fi...
> On 21.01.02 11:42 +0100(+0000), Jordi wrote:
> > Is there any way to change the start/end of Text field character or do
you
> > know a better workaround to avoid parsing/changing all the text fields
just
> > in case they contain the single quote ( ') character??.
> >
> In case the data comes from outside you *must* escape it. Libpq defines a
> function for it called PQescapeString. Consider what would happen if you
> had code like this:
>
> char buf[BUF_SIZE];
> snprintf(buf,BUF_SIZE,"SELECT * FROM mytable WHERE
field='%s';",string_from_user);
> PQexec(con,buf);
>
> now lets say the user would enter a value like
> 0';DELETE FROM mytable;SELECT '1
> and the db would see
> SELECT * FROM mytable WHERE field='0';DELETE FROM mytable;SELECT '1';
>
> - Einar Karttunen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
Jordi wrote:
>
> In my case the data is load from a file (for example a very long list of
> Names/FirstNames) so this security concert should be not applicable here
> (anyway, thanks by the remark because I have never though about it...).
>
> The best solution for me should be if I could change the "start/end of a
> Postgresql string"
As you want to load loads of rows into your database you are probably
better of using COPY, which supports custom delimiters.
With kind regards / Mit freundlichem Gruß
Holger Klawitter
--
Holger Klawitter
holger@klawitter.de http://www.klawitter.de
On Mon, 21 Jan 2002, Jordi wrote:
> The best solution for me should be if I could change the "start/end of a
> Postgresql string" from the single quote to the double quote (") or a
> similar character because now I have to do my own version of PQescapeString
> for every text field assuming the ' character could be there. Anyone knows
> if is possible to change it without modifying the Postgresql source code?
Double quotes (") already have a meaning for doing stuff with mixed case
attribute and table names, so I don't think that there's going to be a way
to change the quote character without modification.
Yes, this is the method I use but the problem is that I need to parse/change
every text. For example, if I have a struct Person with a char *Firstname =
"D'Innocenzo" I can not use:
sprintf (output, "select X where FirstName=%s and Description=%s",
Person.Firstname, Person.Description)
so I need a lot of temporary buffers just to create the SELECT... and some
of the structures I need to dump to the database have lots of text fields...
Anyway, it is not a big problem but I tried to find a workaround but I
think it doesn't exist.
Jordi
"Asim Khan" <goaway@byteme.com> wrote in message
news:zB638.55451$zk4.12540147@typhoon.columbus.rr.com...
> Try this...where ever you have single quote, replace it with two single
> quotes.
>
> Example:
>
> 'John's cat is sitting on the table' will become 'John''s cat is sitting
on
> the table'
>
> AK
>
> "Jordi" <jordil2@hotmail.com> wrote in message
> news:a2gqr0$968$1@news.tht.net...
> > Hi,
> >
> > I have an old problem using the single quote (') character in text field
> > because Postgresql thinks it is the "end of text" character. The '
> > characther is used a lot in my language. As a workaround I'm parsing
the
> > fields duplicating the ' characters inside the text but this is a bad
> > solution because:
> >
> > Assuming field1 and field2 are text field, as I usually use in my
> > programs something like :
> > ...
> > sprintf (auxstr, "SELECT field WHERE field=%s AND field2=%s" ,
> vartext1,
> > vartext2);
> > ..
> >
> > As vartext1 and vartext2 can contain the ' so I need to create
> char[size]
> > variables to save the output of my "duplicate ' characters" function.
> > Something like:
> >
> > char[SIZE] vartext1;
> > char[SIZE] vartext2;
> >
> > DuplicateSingleQuote (OriginalText1, vartext1); //vartext1 is the new
> > valid text to insert
> > DuplicateSingleQuote(OriginalText2, vartext2);
> > sprintf(...)
> >
> > So the code is full of temporary chart[] variables.
> >
> > Is there any way to change the start/end of Text field character or do
you
> > know a better workaround to avoid parsing/changing all the text fields
> just
> > in case they contain the single quote ( ') character??.
> >
> > Best regards,
> >
> > Jordi
> >
> >
>
>
On Tue, Jan 22, 2002 at 10:27:24AM +0100, Jordi wrote:
> Yes, this is the method I use but the problem is that I need to parse/change
> every text. For example, if I have a struct Person with a char *Firstname =
> "D'Innocenzo" I can not use:
>
> sprintf (output, "select X where FirstName=%s and Description=%s",
> Person.Firstname, Person.Description)
>
> so I need a lot of temporary buffers just to create the SELECT... and some
> of the structures I need to dump to the database have lots of text fields...
>
> Anyway, it is not a big problem but I tried to find a workaround but I
> think it doesn't exist.
I beleive that the DB interface layers do this for you if you use the
prepare type statements. If I really had to do this a lot I would consider
writing my own function like this:
mydbsprintf( output, "select * from t where firstname = '$' and description = '$';",
"D'Innocenzo", "blah" );
Then that function can do the escaping for you. Then you only need one
buffer to store the final query before executing it.
Actually, I've thought that we could take a leaf out of Perl's book w.r.t.
generalised quoting. Like q<delimeter>blah<delimeter>. Then you could write
queries like:
select * from t where firstname = q[D'Innocenzo] and description = q:blah:;
Since square brackets are much rarer than single quotes, it's just not an
issue. Thoroughly non-portable though. Another possibility would be to quote
by length instead. Maybe q@<number>@<n bytes>@. Then you get:
select * from t where firstname = q@11@D'Innocenzo@ and description = q@4@blah@;
Ofcourse the strings would be allowed to have embedded @ symbols.
Just silly ideas to throw around. Implementationwise they're not difficult,
it's just deciding whether it's a good idea or not.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.
> I beleive that the DB interface layers do this for you if you use the > prepare type statements. I really don't know these statements. Where can I find information about them?. By the way, I'm using C with libpq to access Postgresql. > If I really had to do this a lot I would consider > writing my own function like this: > > mydbsprintf( output, "select * from t where firstname = '$' and description = '$';", > "D'Innocenzo", "blah" ); > > Then that function can do the escaping for you. Then you only need one > buffer to store the final query before executing it. Do you mean create my own version of the sprintf function? It seems a good idea to avoid having to work so many auxiliar buffers... I'm going to look at the gcc source code for the sprintf implementation to see if it is not too difficult... Thanks, Jordi