Dynamic PHP links with MySQL database, fix possibility of SQL-Injection

[Silas_1]

I have 1 or actually 2 problems. I have 2 sites, the first one generates a table with avatar, coins, and name (which links to a more detailed view like this /stats/user/?name=<dynamic name>. It looks like this:

    <table>
      <thead>
        <tr>
          <th>#</th>
          <th>Points</th>
          <th>Name</th>
        </tr>
      </thead>
      <tbody>
<?php
error_reporting(E_ALL);
define ( 'MYSQL_HOST',      'host' );
define ( 'MYSQL_BENUTZER',  'user' );
define ( 'MYSQL_KENNWORT',  'password' );
define ( 'MYSQL_DATENBANK', 'database' );
$db_link = mysqli_connect (
                     MYSQL_HOST, 
                     MYSQL_BENUTZER, 
                     MYSQL_KENNWORT, 
                     MYSQL_DATENBANK
                    );

$sql = "SELECT * FROM pun ORDER BY CAST(`coins` AS SIGNED) DESC LIMIT 20";
$db_erg = mysqli_query( $db_link, $sql );
if ( ! $db_erg )
{
  die('Ungültige Abfrage: ' . mysqli_error());
}
while ($zeile = mysqli_fetch_array( $db_erg, MYSQL_ASSOC))  
{
       echo "<tr>";
       echo "<td><img src=\"localhost/avatar/" . $zeile['name'] . "\"/30.png><a></td>";
       echo "<td>". $zeile['coins'] . "</td>";
       echo "<td><a href=\"/stats/user/?name=" . $zeile['name'] . "\">" . $zeile['name'] . "<a></td>";  
       echo "</tr>";
       }
       mysqli_free_result( $db_erg );
       ?>
    </tbody>
  </table>

Then there is the 2nd one which reads out the name which did come with the link and only shows the row of this person [the database contains only unique names, so thats no problem]. It's actually nearly the same like the first page, but only displaying the 1 person. I know that that makes no sense right now, but it was just for testing, the details will come later.

    <table>
      <thead>
        <tr>
          <th>#</th>
          <th>Points</th>
          <th>Name</th>
        </tr>
      </thead>
      <tbody>
<?php
error_reporting(E_ALL);
define ( 'MYSQL_HOST',      'host' );
define ( 'MYSQL_BENUTZER',  'user' );
define ( 'MYSQL_KENNWORT',  'password' );
define ( 'MYSQL_DATENBANK', 'database' );
$db_link = mysqli_connect (
                     MYSQL_HOST, 
                     MYSQL_BENUTZER, 
                     MYSQL_KENNWORT, 
                     MYSQL_DATENBANK
                    );

$sql = "SELECT * FROM pun WHERE name = '" . $_GET['name'] . "';";
$db_erg = mysqli_query( $db_link, $sql );
if ( ! $db_erg )
{
  die('Ungültige Abfrage: ' . mysqli_error());
}
while ($zeile = mysqli_fetch_array( $db_erg, MYSQL_ASSOC))  
{
       echo "<tr>";
       echo "<td><img src=\"localhost/avatar/" . $zeile['name'] . "\"/30.png><a></td>";
       echo "<td>". $zeile['coins'] . "</td>";
       echo "<td><a href=\"/stats/user/?name=" . $zeile['name'] . "\">" . $zeile['name'] . "<a></td>";  
       echo "</tr>";
       }
       mysqli_free_result( $db_erg );
       ?>
    </tbody>
  </table>

My problem is now that someone told my that doing it this way would make it SQL-Injection-Vulnerable. I understand now why it is vulnerable, but dont know how to fix it. So my 2 questions would be:

1. I want to make /stats/user/<dynamic name> instead of /stats/user/?name=<dynamic name>, how do I do that?
2. How do I make it in a way it is not vulnerable?

[diafol]

Never put raw input data into SQL - use prepared statements and bind parameters.

http://www.php.net/manual/en/mysqli.prepare.php

http://www.php.net/manual/en/mysqli-stmt.bind-param.php

In order to create the url structure you want, you need to use Apache url_rewriting - this can be done with a .htaccess file.

A great site for creating rewrites:

http://www.generateit.net/mod-rewrite/