Apostrophes and SQL (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: [ANN] Update: UniHelp 1.2 Module for REALbasic
Next thread: Waiting for App to open Completely?


Serializable ... kind of   -   Jan Erik Moström <
  Apostrophes and SQL   -   George Barnett
   Re: Apostrophes and SQL   -   User SCARR
   Re: Apostrophes and SQL   -   Jean-Renaud Margelidon
   Re: Apostrophes and SQL   -   Totte Alm
    Re: Apostrophes and SQL   -   Kevin Wojniak
     Re: Apostrophes and SQL   -   Didier Barbas
      Re: Apostrophes and SQL   -   Kevin Wojniak

Apostrophes and SQL
Date: 06.05.02 16:14 (Mon, 06 May 2002 09:14:00 -0600)
From: George Barnett
I have a data base in which one of the fields calls for the use of an
apostrophe ( ' ). The data base is a life list for birders (of which
there must be dozens on this list alone). Some species may have a name
like Say's Phoebe or Audubon's Warbler. But it seems that the use of
the apostrophe can cause some SQL commands to do strange things, like
list the same species twice. A search on "Say's" might turn up nothing
or just a couple of the proper records, but a search on "Say" would
yield all of the appropriate records.

Does anyone know of a work around for this or provide any advice on how
to proceed?

Thanks,
George

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Apostrophes and SQL
Date: 06.05.02 16:41 (Mon, 6 May 2002 11:41:52 -0400 (EDT))
From: User SCARR
On Mon, 6 May 2002, George Barnett wrote:
> I have a data base in which one of the fields calls for the use of an
> apostrophe ( ' ). The data base is a life list for birders (of which
> there must be dozens on this list alone). Some species may have a name
> like Say's Phoebe or Audubon's Warbler. But it seems that the use of
> the apostrophe can cause some SQL commands to do strange things, like
> list the same species twice. A search on "Say's" might turn up nothing
> or just a couple of the proper records, but a search on "Say" would
> yield all of the appropriate records.
> Does anyone know of a work around for this or provide any advice on how
> to proceed?

Not tested locally, but best bet is probably to escape all 's and special
meaning type characters with a \ before entering into data. So let's say
you have your input add that when somebody enters Say's Phoebe.

Eg: they put in Say's Phoebe, your program reports "Say\'s Phoebe" to the
SQL database. It'd probably mean a little bit of a kludge though?

Re: Apostrophes and SQL
Date: 06.05.02 16:59 (Mon, 06 May 2002 17:59:31 +0200)
From: Jean-Renaud Margelidon
replaceall(MySqlValue, "'", "''")

Re: Apostrophes and SQL
Date: 06.05.02 17:19 (Mon, 6 May 2002 18:19:25 +0200)
From: Totte Alm

On måndag, maj 6, 2002, at 05:14 , George Barnett wrote:

> I have a data base in which one of the fields calls for the use of an
> apostrophe ( ' ). The data base is a life list for birders (of which
> there must be dozens on this list alone). Some species may have a name
> like Say's Phoebe or Audubon's Warbler. But it seems that the use of
> the apostrophe can cause some SQL commands to do strange things, like
> list the same species twice. A search on "Say's" might turn up nothing
> or just a couple of the proper records, but a search on "Say" would
> yield all of the appropriate records.
>
> Does anyone know of a work around for this or provide any advice on how
> to proceed?
>

Hello,
one way is to substitute all ' with \' in your search.

// Totte


> Thanks,
> George
>
> ---
> Subscribe to the digest: <mailto:realbasic-nug-
> <email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>
---------------------------------------------------------------------------------------------------
It is against US Department of Agriculture regulations to advertise or
sell
as "Prime Rib" any cut of meat containing a non-prime number of ribs.

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Apostrophes and SQL
Date: 06.05.02 16:37 (Mon, 6 May 2002 11:37:18 -0400)
From: Kevin Wojniak
At Monday, 06 May 2002, you wrote:

>I have a data base in which one of the fields calls for the use of an
>apostrophe ( ' ). The data base is a life list for birders (of which
>there must be dozens on this list alone). Some species may have
a name
>like Say's Phoebe or Audubon's Warbler. But it seems that the use of
>the apostrophe can cause some SQL commands to do strange things, like
>list the same species twice. A search on "Say's" might turn up
nothing
>or just a couple of the proper records, but a search on "Say" would
>yield all of the appropriate records.
>
>Does anyone know of a work around for this or provide any advice
on how
>to proceed?

Try putting a \ before the single quotes. So you could use a replaceall(sql,
"'", "\'"). This has been a problem for me with my MySQL database
and PHP, but adding a slash before it usually solves it.

Kevin





---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: Apostrophes and SQL
Date: 07.05.02 00:07 (Tue, 07 May 2002 08:07:20 +0900)
From: Didier Barbas
On 5/7/02 12:37 AM, "Kevin Wojniak" <<email address removed>> wrote:

> At Monday, 06 May 2002, you wrote:
>
>> I have a data base in which one of the fields calls for the use of an
>> apostrophe ( ' ). The data base is a life list for birders (of which
>> there must be dozens on this list alone). Some species may have
> a name
>> like Say's Phoebe or Audubon's Warbler. But it seems that the use of
>> the apostrophe can cause some SQL commands to do strange things, like
>> list the same species twice. A search on "Say's" might turn up
> nothing
>> or just a couple of the proper records, but a search on "Say" would
>> yield all of the appropriate records.
>>
>> Does anyone know of a work around for this or provide any advice
> on how
>> to proceed?
>
> Try putting a \ before the single quotes. So you could use a replaceall(sql,
> "'", "\'"). This has been a problem for me with my MySQL database
> and PHP, but adding a slash before it usually solves it.
>
> Kevin
There shouldn't be any problem with PHP, though. Addslashes() and
stripslashes() escape and de-escape a string for db use. There is also a
mysql_escape_string() in the mysql module.
Just do the same. Add two function that add and remove slashes to ' " and \
and you're home free.

Re: Apostrophes and SQL
Date: 07.05.02 00:30 (Mon, 6 May 2002 19:30:16 -0400)
From: Kevin Wojniak

On Monday, May 6, 2002, at 07:07 PM, Didier Barbas wrote:
>

> There shouldn't be any problem with PHP, though. Addslashes() and
> stripslashes() escape and de-escape a string for db use. There
> is also a
> mysql_escape_string() in the mysql module.
> Just do the same. Add two function that add and remove slashes
> to ' " and \
> and you're home free.

There already is a PHP function for doing this? Dang, I just
used the str_replacea() function. Oh well, now I know better!!
Thanks,

Kevin

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>