checking urls against tables in the database

Question

i am currently using url rewrite via IIS which is working and i am capturing the url with the below variable

$compiled_url= $_GET["params"];

however i need to check whats in that url with whats in the pages table in MySQL. the table looks like the below

tbl_pages
page_id | page_slug | page_parent
--------+-----------+------------
   1    |   posts   | 0
   2    |   daily   | 2

http://www.domain.com/posts/daily

what possible methods are there to check the above domain and passed parameters against the database and make sure they exist in that order so if the url was typed backwards daily/posts it would fail to a 404 as they don't reflect that way in the database

i have started with this method but just as an example my end result i would like to be a class or a neater option

$compiled_url = explode("/",$compiled_url); 
$compiled_parent=0;
foreach($compiled_url as $url_decompiled)
{                   
    $url_results = mysqli_query($webapp_db,"SELECT * FROM tbl_pages WHERE page_slug='" . $url_decompiled . "' AND page_parent ='" . $compiled_parent . "'");
    if(mysqli_num_rows($url_results) > 0)
    {
    echo "page found <br>";
    $result = mysqli_fetch_array($url_results);                 
    $compiled_parent=$result['page_id'];
    }
    else
    {
    echo "page not found <br>"; 
    break;
    }

}

who has done something like this before? what methods are available without using a framework?

you need a join here, joining pages table to itself. you need to practice with raw SQL, without any PHP, until you get a working query. — Your Common Sense, Jun 28 '16 at 08:24
while speaking of PHP implementation, it's severely prone to sql injection. — Your Common Sense, Jun 28 '16 at 08:25
i have handled the sql injection side of things however just not in the example. are you able to provide me with an example of the joining table — Nathan, Jun 28 '16 at 08:37

score 0 · Answer 1 · answered Jun 28 '16 at 08:48

You can use something like this (have not tested it), but you get the gist of it...

<?php
$compiled_url = explode("/",$compiled_url);

// using alphabet characters for table aliases
$alphabet = explode("","abcdefghiklmnopqrstuvwxyz");

$sql_query = "SELECT * FROM tbl_pages ".$alphabet[0];

// loop to build all the JOINs 
for($i=0; $i<count($compiled_url); $i++)
{
    $sql_query .= " INNER JOIN tbl_pages ".$alphabet[$i+1]." ON  ".$alphabet[$i+1].".page_id = ".$alphabet[$i].".page_parent";
}


// loop to build all the WHERE filters
$where = array();
for($i=0; $i<count($compiled_url); $i++)
{
    $where[] = $alphabet[$i].".page_slug = '".$compiled_url[$i]."'";
}

$sql_query .= " WHERE ".implode(" AND ",$where);

// if results are found print "page found" else print "page not found"
if(mysqli_num_rows($url_results) > 0)
{
   echo "page found <br>";
   $result = mysqli_fetch_array($url_results);                 
   $compiled_parent=$result['page_id'];
}
else
{
   echo "page not found <br>"; 
   break;
}

score 0 · Answer 2 · answered Jun 28 '16 at 08:59

The problem with your code, that you have to check outside of the for loop not inside it. And I think you added some wrong data in your database.

So consider your table looks like this

tbl_pages
page_id | page_slug | page_parent
--------+-----------+------------
   1    |   posts   | 0
   2    |   daily   | 1 // <-- edited from 2 to 1

Then you use this script

$compiled_url = explode("/",$compiled_url); 
$parent = 0;
$correct = true;
foreach($compiled_url as $item){                   
    $results = mysqli_query($webapp_db,
        "SELECT * FROM tbl_pages WHERE page_slug='" . $item . "' AND page_parent ='" . $parent . "' LIMIT 1"
    );
    if($row = mysqli_fetch_array($result)){
        $parent = $row['id'];
    } else {
        $correct = false;
        break;
    }
}

if($correct){
   echo "page found <br>";
} else {
   echo "page not found <br>"; 
}

Edit this code as you wish and be aware of sql injections if you use it as is.

This is perfect. what methods would you use to prevent sql injection of $compiled url — Nathan, Jun 28 '16 at 09:11

checking urls against tables in the database

2 Answers2