MySQL apostrophy with LIKE won't work.

**uws** · Jul 16 '05, 10:47 PM

Re: MySQL apostrophy with LIKE won't work.

I <bet9ev$4dt$1@s lb9.atl.mindspr ing.net>, Adams-Blake Co. skrev:[color=blue]
> $sql="Select * from Company where CompanyName =' " . addslashes($x) . " ' ";
> (standard MySQL query code)
>
> Does anyone have any ideas of what is happening here?[/color]

Have you tried:

echo $sql = "SELECT ...";

and examining (or pasting in a commandline mysql session) the generated
query?

mvrgr, Wouter

--
uws mail uws@xs4all.nl

i will hold you close :: if you're afraid of heights -- incubus

**Andy Hassall** · Jul 16 '05, 10:47 PM

Re: MySQL apostrophy with LIKE won't work.

On Mon, 14 Jul 2003 08:09:27 -0700, "Adams-Blake Co."
<atakeoutcanton @adams.takeme.o ut.-blake.com> wrote:
[color=blue]
>Adams-Blake Co. wrote:
>[color=green]
>> In table is record, field called CompanyName that has: Joe\'s Place (I
>> added the slashes before inserting the field.)
>>
>> This will NOT work:
>> $x="Joe's Place";
>> $sql="Select * from Company where CompanyName LIKE '%" . addslashes($x) .
>> "%'";
>> (standard MySQL query code)
>>
>> I don't get an error, but I don't get a hit either!
>>
>> Here is the quirk. Take out the LIKE and put in an = and it works:
>>
>> $sql="Select * from Company where CompanyName =' " . addslashes($x) . " ' ";
>> (standard MySQL query code)
>>
>> Does anyone have any ideas of what is happening here?
>>
>> Thanks,
>>
>> Al[/color]
>
>
>(responding to my own post)
>
>There is what does work:
>$sql="Select * from Company where CompanyName LIKE '%" .
>addslashes(add slashes($x)).
>
>In other words, you have to add TWO addslash functions.
>
>Why?[/color]

mysql> select * from liketest where c = 'Joe\\\'s place';
+--------------+
| c |
+--------------+
| Joe\'s place |
+--------------+
1 row in set (0.00 sec)

mysql> select * from liketest where c like 'Joe\\\'s place';
Empty set (0.00 sec)

mysql> select * from liketest where c like 'Joe\\\\\'s place';
+--------------+
| c |
+--------------+
| Joe\'s place |
+--------------+
1 row in set (0.00 sec)

The manual explains:

fw_error_www

http://www.mysql.com/doc/en/String_comparison_functions.html

"Note: Because MySQL uses the C escape syntax in strings (for example, `\n'),
you must double any `\' that you use in your LIKE strings. For example, to
search for `\n', specify it as `\\n'. To search for `\', specify it as `\\\\'
(the backslashes are stripped once by the parser and another time when the
pattern match is done, leaving a single backslash to be matched). Note:
Currently LIKE is not multi-byte character safe. Comparison is done character
by character. "

(Although why do you have the \ in your data anyway?)

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**Andy Hassall** · Jul 16 '05, 10:48 PM

Re: MySQL apostrophy with LIKE won't work.

On Mon, 14 Jul 2003 23:32:46 -0700, "Adams-Blake Co."
<atakeoutcanton @adams.takeme.o ut.-blake.com> wrote:
[color=blue]
>Because I used the "addslashes " function before I inserted the record. Isn't
>that the correct way:
>
>$CompanyName = "Joe's Place";
>Insert into mytable fld1= addslashes($Com panyName)....
>
>How else would you do it?[/color]

If you have:

Joe\'s place

... stored in the database, you've added slashes twice.

You should only add enough slashes so that the data gets to the database in
its original form.

If $CompanyName contains "Joe's Place" then doing one addslashes() as you say
is correct. This makes it "fld1='Joe\ 's Place'" in the SQL, and stores "Joe's
Place".

However if it's already "Joe\'s Place" then another addslashes makes it
"fld='Joe\\ \'s place'" in the SQL, and you store "Joe\'s Place" which wasn't
your original data.

Do you have one of the automatic escaping functions on, the magic_quotes*
settings? That would explain the double-escaping.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**Adams-Blake Co.** · Jul 16 '05, 10:48 PM

Re: MySQL apostrophy with LIKE won't work.

Andy Hassall wrote:
[color=blue]
> On Mon, 14 Jul 2003 23:32:46 -0700, "Adams-Blake Co."
> <atakeoutcanton @adams.takeme.o ut.-blake.com> wrote:
>[color=green]
>>Because I used the "addslashes " function before I inserted the record. Isn't
>>that the correct way:
>>
>>$CompanyNam e = "Joe's Place";
>>Insert into mytable fld1= addslashes($Com panyName)....
>>
>>How else would you do it?[/color]
>
> If you have:
>
> Joe\'s place
>
> ... stored in the database, you've added slashes twice.
>
> You should only add enough slashes so that the data gets to the database in
> its original form.
>
> If $CompanyName contains "Joe's Place" then doing one addslashes() as you
> say
> is correct. This makes it "fld1='Joe\ 's Place'" in the SQL, and stores
> "Joe's Place".
>
> However if it's already "Joe\'s Place" then another addslashes makes it
> "fld='Joe\\ \'s place'" in the SQL, and you store "Joe\'s Place" which wasn't
> your original data.
>
> Do you have one of the automatic escaping functions on, the magic_quotes*
> settings? That would explain the double-escaping.
>[/color]

