Selecting data from mySQL using the ID in URL

问题:

I have a table that has the columns

GroupID | GroupName | GroupDesc | Overs |
-----------------------------------------
1       | Test Group|Description| Yes   |

I have a page called list.php and it currently creates the URL for each row in the DB in the groups table(above).

The code is not the prettiest but I think it works this is he code

list.php

<?php 
    $result = mysql_query("SELECT * FROM groups");

    while($row = mysql_fetch_array($result))
        {
            echo "<div class=\"divider\">";
            echo "<a href=\"group.php?id=";
            echo $row['GroupID'];
            echo "\">";
            echo $row['GroupName'];

            echo "</a>";
            echo "<br><br>";
            echo $row['GroupDesc'];
            echo "<br>";
            echo "Over 18's: ";
            echo $row['AgeRes'];
            echo "</div>";
        }
?>

This then creates a URL such as this http://domainname.com/group/group.php?id=1

This is where my questions are - how would I select the relevant row from the DB above using the ID section in the URL?

My second question would be how would we stop this being SQL injectable?

I am kind of new to all this so I would love an answer on this and any good reading sources so I can develop my skills further.

Thanks

回答1:

To answer the first question, you need to use a where clause in your query. I am not sure if the column name I used is correct, but I am sure you get the idea. There are LOADS of great online interactive SQL tutorials for free that you can use to get some idea of how to code queries.

To answer your second question, you can use the mysql_real_escape_string() function to tidy up the variable being passed. A better way however is to change the way you are connecting to the datbase. The PDO and mysqli both do a much better job of connecting to the database. You should look at learning those instead - especially if you are just starting out.

<?php 
    $id=mysql_real_escape_string($_GET['id']);
    $result = mysql_query("SELECT * FROM groups where id=".$id.";");
    // Am not 100% sure if that is the right column name to use for your database.


    while($row = mysql_fetch_array($result))
    {
        echo "<div class=\"divider\">";
        echo "<a href=\"group.php?id=";
        echo $row['GroupID']; 
        echo "\">";
        echo $row['GroupName'];

        echo "</a>";
        echo "<br><br>";
        echo $row['GroupDesc'];
        echo "<br>";
        echo "Over 18's: ";
        echo $row['AgeRes'];
        echo "</div>";
    }
?>

回答2:

Q1: You need to retrieve the request variable and include in your SQL query:

$result = mysql_query("SELECT * FROM groups WHERE id = '" . $_GET['id'] . "'");

Q2: You should escape the value first before including in your query:

$id = (int)mysql_real_escape_string($_GET['id']);

Then change your query to the following:

$result = mysql_query("SELECT * FROM groups WHERE id = '" . $id . "'");