Problem with character ' (single quote) in text fields

Problem with character ' (single quote) in text fields

Post by Jord » Tue, 22 Jan 2002 19:42:12



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

 
 
 

Problem with character ' (single quote) in text fields

Post by Asim Kha » Wed, 23 Jan 2002 14:17:19


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


Quote:> 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


 
 
 

Problem with character ' (single quote) in text fields

Post by Jord » Wed, 23 Jan 2002 18:27:24


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


> 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



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

 
 
 

1. Problem with character ' (single quote) in text fields


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

2. Issue with query path chosen by Informix 7.20 UC2 optimizer ***Help*** :*(

3. Single-quote and double-quote characters in fields

4. Apologies

5. Problem with single quote in text field

6. How to find all tables in db that have datetime columns?

7. Handling single quotes in field text

8. Best NewsGroup?

9. Single Quote Issue relating to Text Fields

10. Single Quote in text field

11. single quote and text fields

12. Single Quote in a text Field

13. Single Quotes converted to 2 single quotes