1

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

Legend1989
  • 664
  • 3
  • 9
  • 23
  • 1
    Learn to solve the tasks iteratively. Split the task "how would I select the relevant row from the DB above using the ID section in the URL" to 2 correspondent: 1) how to get the data from the url (request) 2) how to get the data from DB. Which actually #1 or #2 confuses you? – zerkms Jul 24 '12 at 22:20
  • if #1 is not a problem it would be better not to mention urls at all. As long as querying database with data retrieved from any datasource is the similar - information about URL only makes noise. – zerkms Jul 24 '12 at 22:33

2 Answers2

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>";
    }
?>
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
1

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 . "'");
Mike S.
  • 4,806
  • 1
  • 33
  • 35
  • For integers using of `(int)` instead of `mysql_real_escape_string()` makes more sense – zerkms Jul 24 '12 at 22:32
  • agree zerkms but instead of replacing, just cast the variable as (int) along with it is best to avoid "1; DROP table" attack without quotes, etc. good suggestion! – Mike S. Jul 24 '12 at 22:36
  • `(int)mysql_real_escape_string` -- makes even less sense. Just `(int)` is enough – zerkms Jul 24 '12 at 22:37
  • Reference another similar discussion: http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string thanks for additional input though. ;-) – Mike S. Jul 24 '12 at 22:39
  • If someone gives that as advice doesn't mean it makes sense. `(int)mysql_real_escape_string()` - this construction is proposed by person who doesn't understand what it does. And in every single input `(int)mysql_real_escape_string()` will give **exactly the same** result as `(int)`. If you still want to discuss it - please bother providing an input where the result will be different – zerkms Jul 24 '12 at 22:40
  • Haha... move on my friend. The purpose is to answer the question for Wayne not to critique him and everyone else. If you have a better answer then submit it and others can vote it up or down. Thanks again. – Mike S. Jul 24 '12 at 22:41
  • you already have given good enough answer, with one exception - it contains one small part that can be improved by removing function that doesn't bring anything – zerkms Jul 24 '12 at 22:42
  • even for string `(int)` and `(int)mysql_real_escape_string` will produce **the same** result. They **always** will produce the same result for **ANY** input. Just try and see yourself (if you don't believe me) – zerkms Jul 24 '12 at 22:46
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14370/discussion-between-zerkms-and-mike-s) – zerkms Jul 24 '12 at 22:48