Andy, et. al.

When I do add[color=blue][color=green]
>>$CompanyNam e = "Joe's Place";
>>Insert into mytable fld1= addslashes($Com panyName)....[/color][/color]

and look at the field name in phpMySQLAdmin for the record I see: Joe\'s
Place. So I assume that the slash is actually stored in the database. And
this is why when I do:

$recsql="select CompanyName from mytable";
$rs = $db->Execute($recsq l);
$cname= stripslashes($r s->Fields['CompanyName']);
(I use the ADODB wrapper)

Does the database table actually carry the slash? I don't know, but I see it
in MySQLAdmin.... so I figure I have to do the stripslashes. Everything
seems to work..... except when you do the "LIKE" search in SQL and you need 2
addslash functions.

I don't know if I have magic anything turned on. I know that the above code
works fine on my local Apache as well as whatever pair.com runs.

Let me know what you think regarding the double addslashes for Joe's Place.

-Al

**Adams-Blake Co.** · Jul 16 '05, 10:49 PM

Re: MySQL apostrophy with LIKE won't work.

Adams-Blake Co. wrote:
[color=blue]
> Andy Hassall wrote:
>[color=green]
>> On Mon, 14 Jul 2003 23:32:46 -0700, "Adams-Blake Co."
>> <atakeoutcanton @adams.takeme.o ut.-blake.com> wrote:
>>[color=darkred]
>>>Because I used the "addslashes " function before I inserted the record.
>>>Isn't that the correct way:
>>>
>>>$CompanyNa me = "Joe's Place";
>>>Insert into mytable fld1= addslashes($Com panyName)....
>>>
>>>How else would you do it?[/color]
>>
>> If you have:
>>
>> Joe\'s place
>>
>> ... stored in the database, you've added slashes twice.
>>
>> You should only add enough slashes so that the data gets to the database
>> in
>> its original form.
>>
>> If $CompanyName contains "Joe's Place" then doing one addslashes() as you
>> say
>> is correct. This makes it "fld1='Joe\ 's Place'" in the SQL, and stores
>> "Joe's Place".
>>
>> However if it's already "Joe\'s Place" then another addslashes makes it
>> "fld='Joe\\ \'s place'" in the SQL, and you store "Joe\'s Place" which
>> wasn't your original data.
>>
>> Do you have one of the automatic escaping functions on, the magic_quotes*
>> settings? That would explain the double-escaping.
>>[/color]
>
> Andy, et. al.
>
> When I do add[color=green][color=darkred]
>>>$CompanyNa me = "Joe's Place";
>>>Insert into mytable fld1= addslashes($Com panyName)....[/color][/color]
>
> and look at the field name in phpMySQLAdmin for the record I see: Joe\'s
> Place. So I assume that the slash is actually stored in the database. And
> this is why when I do:
>
> $recsql="select CompanyName from mytable";
> $rs = $db->Execute($recsq l);
> $cname= stripslashes($r s->Fields['CompanyName']);
> (I use the ADODB wrapper)
>
> Does the database table actually carry the slash? I don't know, but I see it
> in MySQLAdmin.... so I figure I have to do the stripslashes. Everything
> seems to work..... except when you do the "LIKE" search in SQL and you need
> 2 addslash functions.
>
> I don't know if I have magic anything turned on. I know that the above code
> works fine on my local Apache as well as whatever pair.com runs.
>
> Let me know what you think regarding the double addslashes for Joe's Place.
>
> -Al[/color]

OK, I FOUND THE ANSWER. It was Andy who helped me see the light here... along
with some other posts in the archives. It seems that "addslashes " does what
it says, but that MySQL strips them out before it pops the field in the
database. Under normal conditions it is NOT stored as Joe\'s Place but as
Joe's Place.

HOWEVER, if for some (dumb) reason you have something called
"magic-quotes-gpc" turned ON in your php.ini file, the slashes are added for
you atomatically. If you continue to do a "addslashes " you end up with
"Joe\\'s Place". MySQL strips out the first one, but leaves the second which
is why you will see the \ in the database if you go in and edit a record.

And if this is the case then you NEED to do a stripslashes when getting the
record in order to get rid of the darn \.

THE KEY, (IMO... and maybe I'm wrong) is to turn OFF this "magic quote"
thingy, and ALWAYS use the addslashes function on all strings that are going
to be inserted into SQL or if you are going to do a string search (select).

Maybe someone can explain the concept of this "magic quote" parm, but it
seems to me (and other postings that I've read) that the PHP developers made
a mistake by trying to do "too much" for the developer.... but I guess that's
another issue.

I hope someone will please come on and tell me if the above is a correct
analysis in case I'm all wrong. We don't want bad info to be on Google
without someone setting it straight because I'm sure others will have this
problem as well.

Al

MySQL apostrophy with LIKE won't work.

MySQL apostrophy with LIKE won't work.

Comment

Comment

Comment

Comment

Comment