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

[BuddyH]

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.

$url = $_SERVER['REQUEST_URI'];
$url = str_replace ('/','',$url); 

Then in my function to output content I will use WHERE name = $url

[StarLion]

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.

[BuddyH]

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:

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

[StarLion]

Yup. Sanitize, wrap the $url bit in single quotes. Long as you do that, there shouldnt be a problem.

[Cups]

mywebsite.com/my-first-html-page gives: mywebsite.com/index.php?slug=my-first-html-page

Consider:

Pages
====
 slug | name | content
---------------------
my-first-html-page | My first HTML Page | blah blah
---------------------

Your queries become:

$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

[BuddyH]

Sorry, I meant to write.

 $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