mysql_real_escape_string ISSUE

Question

If I type

'

into my search bar I get a mysql error as the "sting" has not been escaped- it think.

But the reason why I cant escape it is because I dont think it currently is a string.

the search box generates search results dynamically with ajax it is as I type and it finds the results that I get the error:

    You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '%' OR Location
LIKE '%'%' OR Map LIKE '%'%' LIMIT 0, 16' at line 2

This is the mysql query:

<?php 
    if($_POST['q']!=""){
  include $_SERVER['DOCUMENT_ROOT'] . "/include/datebasecon.php";
        $result = mysql_query("
          SELECT id, Name, Location,  Map
          FROM Accommodation WHERE Name LIKE '%".$_POST['q']."%' OR Location LIKE '%".$_POST['q']."%' OR Map LIKE '%".$_POST['q']."%' LIMIT 0, 16") 
        or die(mysql_error());
        $output = "";
        while($row = mysql_fetch_array($result)){
            $N = preg_replace("/(".$_POST['q'].")/i","<span>$1</span>",$row['Name']);
            $L = preg_replace("/(".$_POST['q'].")/i","<span>$1</span>",$row['Location']);
            $M = preg_replace("/(".$_POST['q'].")/i","<span>$1</span>",$row['Map']);
            $output .= "<p>".$N." - ".$L."</p>";    
        }

        print $output;

    }
?>

Is there anyway i can fix this after its post the query maybe?

I don't think it currently is a string. and what is it?

Your Common Sense
– Your Common Sense

2010-09-25 13:43:05 +00:00
Commented Sep 25, 2010 at 13:43 — Your Common Sense
– Your Common Sense, Commented Sep 25, 2010 at 13:43

BoltClock · Accepted Answer · 2010-09-25 13:37:41Z

3

When magic_quotes_gpc is off (as it should be!), $_POST['q'] is simply the string ', as just one character. That's why it's appearing in your SQL code like this:

%' OR Location LIKE '%'%' OR Map LIKE '%'%' LIMIT 0, 16

The error takes place at '%'%' because the LIKE string is being prematurely terminated.

You can just use mysql_real_escape_string() on $_POST['q'] and it'll be escaped:

$q = mysql_real_escape_string($_POST['q']);
$result = mysql_query("
  SELECT id, Name, Location,  Map
  FROM Accommodation WHERE Name LIKE '%".$q."%' OR Location LIKE '%".$q."%' OR Map LIKE '%".$q."%' LIMIT 0, 16") 
or die(mysql_error());

answered Sep 25, 2010 at 13:37

BoltClock

728k165 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hacksteak25 · Accepted Answer · 2010-09-25 13:43:32Z

1

You wrote "I dont think it currently is a string"... it is a string. You can pass it to mysql_real_escape_string() and use the result to make your query secure and reliable. Everything your script receives by the $_POST, $_GET, $_REQUEST and $_COOKIE params can be used as string, except it is an array.

answered Sep 25, 2010 at 13:43

hacksteak25

2,0591 gold badge16 silver badges23 bronze badges

Comments

Your Common Sense · Accepted Answer · 2010-09-25 13:55:11Z

1

To make you understand.
Look at your query:

LIKE '%search string%'

note apostrophes you have used to delimit search string.
These apostrophes does mean that data inside IS a string.
Everything you put in quotes into query is a string.
Everything you put in quotes into query must be escaped.
No need to think, consider or estimate. The rule is simple and unambiguous: quoted text should be always escaped.

edited Sep 25, 2010 at 13:55

answered Sep 25, 2010 at 13:49

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

1 Comment

AJFMEDIA Over a year ago

Thank you for this, im fairly new to php, this has helped a lot

Collectives™ on Stack Overflow

mysql_real_escape_string ISSUE

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related