MySQL quote escaped still returns error

Question

SELECT * FROM `entries` WHERE MATCH(`title`) AGAINST('Linux\'s')

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 's'' at line 1

I don't have the vaguest idea why this error persists when the quote is escaped.

PS: The upper query works in phpMyAdmin.

class SmalllightSearch extends Smalllight {

        //----- SEARCH BY FIELD VALUE -----\\
        public function searchByFieldValue($field, $value, $smart = true, $limit = NULL, array $orderby = NULL) {
            $value = mysql_real_escape_string($value);
            $entries = array();
            $q = "SELECT * FROM `".$this->table."` WHERE MATCH (`$field`) AGAINST ('$value')";
            echo $q;

            if($orderby != NULL) {
                $list = array();
                foreach($orderby as $k => $v) { array_push($list, '`'.$k.'` '.$v); }
                $order = implode(', ', $list);
                $q .= ' ORDER BY '.$order;
            }
            if($limit != NULL) { $q .= ' LIMIT '.$limit; }

            $r = mysql_query($q) or die(mysql_error());
            while($row = mysql_fetch_assoc($r)) {
                array_push($entries, $row);
            }

            if($smart == true) {
                $right = new Smalllight('right');
                $wrong = new Smalllight('wrong');
                $words = str_word_count($value, 1);

                foreach($words as $word_key => $word_value) {
                    $find_right = $right->findByFieldValue('value', $word_value);

                    if($find_right == false) {
                        $find_wrong = $wrong->findByFieldValue('value', $word_value);

                        if($find_wrong == true) {
                            $pair_right = $right->findById($find_wrong[0]['right_id']);
                            $words[$word_key] = $pair_right['value'];
                        }
                        else {
                            $pattern_right = $right->findByPattern('value', $word_value, 5, array('occur' => 'DESC'));

                            if($pattern_right == true) {
                                foreach($pattern_right as $pattern_key => $pattern) {
                                    similar_text($word_value, $pattern['value'], $similar);

                                    if($similar >= 70 && $word_value !== $pattern['value']) {
                                        $wrong->setValue('right_id', $pattern['id']);
                                        $wrong->setValue('value', $word_value);
                                        $wrong->store();
                                        $words[$word_key] = $pattern['value'];
                                    }
                                }
                            }
                        }
                    }
                }
                $meaning = implode(' ', $words);
                if($meaning != $value) {
                    $link = implode('+', $words);
                    echo 'Did you mean: <a href="index.php?search='.$link.'">'.$meaning.'</a><br/>';
                }
                return $entries;
            }

            elseif($smart == false) {
                return $entries;
            }
        }
}

This is how I execute it:

if($_GET['search']) {
        $slight = new SmalllightSearch('entries');
        $entries = $slight->searchByFieldValue('title', $_GET['search']);
        if($entries == true) {
            foreach($entries as $entry) {
                echo '<b>'.$entry['title'].'</b><br/>'.$entry['body'].'<br/><br/>';
            }
        }
        else {
            echo '<br/>No results found for: <b>'.$_GET['search'].'</b>';
        }
    }

There is no need to echo right before, because the limit and orderby parameters are not set.

"The upper query works in phpMyAdmin." --- and where it doesn't work? — zerkms, Mar 04 '14 at 21:51
try ``SELECT * FROM `entries` WHERE MATCH(`title`) AGAINST('Linux''s')``, i.e. use another `'` to escape. — KrazzyNefarious, Mar 04 '14 at 21:52
Now `echo` the final query version, not intermediate results. — zerkms, Mar 04 '14 at 22:13
I did it and same result, tho it wasn't needed because I haven't set the limit and order parameters in the function. — Alexandrw, Mar 04 '14 at 22:15
Tell me how I can chat you some teamviewer data and you will test and see for yourself. I'm going to pull out all my hair in the next hours... — Alexandrw, Mar 04 '14 at 22:22

score 2 · Answer 1 · answered Mar 04 '14 at 21:53

It obviously doesn't work in php because \' is treated as an escape sequence, thus it's parsed by php.

So you need to escape a slash as well

AGAINST('Linux\\\'s')

PS: if you used prepared statements or at least properly used escape functions your mysql client provides - it wouldn't be an issue.

score 0 · Answer 2 · answered Mar 04 '14 at 21:55

0

This is the PHP code (part of it):

public function searchByFieldValue($field, $value, $smart = true, $limit = NULL, array $orderby = NULL) {
            $value = mysql_real_escape_string($value);
            $entries = array();
            $q = "SELECT * FROM `".$this->table."` WHERE MATCH (`$field`) AGAINST ('$value')";
            echo $q;

And this is the output:

SELECT * FROM entries WHERE MATCH (title) AGAINST ('Linux\'s')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 's'' at line 1

answered Mar 04 '14 at 21:55

Alexandrw

1,289
2
8
10

In my database, Linux's is written as Linux's, not Linux\'s – Alexandrw Mar 04 '14 at 21:56
This code doesn't run the generated query thus it cannot cause the given error. PS: Put that in the question – zerkms Mar 04 '14 at 21:57
I really don't know what to do... :( – Alexandrw Mar 04 '14 at 22:00
it's not helpful. Your code doesn't execute the query, so it cannot cause the given error. – zerkms Mar 04 '14 at 22:00
i execute the function like this: `$slight = new SmalllightSearch('entries'); $entries = $slight->searchByFieldValue('title', $_GET['search']);` And inside the GET search i type Linux's... is this helpful now? – Alexandrw Mar 04 '14 at 22:02
1

you're executing the function like that. But how do you run the query? I don't see any `mysql_query` in the code or whatever – zerkms Mar 04 '14 at 22:03
`$r = mysql_query($q) or die(mysql_error()); while($row = mysql_fetch_assoc($r)) { array_push($entries, $row); }` – Alexandrw Mar 04 '14 at 22:04
It's not possible for this code to throw such an error. – zerkms Mar 04 '14 at 22:05
It does it sadly :( i could show you over teamviewer the entire code... this is why i'm telling you that the generated query by this code actually works in phpMyAdmin, but not here :( and I don't know what to do. – Alexandrw Mar 04 '14 at 22:07
1

put the whole function to the question. – zerkms Mar 04 '14 at 22:08

score -1 · Answer 3 · answered Mar 04 '14 at 22:49

-1

The problem is solved, I needed to add '\' in the str_word_count() function so it would recognise \ as part of a word.

$words = str_word_count($value, 1, '\\');

answered Mar 04 '14 at 22:49

Alexandrw

1,289
2
8
10

MySQL quote escaped still returns error

3 Answers3