SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ok to use the page name in url to determine where clause in sql query?

    Hi, I'm making an attempt to build a small cms based site. I have read in a lot of places it is best to use the id of a table row to fetch contents, but being as I am using url rewrite in frontend, it won't be showing query strings, so I thought I would ask if anyone saw any potential problems if I did the following.

    Page table looks like this:
    Pages
    - id
    - name
    - content

    In backend, the user has a form to name the page and use ckeditor to add rich content. I will use php to ensure that the name is alphanumeric and use strtolower & str_replace to ensure that My First HTML Page is sent to mysql as my-first-html-page and also check that a row with this name does not already exist.
    So on front end this page url will be mywebsite.com/my-first-html-page

    So to output the content I strip out the domain & / so I'm left with the name as entered in the db.
    PHP Code:
    $url $_SERVER['REQUEST_URI'];
    $url str_replace ('/','',$url); 
    Then in my function to output content I will use WHERE name = $url

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    So.. you're doing some url rewriting/redirecting to change mywebsite.com/my-first-html-page into mywebsite.com/page.php (cause otherwise you'll get a 404).
    #1: Your where clause is gonna need some quotation marks because you're searching for a non-numeric value.
    #2: Escape the string. Escape the string. Escape the string. I know it's a SERVER variable, but it's user input. If i go to mywebsite.com/'%3B%20DROP%20TABLE%20Pages%3B%20SELECT%20*%20FROM%20Pages%20WHERE%20'a'%20%3D%20'a ....
    #3: Sanitize the string; alphanum and - isnt that hard to filter to.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah yes, sorry I should have explained. I will do all the sanitising, escaping e.t.c. I only needed to see if I might encounter a problem with using the page name instead of the id that I hadn't thought of, so I didn't post the entire code.

    It won't be rewriting mywebsite.com/my-first-html-page into mywebsite.com/page.php, the url will remain mywebsite.com/my-first-html-page

    My .htaccess looks like this:
    RewriteEngine On
    RewriteRule ^(admin|images|gallery)($|/) - [L]
    RewriteRule !(\.gif|\.png|\.jpe?g|\.css|\.js|\.php|^public/.*)$ index.php [nocase,last]

    Then in my index.php I'll do something like this:
    PHP Code:
    function page()
        {        
        if (!
    $url){
           
    $url='home'// this will be page id 1 in db and cannot be deleted or have its name edited
            
    }
        else{    
            
    $url $_SERVER['REQUEST_URI'];
            
    $url str_replace ('/','',$url);
            }
            
    $connection db_connect();            
            
    $query = ("SELECT name FROM pages WHERE name = $url"); 
                
                
    $result mysql_query($query);                
                
    $count mysql_num_rows($result);
                if (
    $count == 0
                {
                  return 
    false;    
                }            
                
    $row mysql_fetch_array($result);            
                return 
    $row;            
        }

    $page page();
    echo 
    $page
    Thanks for taking a look

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Yup. Sanitize, wrap the $url bit in single quotes. Long as you do that, there shouldnt be a problem.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    mywebsite.com/my-first-html-page gives: mywebsite.com/index.php?slug=my-first-html-page

    Consider:
    Code:
    Pages
    ====
     slug | name | content
    ---------------------
    my-first-html-page | My first HTML Page | blah blah
    ---------------------
    Your queries become:
    PHP Code:
    $incoming_slug =  ( isset($_GET['slug']) ) ? $_GET['slug'] :  'default-page' ;

    "SELECT name, content from pages where slug = "mysql_real_escape_string($incoming_slug). "'"
    Bugbears include:

    -making sure that slug is unique (set a unique index on the table)
    -working out how to handle an editor altering the name (and therefore the slug)

    Benefits include:

    -No de-referencing id numbers to get records
    -Used as foreign keys they need no deciphering when you browse your data in the db
    -you can have your .htaccess rules apply some basic filtering (between 4 and 40 chars, a-z and dashes only, say)

    It does not have to be a kneejerk reaction to use ids.

    YMMV

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I meant to write.
    PHP Code:
     $query = ("SELECT content FROM pages WHERE name = $url"); 
    The ck editor will only be used to enter html into the content fields. A standard form input will be used to enter the page name, since it will only be alphanumeric text.
    Great about the .htaccess advice, I'll definitely use that.

    Thanks guys


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
  •