Updating a database with a hyperlink

Question

I am wondering if it is possible to update a database with a hyperlink. On my website, I am attempting to update the user's location in the game world. The locations are represented by numerical values.

Ex:

1 = Camp
2 = Town
3 = Forest

To do so, I created a PHP function:

function updatePlayerLocation($location)
{
    mysql_query("UPDATE ss_character SET location='$location' WHERE id='".$_SESSION['id']."'");
}

This function is then called on the onClick function of the link, as seen below:

echo "<a href=\"play.php?p=location_0\" onclick='updatePlayerLocation(0)'>" . $possibleLocations[0] . "</a><br />";

The $possibleLocations array contains all of the locations that the user can be in, ranging from 0 to 10.

The link seems to work as it loads the page, it just does not execute the MySQL query. My previous research has suggested using AJAX, but as the page needs to refresh, I am wondering if there is an alternative.

Thank you for your time and suggestions! :)

`updatePlayerLocation` is a PHP function running on the server. You can't call it with JavaScript. You have to call it with the `play.php` script — Explosion Pills, Feb 25 '13 at 02:07
@BrianGenisio I am familiar with mysql_real_escape_string, but I do not see how I can apply that to this situation. What part of the SQL is unsecure? — Josh Miller, Feb 25 '13 at 02:12
@ExplosionPills As is include the function in the URL? Ex: play.php?p=location_0&updatePlayerLocation(0) — Josh Miller, Feb 25 '13 at 02:14
@JoshMiller no. Understand that JavaScript and PHP have nothing to do with each other and they don't even know the other ones exist. In the `play.php` code, you need to call your `updatePLayerLocation` function — Explosion Pills, Feb 25 '13 at 02:18
@Josh Miller If someone can update their session id to be something like "'; drop table ss_character; '" then you are in trouble. Consider using prepared statements. — Brian Genisio, Feb 25 '13 at 02:28
@ExplosionPills All pages are included into play.php, so in the long run it is defined on play.php. Also, I am not seeing the big picture here on how to successfully use the function. How would I call it to have it perform a function in a link without using onClick? — Josh Miller, Feb 25 '13 at 02:36
Instead of using `play.php` use some other PHP page that calls the query and then redirect to `play.php` perhaps — Explosion Pills, Feb 25 '13 at 02:38
Are you saying you don't want to use AJAX because the page is refreshing anyway, or you don't want the page to refresh on click? — sjdaws, Feb 25 '13 at 02:42
@sjdaws My main reason for not using AJAX is because I don't know it, and my attempts to learn it have not been so successful. I don't not mind if the page refreshes for this purpose, so not using AJAX isn't a big deal if I don't have to. — Josh Miller, Feb 25 '13 at 02:54

score 4 · Accepted Answer · edited May 23 '17 at 10:33

Firstly, you need to validate input and your code is vunerable to sql injection. Check How to prevent SQL injection in PHP?

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi

So with that in mind, here is a PDO script which does the exact same thing, and I realise it's way longer, but you can use it as a class if needed as this is an example only.

<?php

// create connection to database
$conn = new PDO('mysql:dbname=DATABASE_NAME;host=localhost;port=3306', USERNAME, PASSWORD);

// prepare query
$pdo = $conn->prepare("UPDATE ss_character SET location = :location WHERE id = :session_id");

// set up parameters
$params = ['location' => (int)$_POST['location'], 'session_id' => $_SESSION['id']];

// loop through the paramaters to determine the type
foreach ($params as $key => $value) {

    switch ($value) {

        case is_int($value):
            $param = PDO::PARAM_INT;
            break;

        case is_bool($value):
            $param = PDO::PARAM_BOOL;
            break;

        case is_null($value):
            $param = PDO::PARAM_NULL;
            break;

        default:
            $param = PDO::PARAM_STR;
            break;
    }

    // bind paramter to query
    $pdo->bindValue(":$key", $value, $param);
}

// execute the query
$result = $pdo->execute($params);

// echo result for ajax
echo ($result) ? true : false;

And you will want some jQuery to do you ajaxing so the page isn't forced to reload

<script>

    function updatePlayerLocation(location) {

        // ensure location is numeric or stop
        if !isNaN(location) return false;

        // update location via ajax
        $.ajax({
            url: 'http://your_url/to/php/script.php',
            type: 'POST',
            data: 'location=' + location,
            success: function(data) {
                // log result to console for error trapping purposes
                console.log(data);
            }
        });

        // stop link from being processed
        return false;

    }

</script>

The HTML would of course include jQuery, the script above and at least one link:

<a href="#" onclick="return updatePlayerLocation(0);">Location name</a><br />

score 1 · Answer 2 · answered Feb 25 '13 at 03:02

By clicking a hyperlink, you're performing a GET request on the server. As you're already sending a parameter with your get request, you can do the following in your PHP code:

if(isset($_GET['p'])){ 
    $location = $_GET['p'];
    $number = preg_replace("/[^0-9]/", '', $location);
    updatePlayerLocation(intval($number));
}

Note that this will be run everytime the webpage is refreshed, so it may be advisable that the above code is put in another PHP page and then re-direct to play.php with the same parameters passed as before.

Updating a database with a hyperlink

2 Answers2