problems with single quotes in full text search

Report · Feb 18, 2007

hi:

PHP+MySQL

i'm having problems when the user types single quotes in the search field

Parse error: syntax error, unexpected ')' in
/Users/Manager/Sites/xxx/html/results.php on line 5

line 5: $keywords = $_GET['keywords']);

so, i used the addslashes function to escape the quotes:

$keywords = addslashes($_GET['keywords']);

now i'm receiving a SQL syntax error if you type for example macy'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') AND
lang = 'en' ORDER BY full_name' at line 1

$qs = "SELECT m_id,full_name FROM media ";
$qs .= "WHERE MATCH(biblio) AGAINST('".$keywords."') ";
$qs .= "AND lang = '".$_SESSION["lang"]."' ORDER BY full_name";

tia,

jdoe

Report · Feb 18, 2007

i just realized the first error was due the right parentheses in line 5

John Doe wrote:
> hi:
>
> PHP+MySQL
>
> i'm having problems when the user types single quotes in the search field
>
> Parse error: syntax error, unexpected ')' in
> /Users/Manager/Sites/xxx/html/results.php on line 5
>
> line 5: $keywords = $_GET['keywords']);
>
> so, i used the addslashes function to escape the quotes:
>
> $keywords = addslashes($_GET['keywords']);
>
> now i'm receiving a SQL syntax error if you type for example macy'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') AND
> lang = 'en' ORDER BY full_name' at line 1
>
> $qs = "SELECT m_id,full_name FROM media ";
> $qs .= "WHERE MATCH(biblio) AGAINST('".$keywords."') ";
> $qs .= "AND lang = '".$_SESSION["lang"]."' ORDER BY full_name";
>
> tia,
>
> jdoe

Report · Feb 18, 2007

John Doe wrote:
> so, i used the addslashes function to escape the quotes:
>
> $keywords = addslashes($_GET['keywords']);
>
> now i'm receiving a SQL syntax error if you type for example macy's:

That probably means that magic quotes are turned on on your server. As a
result macy's becomes macy\'s. Passing it to addslashes makes it
macy\\'s. The first slash escapes the second one.

Remove addslashes, and you'll probably be OK.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Feb 18, 2007

hi David:

i realized the first error was just a syntax one due the right
parentheses in line 5 so, i removed the addslashes function but the SQL
error is still showing
i think the problem is the single quote is interpreted as the closing
one of the pair needed when you compare with a string in SQL. for
example if you type ALESSANDRO DELL'ACQUA, the error says:

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 'ACQUA')
OR MATCH(title,description) AGAINST('ALESSANDRO DELL'ACQUA')) AND
!autho' at line 1

it doesn't matter if i apply the addslashes function to the search
string or not!?

magic_quotes_gpc, magic_quotes_runtime and magic_quotes_sybase are Off
in both local/remote servers

thanks for your help,

jdoe

David Powers wrote:
> John Doe wrote:
>> so, i used the addslashes function to escape the quotes:
>>
>> $keywords = addslashes($_GET['keywords']);
>>
>> now i'm receiving a SQL syntax error if you type for example macy's:
>
> That probably means that magic quotes are turned on on your server. As a
> result macy's becomes macy\'s. Passing it to addslashes makes it
> macy\\'s. The first slash escapes the second one.
>
> Remove addslashes, and you'll probably be OK.
>

Report · Feb 18, 2007

John Doe wrote:
> i think the problem is the single quote is interpreted as the closing
> one of the pair needed when you compare with a string in SQL.

Have you tried using mysql_real_escape_string()? It's what you should
use instead of addslashes these days anyway.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Feb 19, 2007

i tried it w/o success:

$keywords = mysql_real_escape_string($_GET['keywords']);
echo $keywords; ---> macy\'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') AND
lang = 'en' ORDER BY full_name' at line 1"

but if i use an escaped string in the SQL query instead a var it works!?

$qs = "SELECT m_id,full_name FROM media ";
//$qs .= "WHERE MATCH(biblio) AGAINST('".$keywords."') ";
$qs .= "WHERE MATCH(biblio) AGAINST('macy\'s') ";
$qs .= "AND lang = '".$_SESSION["lang"]."' ORDER BY full_name";
$rs = mysql_query($qs, $connNS) or die(mysql_error());
$n = mysql_num_rows($rs);
if ($n>0) {
$results += $n;
...

David Powers wrote:
> John Doe wrote:
>> i think the problem is the single quote is interpreted as the closing
>> one of the pair needed when you compare with a string in SQL.
>
> Have you tried using mysql_real_escape_string()? It's what you should
> use instead of addslashes these days anyway.
>

Report · Feb 19, 2007

John Doe wrote:
> but if i use an escaped string in the SQL query instead a var it works!?

Have you tried echoing the value of $qs to see what it contains when you
use the variable?

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Feb 19, 2007

no, but i found that escaping inside the query string solved the problem:

$keywords = $_GET['keywords'];
...
$qs = "SELECT m_id,full_name FROM media ";
$qs .= "WHERE MATCH(biblio)
AGAINST('".mysql_real_escape_string($keywords)."') ";
$qs .= "AND lang = '".$_SESSION["lang"]."' ORDER BY full_name";

and for the sake of curiosity i echoed the qs value when using the
former approach:

$keywords = mysql_real_escape_string($_GET['keywords']);
...
$qs = "SELECT m_id,full_name FROM media ";
$qs .= "WHERE MATCH(biblio) AGAINST('".$keywords."') ";
$qs .= "AND lang = '".$_SESSION["lang"]."' ORDER BY full_name";
echo $qs.'<br />';

SELECT m_id,full_name FROM media WHERE MATCH(biblio) AGAINST('macy's')
AND lang = 'en' ORDER BY full_name

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') AND
lang = 'en' ORDER BY full_name' at line 1

do you have an explanation why the escape \ is lost? may be in
concatanation?

David Powers wrote:
> John Doe wrote:
>> but if i use an escaped string in the SQL query instead a var it works!?
>
> Have you tried echoing the value of $qs to see what it contains when you
> use the variable?
>

Report · Feb 19, 2007

John Doe wrote:
> do you have an explanation why the escape \ is lost? may be in
> concatanation?

No, I have no idea why it disappeared. The reason I asked whether you
have tried echoing the query was because I tested your code myself, and
it was OK (at least it was properly escaped - I didn't try a full
database query).

I can only assume there is something else in your setup or script that I
don't know about. I suppose it's possible there's a bug in the version
of PHP that you're using. Anyway, the fact that you have got it working
is good news.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Nov 07, 2015

Hello, I have same problem. My code is:

$search = $_GET ['search'];

$query = $db->prepare("SELECT COUNT(*) AS num FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE)");

$query->execute();

Can you help me?

Thanks