SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    ri
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help creating safe and secure search for a mysql db

    i'm trying to create a simple search for my mysql database that will be available to the public. i have worked with php and mysql alot but i have never made a search function. i need help with two things:
    1) making sure i'm doing the mysql query correctly
    2) filtering user input correctly for security

    my query looks like this:

    SELECT title, details FROM entries WHERE title LIKE '%$_POST[query]%' OR details LIKE '%$_POST[query]%'

    i want to check both fields title and details for any matches of the query. is this the proper way to do it?

    also how should i filter the user's input $_POST['query'] to make sure there aren't any dangerous metacharacters? is there a function i can run the input through?

    i've read a bunch of articles and ones covering mysql inserts but not selects... i dont know if they differ or not. most people say to use the mysql_real_escape_string() for inserts. would it be appropriate to use this for selects also?

    thanks alot for any help!

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is less danger with select that insert, update or delete (unless you're selecting from a table where there is sensitive information), so most of the time there is no problem. However you need to make sure that ' marks are escaped, as they may cause the query to fail (or not match things it should do, as presumably they were escaped before being inserted. so you can use mysql_real_escape_string(). However on some installations of php get and post are automatically escaped (magic quotes), so using mysql_real_escape string you may get it double-escaped (each ' will come out like \\\'). To overcome this you can define your own function (this one from php.net):

    function quote_smart($value)
    {
    // Stripslashes
    if (get_magic_quotes_gpc()) {
    $value = stripslashes($value);
    }
    // Quote if not integer
    if (!is_numeric($value)) {
    $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
    }

    so you query would be
    SELECT title, details FROM entries WHERE title LIKE '%".quote_smart($_POST['query'])."%' OR details LIKE '%".quote_smart($_POST['query'])."%'

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're using LIKE you should also escape % and _ (those are LIKE wildcards).

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    ri
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the function...

    what would be the best way to also escape % and _ characters ? i'm not too good with regex...

  5. #5
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you don't think your users should be searching for % of _ anyway you could always strip them out with
    str_replace("%","",$str);
    and
    str_replace("_","",$str);.

    if you want them to be able to search for % and _, I may be wrong but i think in mysql these are also escaped with a backslash:
    str_replace("_","\_",$str);
    and
    str_replace("%","\%",$str);


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •