fulltext & weighted relevance

by Pascal Opitz

published 9 January 2007

Via JV Multimedia:

A quick and neat way to use MySQL fulltext search with weighted relevance modificators.

SELECT
*,
(MATCH (category) AGAINST ('$query' IN BOOLEAN MODE)*100) +
(MATCH (title) AGAINST ('$query' IN BOOLEAN MODE)*10) +
MATCH (body) AGAINST ('$query' IN BOOLEAN MODE) AS rating
FROM
stories
WHERE
MATCH (title,category,body) AGAINST ('$query' IN BOOLEAN MODE)
ORDER BY
rating DESC
LIMIT 0,10

http://www.jvmultimedia.com/portal/node/61

Comment

  1. Extremely! Insecure!
    Never! Ever! pass in $query just like that. Its the #1 security hole in most systems. It is called SQL injection.
    — Bèr Kessels    15 January, 10:52am    #
  2. Ber, first of all thank you for bringing this up. I can see why you are concerned, even though I don’t think you have a point here. Let me explain to you why:

    By default register_globals is off, and I always have turned it off. Which means that $query in the example above cannot refer to the equivalent of $_GET[‘query’].

    That means that $query is just any variable. SURELY IT SHOULD BE ESCAPED FIRST! But you cannot really tell if that hasn’t happened by just looking at the example above.

    If you were thinking that this refers to a GET variable because you are still working with register_globals turned on, then I strongly advise you to not do so anymore, because that would mean a major security hole.
    — Pascal    15 January, 1:05pm    #

Have your say

If your comment doesn't show up straight away, please don't be offended - we're not censoring, we are just trying to keep out all the viagra and poker ads. Thanks to an explosion of spam all comments are held for manual green-lighting.

name Remember
email
http://
Message <?>

Quick links

Other people's articles that we think you might be interested in: