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

PHP
1

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

2

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.

3

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 :slight_smile:

4

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

5

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

6

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 :slight_smile: