mysql LIKE with apostrophe

[vurentjie]

hi,

 

i am trying to solve the following problem

the problem arises when $find has an apostrophe,

 

for example...

 

$find = "Wilma's";

$select = "SELECT DISTINCT details FROM list WHERE why LIKE \"%$find%\" order by town";

 

I have tried urldecode,urlencode, mysql_escape_string, and some other functions but to no avail.

I just dont know how I could manipulate the mysql query or variable to get the result to process.

 

Any help would be appreciated.

[kenrbnsn]

Use this:

<?php
$find = "Wilma's";
$select = "SELECT DISTINCT details FROM list WHERE why LIKE '%" . mysql_real_escape_string($find) . "%' order by town";
?>

 

Ken

 

[vurentjie]

Hi,

 

thanks for the advice, but the above replacement doesn't seem to work either, I have been trying allsorts for the last four hours and just can't seem to get it workin,

 

I was wondering whether there is any special mysql setting that might be interfering, or is there some other alternative I could look into, does anybody know whether a similar query as above could be done use regular expressions??

 

Kinda drained from this.....

[radar]

have you tried addslashes?

[The Little Guy]

Try this version:

<?php
$find = "Wilma's";
$select = "SELECT DISTINCT details FROM list WHERE why LIKE '%" . addslashes($find) . "%' order by town";
?>

[vurentjie]

I am going to give that a try but I think I already went through that earlier today....it seems as if it is not meant to be as complicated as it feels at the moment, and very strange, since almost all the help I have googled has told me exactly what I am being told here, either use addslashes or mysql_real_escape_string, but i might stop later and get back to it in the morrow, will get back here in a few momentos...thanx.

[vurentjie]

ok...i will abbreviate,just to double check what I have at the moment

 

$find = urldecode($_GET["find"]);   

 

outputs from      Bob%27s  to      Bob\'s 

( have also tried stripping slashes and additional slashes but the latter did not even

look correct -> Bob\\\s ) 

 

and so I have tried both Bob's Bob\'s each with mysql_real_escape_string as well as addslashes even when i thought it looked strange, and this I send to the abovementioned select="";

 

also tried using the raw variable i.e Bob%27s

 

I will check up here again tomorrow. Thanks again.

 

 

[kenrbnsn]

If the input is "Bob%27s", then you want to do:

<?php
$find = 'Bob%27s';
$select = "SELECT DISTINCT details FROM list WHERE why LIKE '%" . mysql_real_escape_string(urldecode($find)) . "%' order by town";
?>

 

Ken

[vurentjie]

Thanks again,

 

I finally got it working properly offline, but been having trouble online, I am thinkin it might be a 'magic quotes' setting, getting in contact with my provider to see if they can alter some settings.

 

Could this be the case? I think it works when I have magic_quotes OFF for some reason...I am a bit worried if it is, only because the site is not tiny any more, and changing a setting here might affect other posts,gets and the like in other places.

 

I haven't actually double checked this at time of writing...but wanted to say thanks.

 

[kenrbnsn]

Use the following function to clean the variables:

<?php
function cleanvar($in) {
   if (get_magic_quotes_gpc())
           $in  = stripslashes($in);
   return(mysql_real_escape_string(urldecode($in))
}?>

 

Then you could do

<?php
$find = 'Bob%27s';
$select = "SELECT DISTINCT details FROM list WHERE why LIKE '%" . cleanvar($find) . "%' order by town";
?>

 

Ken

[dezkit]

OR U CAN JUST USE %27

 

$find = "Wilma%27s";