Jump to content


Photo

Like and \\\' problem?


  • Please log in to reply
6 replies to this topic

#1 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 06 August 2006 - 03:20 AM

SELECT name, id FROM all_items WHERE name LIKE '%pirate\\\'s%'
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0030 sec)

SELECT name, id FROM all_items WHERE name LIKE '%pirate\'s%'
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0030 sec)

SELECT name, id FROM all_items WHERE name LIKE '%pirate%'
The Pirate\'s Code  30
Famous Pirates 32

I need to be able to search for the entry "The Pirate\'s Code" using "pirate\'s" or "pirate\\\'s". Why do the first two not work?
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 01:03 PM

Have you got magic_quotes_runtime turned on? Because maybe the string in the database has been escaped twice and is actually "The Pirate\\\'s Code"?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 August 2006 - 02:45 PM

I'm not sure I understand why the string in your DB has the backslash at all... sounds like it got "over-escaped" on the way in, as king arthur suggested.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 06 August 2006 - 03:32 PM

The actual record in the database is The Pirate\'s Code, and that's what I need to find. :(

Here's what happens. The user enters pirate's in a search form.

Then:

$item = mysql_real_escape_string(strip_tags($_POST['search']));
$shop_info = "SELECT name, id FROM all_items WHERE name LIKE '%$item%'";

At that point $shop_info = "SELECT name, id FROM all_items WHERE name LIKE '%Pirate\\\'s%'"
Which if I understand the escaping, it should find pirate\'s.

And, I have no idea why it's overescaped, when I put it in the database I used "mysql_real_escape_string(strip_tags($_POST['search']));" to get the string and put it in.

ARGH
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 August 2006 - 04:24 PM

Why does the actual data have escape characters in it?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Jessica

Jessica
  • Staff Alumni
  • This is not my name.
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 06 August 2006 - 05:00 PM

Why does the actual data have escape characters in it?


And, I have no idea why it's overescaped, when I put it in the database I used "mysql_real_escape_string(strip_tags($_POST['item']));" to get the string and put it in.

In that case, 'item' was The Pirate's Code.

Now I've gone into the database and changed the entry to "The Pirate's Code" unescaped, and the query
SELECT name, id FROM all_items WHERE name LIKE '%Pirate\\\'s%'

Finds it.

WTF is going on here? Something is wrong with my database settings.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 August 2006 - 05:56 PM

Sounds like you have magic quotes turned on, and then you're running escape string again.  You need to have a wrapper function that handles this... there are many such examples.  However, if it's stored "properly" now in the DB, you should need to include the extra backlash in your query.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users